Database - Insert Data to Remote MySQL Server

Database - Insert Data to Remote MySQL Server

Postby admin » Wed Oct 25, 2017 5:38 pm

Just imagine that you want to create IoT devices, which read sensor's value and insert this value to MySQL database on remote server. This tutorial gives you solution to do it.
In detail, this tutorial shows how to insert data from an embedded devices, called PHPoC, to remoter MySQL server.


Preparation

- A PHPoC Blue or Black on your hand
- Install MySQL server on your PC.

Install MySQL server on PC

If you did not install MySQL server on your PC, you need to install it. You can install only MySQL server or XAMPP packet on your computer ( XAMPP download link).

After installing MySQL, you need to create and username and password on MySQL. Follow these instructions to create username and password:
- Open cmd.exe
- Type following command:
cd c:\xampp\mysql\bin
- By default, MySQL has root account without password. You should add password for root account via this command:
mysqladmin -u root password root_password
- Create new user account for accessing from another host.
+ Type:
mysql.exe -u root -p
+ Input your root password
+ Create a new username and password (which is used in the source code):
CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'localhost' WITH GRANT OPTION;
CREATE USER 'your_username'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;


cmd windows will be like that:

mysql_cmd.JPG
mysql_cmd.JPG (79.71 KiB) Viewed 80 times

Now is time to program on PHPoC

PHPoC code will do:
+ Connect to MySQL Server on PC
+ Create a database named "student"
+ Create a table named "tbl_student", with five columns: id and name
+ Insert a record (id: 1, name "John") in to "tbl_student"table
+ Inquiry all record in "tbl_student"table and print out to PHPoC Debugger console

+ Delete the table
+ Delete the database
+ Disconnect from MySQL Server on PC

Step-by-step Instruction:

1. Make sure that your PHPoC Blue or Black is connected to the Internet.

2. Please download a PSP (PHPoC Support Package) of libraries and examples from PHPoC

3. Connect PHPoC Blue or Black to your PC via USB cable.

4. Run PHPoC Debugger.

5. Upload all libraries in PSP to your PHPoC Blue or Black via PHPoC Debugger.

In fact, you only need to upload sn_mysql.php file to use in this example. If you use hostname instead of IP address, you also need to upload sn_dns.php. But for future use, you can upload all.

※ Note that: the libraries should be located in the /lib folder.

upload_library.JPG
upload_library.JPG (50.43 KiB) Viewed 81 times


6. Find an example of mysql_insert in the PSP. and upload init.php and task0.php files to your PHPoC Blue or Black.
※ Note that: the init.php and task0.php files should be located in the root folder.

mysql_insert_files.JPG
mysql_insert_files.JPG (28.61 KiB) Viewed 81 times


7. Modify the Server's hostname or your PC's IP address, MySQL username and password you created above in task0.php file.

mysql_modified.JPG
mysql_modified.JPG (40.99 KiB) Viewed 81 times


8. Upload the task0.php to your PHPoC Blue or Black again.

9. Read source code line by line to know how it works.

10. Run the example to see the result shown on PHPoC debugger's console.

mysql_insert_result.JPG
mysql_insert_result.JPG (24.45 KiB) Viewed 81 times


Based on this code, you can creatively modify it to do what you want.

If you meet any issue, feel free to comment below!
admin
Site Admin
 
Posts: 166
Joined: Mon Jan 20, 2014 4:40 pm

Return to Example

Who is online

Users browsing this forum: No registered users and 1 guest

cron