WebOS Internals - User contributions [en]

Talk:Tutorials webOS Getting JSON From An External MySQL Database

2009-10-31T04:09:35Z

Anno:

I would '''''HIGHLY''''' suggest using this example as an example alone. You NEVER want to create a direct link to your database. Instead, you want to abstract it.

Instead of accessing a table called users, you'd probably want to create a method for logging in, permissions, etc so no one can just view the contents of an entire table.

In fact, I feel that this tutorial is quite negligent as it doesn't teach good practice at all.

Remember, keep all logic that can retrieve data on the server ''only'' and never anywhere else. If you want users, you should "request" the users from the server and the server should determine what kind of data you're allowed to get.

I have modified the previous "What's Next" step. I would suggest that someone, at some point, revises the example to use better coding practices rather than having a note saying "btw, this is bad, don't do what I just showed you".

Tutorials webOS Getting JSON From An External MySQL Database

2009-10-31T04:08:17Z

Anno:

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:

{| border="1" cellpadding="5" cellspacing="0" style="border-color:silver;"
|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': {
$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.

Talk:Tutorials webOS Getting JSON From An External MySQL Database

2009-10-31T03:47:29Z

Anno:

Talk:Tutorials webOS Getting JSON From An External MySQL Database

2009-10-31T03:45:40Z

Anno:

Talk:Tutorials webOS Getting JSON From An External MySQL Database

2009-10-31T03:45:24Z

Anno: New page: I would '''''HIGHLY''''' suggest using this example as an example alone. You NEVER want to create a directly link to your database. Instead, you want to abstract it. Instead of accessing ...

I would '''''HIGHLY''''' suggest using this example as an example alone. You NEVER want to create a directly link to your database. Instead, you want to abstract it.

Instead of accessing a table called users, you'd probably want to create a method for logging in, permissions, etc so no one can just view the contents of an entire table.

In fact, I feel that this tutorial is quite negligent as it doesn't teach good practice at all.