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)
email	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': {
       $query = 'SELECT * FROM ' . $_POST['table'];
       break;
   }

}

// 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;

}

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);
   }

}

McpAssistant.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 readRemoteDbTableSuccess('users') in order to test this. <source lang="javascript"> ... readRemoteTableSuccess('users'); ... </source>

What Next?

Well, from here you can use the data directly, massage it for display, or store it yourself in one of the storage options available for Mojo. Happy data pulling!