Connect iOS device to MySQL database on a server (Raspberry Pi Part)

This post shows how to create a PHP+MySQL service on Raspberry Pi for iPhone / iPad to access using the HTTP POST method.

Raspberry Pi Part

1. Make sure that Apache, MySQL, PHP and phpMyAdmin have been installed.

2. Create some data in MySQL using phpMyAdmin or MySQL monitor commands.

3. Create the iosmysql.php file with this command:

sudo nano /var/www/iosmysql.php

4. Edit and save the file as below:                    

<?php

//check if database parameters are received correctly.

if (isset($_POST['username']) and isset($_POST['password']) and isset($_POST['tablename'])) {

$username = $_POST['username'];

$password = $_POST['password'];

$tablename = $_POST['tablename'];

} else {

    die("Wrong request");

}

$servername = "localhost";

//connect to database

$connection = mysql_connect($servername, $username, $password) or die("No MySQL Connection: " . mysql_error());

mysql_select_db("mydatabase", $connection) or die("<br>No Database: " . mysql_error());

$query = "select * from $tablename";

$result = mysql_query($query) or die("Query Error: " . mysql_error());

$FieldTotal = mysql_num_fields($result);

//loop through column names

for ($count = 0; $count < $FieldTotal; $count++) {

    $field = mysql_fetch_field($result, $count) or die("Field error!");    

    //Remove "fruit_" string, which includes $tablename and "_".

    $ShortTitle = substr($field->name, strlen($tablename)+1);

    print "$ShortTitle\t\t";

}

$RowTotal = mysql_num_rows($result);

//loop through rows

for ($RowCount = 0; $RowCount < $RowTotal; $RowCount++) {

    $row = mysql_fetch_row($result) or die("Row error!");

    print "\n";

    //loop through columns

    for ($ColCount = 0; $ColCount < $FieldTotal; $ColCount++) {

        print "$row[$ColCount]\t";

        if (strlen($row[$ColCount]) < 7) {

            print "\t";

        }

    }

}

?>

In this example, parameters obtained from the HTTP POST method are username, password, and tablename, while servername is set to "localhost" locally.

iOS Part

1. Write a simple iOS app. See this:

Connect iOS device to MySQL database on a server (iOS Part)

2. Test the iOS app. The result:

This result is the same as the result obtained using MySQL monitor commands on Raspberry Pi:

References:
PHP Manual
PHP Basics
Connect iOS device to HTTP GET/POST PHP service (Raspberry Pi Part) (iOS Part)
Access and show content of a database in MySQL with PHP