Connecting to a MySQL database using PhoneGap, AJAX, and JQuery Mobile

Question

I'm in a team developing an Android application that will rely greatly on the use of a remote database. We are using PhoneGap and Jquery Mobile and have been attempting to connect to our MySQL database using AJAX and JSON calls. Currently, we are having trouble in our testing phase, which is to verify we even have a connection at all by pulling a hard-coded user of "Ted" from mySQL / input via MySQL Workbench.

From what we have gathered, the process of data transmission works as this:

1. On our html file, we have a

   <script type="text/javascript" src="Connect.js"></script>
   

   ^ Which should run the Connect.js script, correct? So from there, Connect.js is ran?

2. Connect.js runs, connecting it to our ServerFile.php that is hosted on an external web service, allowing it to run PHP to connect to the MySQL database and pull information.

   //run the following code whenever a new pseudo-page is created
   $('#PAGENAME').live('pageshow', function(event)) {
   
       // cache this page for later use (inside the AJAX function)
       var $this = $(this);
   
       // make an AJAX call to your PHP script
       $.getJSON('http://www.WEBSITENAME.com/ServerFile.php', function (response) {
   
           // create a variable to hold the parsed output from the server
           var output = [];
   
           // if the PHP script returned a success
           if (response.status == 'success') {
   
               // iterate through the response rows
               for (var key in response.items) {
   
   
                    // add each response row to the output variable
                    output.push('<li>' + response.items[key] + '</li>');
               }
   
           // if the PHP script returned an error
           } else {
   
               // output an error message
               output.push('<li>No Data Found</li>');
           }
   
           // append the output to the `data-role="content"` div on this page as a
           // listview and trigger the `create` event on its parent to style the
           // listview
           $this.children('[data-role="content"]').append('<ul data-role="listview">' + output.join('') + '</ul>').trigger('create');
       });
   });
   

Here is ServerFile.php. This should connect to the MySQL Database, make the Select statement, and then send the output to the browser encoded in the JSON format.

<?php

//session_start();
$connection = mysql_connect("csmadison.dhcp.bsu.edu", "clbavender", "changeme"); 
$db = mysql_select_db("cs397_clbavender", $connection); 

//include your database connection code
// include_once('database-connection.php');

//query your MySQL server for whatever information you want
$query = mysql_query("SELECT * FROM Users WHERE Username ='Ted'", $db) or trigger_error(mysql_error());

//create an output array
$output = array();

//if the MySQL query returned any results
if (mysql_affected_rows() > 0) {


    //iterate through the results of your query
    while ($row = mysql_fetch_assoc($query)) {

        //add the results of your query to the output variable
        $output[] = $row;
    }


    //send your output to the browser encoded in the JSON format
    echo json_encode(array('status' => 'success', 'items' => $output));

} else {

    //if no records were found in the database then output an error message encoded in the JSON format
    echo json_encode(array('status' => 'error', 'items' => $output));
}
?>

Yet nothing is showing here. What do we do from here?

Answer 1

First thing first. Try to determine where is the problem come from, server side or client side.

Print out your database query and encoded json can be useful. If you are creating a simple API service, you should be able to enter http://www.WEBSITENAME.com/ServerFile.php using your browser and look how the output is.

Use echo to print things with php.

If all looks ok, time to print out the response you receive from the server in the javascript and see what is off.

Use console.log to print thing with javascript. The logs should appear in the logcat section of eclipse (since you are developing android app)