Database - Update Data in Remote MySQL Server

Database - Update Data in Remote MySQL Server

Postby admin » Wed Oct 25, 2017 6:21 pm

Just imagine that you want to create IoT devices, which read sensor's value and update this value to MySQL database on remote server. This tutorial gives you solution to do it.
In detail, this tutorial shows how to update 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 83 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
+ Update the record (John -> Roy)
+ 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 83 times


6. Find an example of mysql_update 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_update_files.JPG
mysql_update_files.JPG (28.18 KiB) Viewed 83 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_update_modified.JPG
mysql_update_modified.JPG (44.8 KiB) Viewed 83 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_update_result.JPG
mysql_update_result.JPG (24.36 KiB) Viewed 83 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