This article illustrates how to access to local or remote MySQL server from the client API in Python, C and PHP.

Refer to MySQL server installing and test to install a MySQL server on your CORE9G25 Board with the database used on this examples.

Connecting to MySQL server with Python

Install MySQLdb module from Debian repository by typing:

debarm:~# apt-get install python-mysqldb

The try this example:

import MySQLdb

db=MySQLdb.connect(host="127.0.0.1",port=3306,db="mydb",user="root",passwd="CORE9G25")

cur = db.cursor()
cur.execute("SELECT * FROM addressbook;")

rows = cur.fetchall()

for row in rows:
    print row

 

debarm:~# python addrlist.py 
(1L, 'CORE9G25 Systems srl', '+39 (06) 99-12-187', 'www.CORE9G25systems.it')
(2L, 'Atmel Corporate', '+1 (408) 441-0311', 'www.atmel.com')
(3L, 'Digikey', '+1 (800) 344-4539', 'www.digikey.com')
(4L, 'Mouser', '+39 02 575 065 71', 'www.mouser.com')

Connecting to MySQL server with C

Install MySQL C client library from Debian repository by typing:

debarm:~# apt-get install libmysqlclient-dev

The try this example:

#include "mysql/mysql.h"
#include "stdio.h"
#include "stdlib.h"
#include "unistd.h"    
#include "fcntl.h"  

int main(int argc, char **argv)
{
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;
    int num_fields;
    int i;

    if ((conn = mysql_init(NULL))==NULL) {
        fprintf(stderr, "Failed on mysql_init()\n");
        exit(1);
    }

    if (mysql_real_connect(conn, "127.0.0.1", "root", "CORE9G25", "mydb", 3306, NULL, 0)==NULL) {
        fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(conn));
        exit(1);
    }

    if (mysql_query(conn, "SELECT * FROM addressbook")!=0) {
        fprintf(stderr, "Failed on SQL Query: %s\n", mysql_error(conn));
        exit(1);
    }

    result = mysql_store_result(conn);

    num_fields = mysql_num_fields(result);

    while ((row = mysql_fetch_row(result))) {
        for(i = 0; i < num_fields; i++) {
            printf("%s ", row[i] ? row[i] : "NULL");
        }
        printf("\n");
    }
    mysql_free_result(result);
    mysql_close(conn);
    exit(0);
}

Compiling it in this way:

debarm:~# gcc addrlist.c -lmysqlclient -o addrlist

debarm:~# ./addrlist
1 CORE9G25 Systems srl +39 (06) 99-12-187 www.CORE9G25systems.it 
2 Atmel Corporate +1 (408) 441-0311 www.atmel.com 
3 Digikey +1 (800) 344-4539 www.digikey.com 
4 Mouser +39 02 575 065 71 www.mouser.com

Connecting to MySQL server with PHP

Install the PDO driver:

debarm:~# apt-get install php5-mysql

Restart lighttpd web server:

debarm:~# /etc/init.d/lighttpd restart

Save in /var/www/ the following PHP script:

Position: CD:\\Debian\playground\python\mysql\addrlist.php

Open a browser on the following url:

• http://board-ip/addrlist.php

We suggest to enable the display_errors parameter on line 531 of /etc/php5/cgi/php.ini file during the development:

display_errors = On

and restart the lighttpd web server:

debarm:~# /etc/init.d/lighttpd restart

Related links

• MySQLdb Python module
• MySQL C client API
• PHP Data Objects (PDO) interface to MySQL