One of the most important and common job, for mobile applications is to talk with a remote database. Today about the 90% of the apps are interfacing a remote RDBMS, for example companies management applications or simple applications like the weather application, the social networks applications etc etc….


Often iOS applications have to interface a web site or an online db; to implement such communications, foundamentally, you can follow two streets:

  • Use a library that talks with the RDBMS

On the internet I've seen a lot of libraries, for iOS, that do the work. For example you can use mysql_connector/C from mysql.com (take a look to this interesting post) or you can use something like iOS Open Database Connectivity SDK

  • Use scripts (php, perl, ruby etc etc) that are stored on the same server of the RDBMS

You can also talk with the RDBMS using scripts, this method has a lot of advantages:

  1. Security: The DB NAME, PASSWORD and HOST NAME aren't on the iOS device, so an attacker doesn't have them.
  2. Decoupling: The application code and the database code are decoupled, so if you need to change your application you don't have to recompile the interface, too. And vice-versa.
  3. Porting: if you want to port your application from a device to another, or to a iOS device to an Android device you don't have to recompile the library (if it exists).

Anyway there are also some disavantages, for example the CPU load is demanded to the server, because you are not using the device CPU to make computation. So keep in mind you can have this problem.

Now I'm going to show to you how to make a query to a MySQL DB using iPhone together with php scripts.

The connection to the database is the first problem you'll have, normally I write a php script that makes the connection and then I include it into each php script that works with the DB. Here you are the connection.php script:

 

<?php
    //
    //  connection.php
    //  
    //
    //  Created by gg1 on 10/04/12.
    //  Copyright (c) 2012 xAppSoftware.com. All rights reserved.
    //
    //  Connect to the DB
    //

    /** MySQL database name */
    define('DB_NAME', 'databasename');
    /** MySQL database username */
    define('DB_USER', 'username');
    /** MySQL database password */
    define('DB_PASSWORD', 'databasepassword');
    /** MySQL hostname */
    define('DB_HOST', 'databasehost');

        // Initialization
    $conn = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);
    mysql_select_db(DB_NAME, $conn);

    // disable reporting errors for security reason
    error_reporting(0);

    // Error checking
    if(!$conn) {
        die('Could not connect ' . mysql_error());
    }
?>

I think this script is very simple so I'll go ahead without explanations.

Now if you want to retrieve the complete content of the "account" table you have to write a new script, let's call it accountlist.php


 

<?php
    include('connection.php');

    $table = "accounts";

    // Build the sql query
    $sql = "SELECT * FROM $table ";

    $result = mysql_query($sql,$conn);

    if(!$result) {
        die("Error retrieving scores " . mysql_error());
    }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
         "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta name="viewport" content="width=320; initial-scale=1.0; maximum-scale=1.0; user-scalable=0;"/>
    </head>
    <body>
        <?php
            // Display the table
            echo '<table style="width:100%">
                    <thead>
                        <tr>
                            <td>id_account</td>
                            <td>username</td>
                        </tr>
                    </thead>
                    <tbody>';
           while ($row = mysql_fetch_object($result)) {
                echo '<tr>
                        <td>
                        '.$row->id_account.'
                        </td>
                        <td>
                        '.$row->username.'
                        </td>
                     </tr>';
            }
            echo '</tbody>
                </table>';
            mysql_free_result($result);
            mysql_close($conn);
        ?>
    </body>
</html>

In your objective c code add the following few lines:

First of all prepare the request, 

NSURLRequest *request = [NSURLRequest requestWithURL:[NSURL URLWithString@"http://www.yourdomain.ext/accountlist.php"]];

 

 

then issue the request,

NSError         * e;

NSData      *data = [NSURLConnection sendSynchronousRequest:request returningResponse:nil error:&e];

 

last, retrieve the data

NSString *strResult = [[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding];



That's all,

Gg1