Tutorials webOS Getting JSON From An External MySQL Database
This tutorial covers a simple example of how to connect to a PHP page using an Ajax POST method to retrieve data from an external MySQL datbase and return the results in JSON format.
What You Need
Required
- A Web server with MySQL
- Mojo SDK and emulator (obvious)
Optional
- Palm Pre
The Setup
MySQL
The first thing you need to do is create a MySQL database with at least one table in it. This tutorial will focus on a single table (named users) with the following structure:
Column | Type |
uid | INT |
name | VARCHAR(45) |
VARCHAR(45) |
Now, put some records in there and move on to the Web setup.
Web
All you NEED to have is a PHP (or some other scripting solution that can handle JSON) program accepting connections. Here is a sample that should be self-explanatory:
<source lang="php"> <?php header('Content-type: application/json'); // this is the magic that sets responseJSON
// Connecting, selecting database $link = mysql_connect($dbhost, $dbuser, $dbpass)
or die('Could not connect: ' . mysql_error());
mysql_select_db($dbname) or die('Could not select database');
switch($_POST['op']) {
case 'getAllRecords': { $table = $_POST['table']; $query = sprintf("SELECT * FROM %s", mysql_real_escape_string($table)); // Performing SQL query $result = mysql_query($query) or die('Query failed: ' . mysql_error()); $all_recs = array(); while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { $all_recs[] = $line; } break; }
}
echo json_encode($all_recs);
// Free resultset mysql_free_result($result);
// Closing connection mysql_close($link); ?> </source> Notes:
- setting the "Content-type" header to "application/json" is required to get responseJSON set in the response object
- $dbhost, $dbuser, and $dbpass (obviously) need to be set by you, it is beyond the scope of this tutorial to discuss proper hiding of your connection information
- 'op' and 'table' will be set as part of the query from the Ajax request and will be present in $_POST, this allows the code to be generic enough to respond with a dump for any table name you pass
- your code can be tested in a normal Web browser BUT you must remove/comment out the header line (unless you want to download the result, then leave it in) AND you must check $_REQUEST in the PHP instead of $_POST (don't forget to change back when it's time for emulator testing)
Mojo
Everything is coming along nicely, now you just need to have some code that will create an Ajax request to your PHP page and, upon success, process responseJSON to do whatever you need to do. <source lang="javascript"> SomeAssistant.prototype.readRemoteDbTable = function(table) {
var url = 'http://www.myserver.com/pathTo/my-awesome-script.php';
try { if(!table) { throw('readRemoteDbTable(): table name must be defined'); } var request = new Ajax.Request(url,{ method: 'post', parameters: {'op': 'getAllRecords', 'table': table}, evalJSON: 'true', onSuccess: this.readRemoteDbTableSuccess.bind(this), onFailure: function(){ //Stuff to do if the request fails, ie. display error Mojo.Log.error('Failed to get Ajax response'); } }); } catch(e) { Mojo.log.error(e); }
}
SomeAssistant.prototype.readRemoteDbTableSuccess = function(response) {
Mojo.log.info('Got Ajax response: ' + response.responseText); var json = response.responseJSON;
try { for(var field in json){ Mojo.log.info('Got field: ' + field + ' with value: ' + json[field]); } } catch(e) { Mojo.log.error(e); }
} </source> Notes:
- 'method' is where you set the POST request
- 'parameters' holds the pieces of the query string('op' and 'table')
- evalJSON is probably optional, but it makes me feel better to use it
Of course, now you need to call readRemoteDbTable('users') in order to test this. <source lang="javascript"> ... readRemoteDbTable('users'); ... </source>
Good Coding Practices
Keep in mind that this example is just that: an example. You normally wouldn't want to create code in PHP that will return the entire contents of a table. Having said that, use this to expand upon the technique and gather the specific data you need. Remember to keep all data retrieval operations, massaging, etc on the server-side to prevent unauthorized access to your database. If you're storing user-specific information, you'll most likely want to create a method to login, return an authentication token and use that for all further operations when requesting data. This helps keep the requests secure and prevents unauthorized access from third parties.