Access SQL database through PHP?

Question

I'm a spanking new developer to the PHP & SQL scene. I've only done Objective-C programming so far. However one of my projects requires me to have an online database which I need to access from my application. I was going to use the MCPKit framework to remotely access my DB like this:

MCPConnection *db;
db = [[MCPConnection alloc] initToHost:@"db.something.com" withLogin:@"someuser" usingPort:3306];
[db setPassword:@"somepass"];
NSLog(@"Connect: %d", [db connect]);

But this doesn't work because I contacted my hosting provider and they have it set up so that I can't access my database from an external host (for security). So, I'll have to look for alternatives. The only thing I could think of would be to set up a PHP script that would be on my server that would download the entire database and feed it to me as a .sql file which I can then manipulate.

However, I have no clue where to start here. I found that you can access a database in PHP like this:

$mysql = new MySQLi('db.something.com', 'someuser', 'somepass', 'mydb')
$mysql->query("SELECT * FROM `something`");

However I haven't tried this and I don't know how I can access the result of this.

To boil the question down, I want to know how to access a remote database and have a PHP script send me the database as a file which I can manipulate in my Cocoa application.

In fact, if running the PHP script can be done inside the Cocoa app it'd be even more awesome. Ideas?

---

Well, I ended up doing it like this:

<?PHP
$con = mysql_connect("db.something.com","someuser","somepass");
if (!$con){die('Could not connect: ' . mysql_error());}
mysql_select_db("db", $con);
$result = mysql_query("SELECT * FROM table");
if(mysql_num_rows($result)) {
    while($device = mysql_fetch_assoc($result)) {
      $devices[] = $device;
    }
  }

header('Content-type: application/json');
echo json_encode($devices);
mysql_close($con);
?>

Once that runs I can do stuff with the resulting JSON. Is this a good way to do it?

Answer 1

Downloading the entire database and uploading it with the changes will probably not make sense for performance (and security) reasons.

What one would usually do is set up an API in PHP or some other server side language that can be called from your iOS app, and does the operations for you.

I have no own experience with them so I can't judge on their completeness and robustness, but there are ready-made PHP APIs that translate mySQL operations into REST operations like phprestsql. Maybe that's something for you; however note that you always need to solve the problem of authentication. If you have an API with write access (or read access to sensitive materials), you will need to do something to prevent everyone on the Internet from accessing that URL.

Edit: if it's just about polling a defined set of data, you may be easiest off with a standard PHP "query a table and output stuff in a loop" script (preferably using PDO), and returning a json_encode()d result.

Answer 2

You could use a combination of JavaScript/PHP and the web scripting capabilities of WebKit. Apple has it in the Mac OSX docs here: http://developer.apple.com/library/mac/#documentation/Cocoa/Conceptual/DisplayWebContent/Tasks/JavaScriptFromObjC.html and here: http://developer.apple.com/library/mac/#documentation/AppleApplications/Conceptual/SafariJSProgTopics/Tasks/ObjCFromJavaScript.html#//apple_ref/doc/uid/30001215-BBCBFJCD

My basic idea is, you setup a PHP script that runs your queries on the db, this script will be called by a JavaScript function by using AJAX. This JS function would be invoked by Objective-C and your JavaScript could call a callback function to hand over the results to your Obj-C code.

Makes sense?