Announcement

Collapse
No announcement yet.

MySQL - Updating Data to Remote Server

Collapse
X
Collapse

  • 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 Code:
    <?php

    system
    ("php task0.php");

    ?>



    task0.php

    [Full Code]
    PHP 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:
      Code:
      cd c:
      	mpp\mysql\bin
    • By default, MySQL has root account without password. You should add password for root account via this command:
      Code:
      mysqladmin -u root password root_password
    • Create new user account for accessing from another host.
      • Type:
        Code:
        mysql.exe -u root -p
      • Input your root password
      • Create a new username and password (which is used in the source code):
        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

    Other Resources
    Last edited by support; 02-09-2018, 09:37 AM.
      Posting comments is disabled.

    Categories

    Collapse

    Latest Articles

    Collapse

    • Arduino - RS-485 Expansion Board
      support
      PES-2607 is an easy-to-use RS422/RS485 Expansion Board for Arduino Uno and Mega, which allows Arduino to exchange data with serial device via RS422 or RS485.
      Especially, Arduino does NOT use UART pins to communicate with RS422/RS485 expansion board. Therefore, users can use Arduino UART pins for other purposes.
      Moreover, A single Arduino Uno/Mega can communicate with multiple RS422/RS485 expansion boards (up to 14) without using Arduino UART pins.

      Library and examples for...
      11-13-2018, 03:45 PM
    • Arduino - RS-422 Expansion Board
      support
      PES-2607 is an easy-to-use RS422/RS485 Expansion Board for Arduino Uno and Mega, which allows Arduino to exchange data with serial device via RS422 or RS485.
      Especially, Arduino does NOT use UART pins to communicate with RS422/RS485 expansion board. Therefore, users can use Arduino UART pins for other purposes.
      Moreover, A single Arduino Uno/Mega can communicate with multiple RS422/RS485 expansion boards (up to 14) without using Arduino UART pins.

      Library and examples for...
      11-13-2018, 03:44 PM
    • Arduino - RS-232 Expansion Board
      support
      PES-2606 is an easy-to-use RS-232 Expansion Board for Arduino Uno and Mega, which allows Arduino to exchange data with serial device via RS-232.
      Especially, Arduino does NOT use UART pins to communicate with RS-232 expansion board. Therefore, users can use Arduino UART pins for other purposes.
      Moreover, A single Arduino Uno/Mega can communicate with multiple RS-232 expansion boards (up to 14) without using Arduino UART pins.

      Library and example for the RS-232 expansion board...
      11-13-2018, 03:43 PM
    • Arduino - Stepper Motor Controller
      support
      PES-2605 is an easy-to-use stepper motor controller for Arduino Uno and Mega, which uses micro-stepping method to precisely control stepper motor.
      Library and example for the stepper motor controller are part of of PhpocExpansion library for Arduino. The library reference is available here.

      This tutorial shows how to use the step motor controller with an example of PhpocExpansion library for Arduino.


      Hardware Required...
      11-13-2018, 03:41 PM
    • Arduino - DC Motor Controller
      support
      PES-2604 is an easy-to-use DC motor controller for Arduino Uno and Mega.
      Library and example for the DC motor controller are part of of PhpocExpansion library for Arduino. The library reference is available here.

      This tutorial shows how to use the DC motor controller with an example of PhpocExpansion library for Arduino.


      Hardware Required...
      11-13-2018, 03:40 PM
    • Arduino - Digital Input Board
      support
      PES-2602 is an easy-to-use 4-port Input Expansion Board for Arduino Uno and Mega, which allows Arduino to monitor state of DC electric device. In addition, it can monitor NPN, PNP and dry contact(relay).
      Library and example for the 4-port input expansion board are part of of PhpocExpansion library for Arduino. The library reference is available here.

      This tutorial shows how to use 4-port input expansion board with an example of PhpocExpansion library for Arduino.

      ...
      11-13-2018, 03:39 PM
    Working...
    X