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:
- Security: The DB NAME, PASSWORD and HOST NAME aren't on the iOS device, so an attacker doesn't have them.
- 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.
- 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
can be a very simple example, but lacks a lot of features need to work decently
Apart form error checking in PHP, synch download in IOS is the evil.. apple says.
Feel free to contact me if You need further details.
Hi Mr. conti,
this is only a sample, it’s not the bible.
I want only to give some info for beginners.
Also, remeber that Apple is a great company its guidelines are very useful but Apple is a company, Apple wants to drive developers to Apple’s goals.
Anyway this blog is open, so if you want you can post your articles (I’ll put your name and a link to your site) or you can reply to this post.
Have a good day,
Gg1
Mr Conti, could you elaborate why retrieving data this way is evil? Im a beginner and would like to know further detail. Also, how do you ensure data delivery whilst not overloading the server If many users are downloading the data at once?
Hi
after getting the result from the database. how would you display the result? i mean what class is the right to use? is it uitable view? im a professional web developer using php and ruby but im a new on ios 😀
i just want to understand what the class is the right to use with the response from a database.
thanks and more power
Hi,
It depends on what you are doing, if you are retrieving a dump of the db, you cold put it into a uitextview.
If you are retrieving a list of users, you should put it into a uitableview, if you are getting data about aircrafts you should place each flying object in a mkmapview.
i have an error that strResult is an unused variable:(
Strange, you should have a warning. Ant it is normal, you have only set its value, so it is unused.
If you try to print it the warning will go away.
For example add the following line:
NSLog(“%@”, strResult);
after
NSString *strResult = [[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding];
After pasting your code, I get this error:
Use of undeclared identifier 'data'
How do I fix this issue?
The error is in this line of code
NSString *strResult = [[NSString alloc]initWithData:data encoding:NSUTF8StringEncoding];
@Charles make sure you have added the following line before the error
NSData *data = [NSURLConnection sendSynchronousRequest:request returningResponse:nil error:&e];
Thanks! Worked like a charm.
I want to take the *strResult and populate a TableView with it. Can you give me some pointers?
take a look to this tutorial http://www.iosdevnotes.com/2011/10/uitableview-tutorial/
It is giving me a Bad Gateway 502 error. Will have to check this ayer once they get it fixed. Thanks again!
Yesterday, it was working fine. Try this one http://www.xcode-tutorials.com/uitableview-–-1-creating-a-simple-table-view/
Both of those links are dead. 🙁 How can I go about using the strResult variable? Id like to plug it into a Table View or a drop down menu.
What’s happening?
Please try with google http://www.google.it/search?client=safari&rls=en&q=uitableview+–+creating+a+simple+table+view&ie=UTF-8&oe=UTF-8&redir_esc=&ei=qotkUNmEC8fgtQasn4DgBw
Man… tyvm…
I have no idea how many time i have looking for a simple sample about to use PHP as layer to connect with remote MySQL.
Thks again!
Hi,
I have found a simple solution for your problem. I tried the sqlgw package from
http://www.sqlgw.com
and it works fine.
It’s java….
It costs a tot….
I cannot see the source.
This is really not a good example. Generating HTML on the server assumes that your app(s) will display data without any understanding of it.
A much more flexible architecture would be to return the data as JSON and format it on the client.
If your database is in the cloud, take a look at Espresso Logic, EmergentOne or Kumulos. There is no excuse for writing this code by hand when you can just leverage a service.
@Max this is your opinion and I publish it! Depending on what you are doing there are no excuses for use third parties (with high prices) architectures instead of building your own one.
Regarding the disadvantage of using script to retrieve data from DB, is it really bad? Will demanding the CPU Load to server be able to cause a server down? I am new to iOS development so I want to know the best way of doing this.
Hi!
thanks for your tutorial!! But … I have a problem!!
I changed accountlist.php to get data I need, but when i Log my results I have something like:
2014-06-15 01:28:26.467 ProvaPhp[12784:60b] Risultati: <!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>
<table style="width:100%">
<thead>
<tr>
<td>Frase</td>
</tr>
</thead>
<tbody><tr>
<td>
Frase uno, Prova!
</td>
<td>
</tr><tr>
<td>
Frase due, Prova!
</td>
<td>
</tr></tbody>
</table> </body>
</html>
@Giovanni,
I cannot understand, what is the error in your test?
in strResult you should have the results from database in an HTML page, and…. you have this page, now you have to parse the results.
Let me know if you are talking about a different problem.
is it possible to use this aproach to insert data from app into remote db
Yes, you can use this approach!
So, do I have to compose your accountlist.php as NSString to include, say $sql = "INSERT INTO $table ('col1','col2') VALUES('val1','val2')"; statement instead of SELECT etc, save/upload it as .php file up on a server then fire up NSURLRequest on that php file? Something like that?
You can create a new php file accepting a parameter like “account_name”
let’s call it addaccount.php
include(‘connection.php’);
$table = “accounts”;
// Build the sql query
$sql = “INSERT …….. ” . $_GET[“account_name”];
$result = mysql_query($sql,$conn);
if(!$result) {
die(“Error retrieving scores ” . mysql_error());
}
?>
In the objc you have to change the line
NSURLRequest *request = [NSURLRequest requestWithURL:[NSURL URLWithString: @”http://www.yourdomain.ext/accountlist.php”]];
in
NSURLRequest *request = [NSURLRequest requestWithURL:[NSURL URLWithString: @”http://www.yourdomain.ext/addaccount.php?account_name=youraccountname”]];
gg
Great, thank you