How to use database MySQL with Raspberry Pi

     Raspberry Pi can interface database MySQL with C++ Programming. You can read or write data to MySQL Server. So in your Raspberry Pi will must prepare MySQL Library for programming before.

SETUP MySQL C Access

sudo apt-get update                                           
sudo apt-get upgrade                                          
sudo apt-get install libmysqlclient-dev                       



When install complete you will see folder below.

/usr/include/mysql

Using library Example.

1. Download Xampp for create MySQL Server on your PC

http://www.apachefriends.org/en/xampp.html

2. Install Xampp and open XAMPP Control Panel and click start Apache and MySQL

3. Open browser type http://localhost and click Enter

4. Look menu Tools at left side. Click phpMyAdmin

5. Click Databases

6. Type database name = "raspberrypi" and click Create

7. Type table name = "settings" number of Columms = 2 and click Go

8. Type columm name follow picture and click Save. After that you will got database infomation below.

DATABASE_NAME "raspberrypi"
DATABASE_USERNAME "root"
DATABASE_PASSWORD "password"

9. Create new source file .

nano db.c                                                   

10. Copy example source and past to that file.

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include <mysql/mysql.h>

#define DATABASE_NAME "raspberrypi"
#define DATABASE_USERNAME "root"
#define DATABASE_PASSWORD "password"
MYSQL *mysql1;

//*****************************************
//*****************************************
//********** CONNECT TO DATABASE **********
//*****************************************
//*****************************************
void mysql_connect (void)
{
    //initialize MYSQL object for connections
mysql1 = mysql_init(NULL);

    if(mysql1 == NULL)
    {
        fprintf(stderr, "ABB : %s\n", mysql_error(mysql1));
        return;
    }

    //Connect to the database
    if(mysql_real_connect(mysql1, "192.168.1.52", DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, 0, NULL, 0) == NULL)
    {
    fprintf(stderr, "%s\n", mysql_error(mysql1));
    }
    else
    {
        printf("Database connection successful.\r\n");
    }
}

//**********************************************
//**********************************************
//********** DISCONNECT FROM DATABASE **********
//**********************************************
//**********************************************
void mysql_disconnect (void)
{
    mysql_close(mysql1);
    printf( "Disconnected from database.\r\n");
}

void mysql_write_something (void)
{
    //vector times;   //a vector of alarm times
    if(mysql1 != NULL)
    {
        //Retrieve all data from alarm_times
        if (mysql_query(mysql1, "INSERT INTO settings (   \
id,   \
value_string   \
) VALUES (   \
99,   \
'Hello'   \
) \
ON DUPLICATE KEY UPDATE   \
id = 99,   \
value_string = 'Hellow'   \
"))

        {
            fprintf(stderr, "%s\n", mysql_error(mysql1));
            return;
        }
    }
}


int main(int argc, char **argv)
{
mysql_connect();
mysql_write_something ();

if (mysql1 != NULL)
{
        if (!mysql_query(mysql1, "SELECT id, value_string FROM settings WHERE id = 99"))
        {
        MYSQL_RES *result = mysql_store_result(mysql1);
        if (result != NULL)
        {
        //Get the number of columns
        int num_rows = mysql_num_rows(result);
        int num_fields = mysql_num_fields(result);

        MYSQL_ROW row; //An array of strings
        while( (row = mysql_fetch_row(result)) )
        {
        if(num_fields >= 2)
        {
        char *value_int = row[0];
        char *value_string = row[1];

        printf( "Got value %s\n", value_string);
                }
        }
                mysql_free_result(result);
        }
else
{
printf("Cannot get data\r\n");
}
        }
else
{
printf("Cannot SELECT\r\n");
}
    }
    mysql_disconnect();
        return 0;
}

11. Save and Exit by type Ctrl + x , Y and Enter

12. Compile and Build execute file.

g++ -o db db.c `mysql_config --cflags` `mysql_config --libs`            

13. Run file

sudo ./db                                                               

14. If no error it will show below.