MySQL - Updating Data to Remote Server

If you want to create an IoT device, which reads sensor's value and updates the value to MySQL database on remote server. This tutorial gives you solution to do it.


Hardware

• PHPoC Blue (+ USB WLAN) or PHPoC Black (+ Ethernet cable)
• Micro USB to USB Cable (to upload source code to PHPoC Device)

Quick Steps

Source code of this example is a part of PHPoC Support Packet (PSP). You need to:

• Download PHPoC Support Package.
• Upload example\net\01.php_task\mysql_update to PHPoC Blue/Black.
• Modify the MySQL server's hostname or IP address, MySQL username and password in task0.php.
• Save the modified file.
• Configure network parameters (e.g. WiFi SSID, password, IP address ...).
• Make sure that MySQL database has been intalled on your PC or server and the account identical to account you put on PHPoC code is exsited. If you have not install MySQL database on your PC, refer to "How to Install MySQL on PC" at the end of this article.

If you use PHPoC for the first time, see How To Use PSP.


Source Code

Source files includes:

• init.php: this file is run when PHPoC system is powered or reset. It is used to specify which file is run is system loop.
• task0.php: this file is run in system loop of PHPoC devices.

init.php

This file is run when PHPoC system is powered or reset. It is used to specify that task0.php is run is system loop.

<?php

system("php task0.php");

?>

task0.php

[Full Code]

<?php

if(_SERVER("REQUEST_METHOD"))
 exit; // avoid php execution via http request

include_once "/lib/sn_dns.php";
include_once "/lib/sn_mysql.php";

echo "PHPoC example: update data to MYSQL DB\r\n\r\n";

//Enter your DB Server's hostname or IP address!
$server_addr = "192.168.0.100";

//Enter your account information!
$user_name = "user_id";
$password = "password";

//Connect to DB Server
if(mysql_connect($server_addr, $user_name, $password) === false)
 exit(mysql_error());

//Create a database named student
if(mysql_query("CREATE DATABASE student;") === false)
 exit(mysql_error());

if(mysql_select_db("student") === false)
 exit(mysql_error());

//Create a table named student
if(mysql_query("CREATE TABLE tbl_student (id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL);") === false)
 exit(mysql_error());

//Insert a record
if(mysql_query("INSERT INTO tbl_student (id, name) VALUES (1, 'John');") === false)
 exit(mysql_error());

//Update the record (John -> Roy)
if(mysql_query("UPDATE tbl_student SET name='Roy' where id=1;") === false)
 exit(mysql_error());

//Inquiry all record
$result = mysql_query("SELECT * FROM tbl_student;");
if($result === false)
 exit(mysql_error());
else
{
 $result_arr = mysql_fetch_row($result);
 //Print the result
 printf("%s -> %s\r\n", $result_arr[0], $result_arr[1]);
}

//Delete the table
if(mysql_query("DROP TABLE tbl_student;") === false)
 exit(mysql_error());

//Delete the database
if(mysql_query("DROP DATABASE student;") === false)
 exit(mysql_error());

mysql_close();
echo "example has been finished!\r\n";

?>

[Explanation]

Source code of this file does:

• Connects to MySQL Server on PC.
• Creates a database named "student".
• Creates a table named "tbl_student", with two columns: id and name.
• Inserts a record (id: 1, name "John") into "tbl_student" table.
• Updates the record (John -> Roy).
• Inquiries all record in "tbl_student"table and print out to PHPoC Debugger console.
• Deletes the table.
• Deletes the database.
• Disconnects from MySQL Server on PC.

Test and Result

• Click "Run" button on PHPoC Debugger.
• See Result on PHPoC Debugger's Console.
  

Appendix: How to Install MySQL 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:
  	mpp\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:


See Also

• DNS lookup
• Sending email (SMTP)
• Sending email (ESMTP)
• MySQL - Inserting Data to Remote Server
• TCP Server - Single TCP Connection
• TCP Server - Multiple TCP Connections
• TCP Client - HTTP GET

Other Resources

• How To Use PHPoC Support Package
• PHPoC System Files
• How PHPoC System Works