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?

Why are you trying to download the whole DB if its supposed to be online? IF its supposed to be online it makes more sense to set up a API you can access from your C app. — prodigitalson
– prodigitalson, Commented Apr 23, 2011 at 17:55
@prodigitalson: Yeah I'd love to be able to access it from my app directly. However my hosting provider claims that I cannot do that because they don't allow external connections to the database. :/ — sudo rm -rf
– sudo rm -rf, Commented Apr 23, 2011 at 17:57
@sudo and they are right. Running mySQL traffic through a public network is slow, dangerous, and you usually end up having to give the database user more privileges than your app's user should have. With a direct mySQL connection, you have to assume the device's user can manipulate the SQL statements that get to the server. — Pekka
– Pekka, Commented Apr 23, 2011 at 17:58
@sudo: You mis unsderstand.. you would make a web application on the remote server that can access the DB then you would that remot app's api to get and send info from the db for example by making put or post http requests with the data to store or making get requests for the data you want. — prodigitalson
– prodigitalson, Commented Apr 23, 2011 at 18:06

Pekka · Accepted Answer · 2011-04-23 18:01:11Z

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.

edited Apr 23, 2011 at 18:01

answered Apr 23, 2011 at 17:55

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

sudo rm -rf Over a year ago

Well, I'm not going to be uploading the database, it's a read-only operation basically. I just need to access the stuff in it every hour. But yes that RESTful link you provided looks good.

sudo rm -rf Over a year ago

@Pekka: This is getting more complicated that I bargained for. I was hoping for just a simple script that would download my database, but I guess it's not that simple, is it? :)

Pekka Over a year ago

@sudo nope, sorry :) But in the end, it will probably be simpler than it looks now. Maybe @Björn's links help, I'm sure you're not the first iOS developer to hit this problem so there probably exist some accepted practices already

sudo rm -rf Over a year ago

@Pekka: I'm sure others have faced the same problem. I'm actually new to programming in general; I've only been doing it for about a year. So you understand how this looks slightly overwhelming. :)

Pekka Over a year ago

@sudo that looks okay. Just remember that if you start accepting parameters in the URL, you need to escape them before using them in the query using php.net/mysql_real_escape_string

|

Björn Kaiser · Accepted Answer · 2011-04-23 18:01:49Z

1

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?

answered Apr 23, 2011 at 18:01

Björn Kaiser

9,9024 gold badges40 silver badges57 bronze badges

4 Comments

sudo rm -rf Over a year ago

It makes sense, sure. It also sounds like a nice way to do it. But I have no clue where to start on all that. I'm not asking someone to write my whole method for me, but I don't really know how to get going on this. Is there an example somewhere on the 'net that would help explain this?

Björn Kaiser Over a year ago

On which part you need examples? For the PHP part? Or accessing JavaScript from Obj-C and Obj-C to JavaScript?

sudo rm -rf Over a year ago

örn: Both of them, really. However if you can only find one or the other that's helpful too.

Björn Kaiser Over a year ago

For the query part this should help stackoverflow.com/questions/383631/json-encode-mysql-results I don't have an example for the "Access JavaScript from Objective-C part" but the Apple docs should help you here

Collectives™ on Stack Overflow

Access SQL database through PHP?

2 Answers 2

13 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related