Populate your PhoneGap App with Data from an External Server DB

With this article, we’ll pull data from a server and show it in the app. We’ll create a database table on a server, load it with data, then set up the scripts to make it all work. You’ll need a MySQL server and the ability to upload and change files on it.

My development environment:

Mac OS X 10.9.1 on latest Mac Mini
Cordova CLI 3.4.0
For Android 4.3, 4.4 v19
Testing in device: Android-19, Nexus 7 with 4.3
No Jquery or other JS or CSS packages used

This post assumes you already have your Cordova PhoneGap environment already set up. Look elsewhere on this site for setting up for iOS or Android.

Create a Database on a Server

To pull data from a database from a server, we need a database on a server! Here are the steps I took for my shared server, using the cPanel.

1. I signed in to the Admin area then in cPanel I clicked on the icon for MySQL Databases.

2. I typed testDB in the database name field. The host automatically prefixes my DB name with an internal name to keep it unique on their servers. Jot down this full name (prefix and name) for a later step. Click on Create Database.

3. An empty database has been created. Click on cPanel to go back.

Set Up a Table in the Database

The database has been made, but it needs to be populated with a table that contains information. We first create a table then fill it with data.

1. In cPanel, I clicked on the phpMyAdmin icon to start that program.

2. In the left nav bar, click on your new DB name, testDB.

3. Fill in the table name, practiceTBL, with 4 columns, then on Go. For this project, the data will be a list of four electric and nitro RC cars and they will eventually be displayed onscreen according to the power source selected.

4. The Create Table dialog appeared and I filled in the blanks for each column. (An explanation of these column values is presented later in this article for those who are unfamiliar with databases).

record 1: the ID for each record:
Name: id
Type: INT
Length/Values: 2
Default: None
Collation: utf8_general_ci
Attributes: unsigned
Null: unchecked
Index: Primary
AUTO-INCREMENT: checked
Comments: ID of record

record 2: to hold the category for record selection (electric or nitro):
Name: power
Type: CHAR
Length/Values: 8
Default: None
Collation: utf8_general_ci
Attributes: blank
Null: unchecked
Index: blank
AUTO-INCREMENT: unchecked
Comments: sort with this column

record 3: the actual vehicle name and description:
Name: vehicle
Type: VARCHAR
Length/Values: 40
Default: None
Collation: utf8_general_ci
Attributes: blank
Null: unchecked
Index: blank
AUTO-INCREMENT: unchecked
Comments: vehicle names

record 4: a full URL for more information:
Name: url
Type: VARCHAR
Length/Values: 100
Default: None
Collation: utf8_general_ci
Attributes: blank
Null: unchecked
Index: blank
AUTO-INCREMENT: unchecked
Comments: URL to vehicle’s page

With a fifth column, we could also include a URL to a thumbnail of the item. But I am keeping this basic.

5. Click on the Save button. Now I see a list of tables in the left nav, with mine listed among them.

———————–

Brief explanation of the record fields:

Name: The first column is typically the ID of the record. In this case, the count will only go from 0-3, since there are only 4 records (count starts with “0” not 1). Otherwise, it’s the descriptive name of the category of data for that record, such as “power” in my case.

Type: The ID column will be filled with digits only, so, “INT” for integer. In the case of non-integers, the records will be filled with a variable length of characters, so, VARCHAR.

Length/Values: How many characters will this field hold, maximum? Using a reasonable number will keep the size of the database down.

Default: None in my case, because each record’s field will be different.

Collation: utf8_general_ci, because this character set will be best read in browsers around the world.

Attributes: “unsigned” for the ID field to use only positive integers.

Null: unchecked, because this field should not be left blank in my case.

Index: “Primary” to keep each row in the table a unique ID number in the database, and it does not allow a NULL value in that field.

AUTO-INCREMENT: checked, so the database will automatically fill in this field with the next highest integer.

Comments: Put notes to yourself about the column (optional; not seen by public).
———————–

Add Data to the Table

The table is created, but there is no data in it yet. I clicked on practiceTBL. The menu bar at top changes. Follow along as we enter the data into fields:

1. To enter data, click on Insert in the menu bar at top. A page for entering data for two records at a time appears. We don’t enter anything in the ID field because the server will populate that field automatically.

record 1:
power: electric
vehicle: RC10B5 Team Kit, 1:10 scale buggy
url: http://www.teamassociated.com/cars_and_trucks/RC10B5/Team/

record 2:
power: electric
vehicle: SC10RS RTR, 1:10 scale short course truck
url: http://www.teamassociated.com/cars_and_trucks/SC10/Race-Spec/

Click on the Go button at bottom to insert the values. The next page will show what values were entered, but in the SQL syntax. Click on Insert in the menu bar to enter two more records.

record 3:
power: nitro
vehicle: RC8.2RS RTR, 1:8 scale buggy
url: http://www.teamassociated.com/cars_and_trucks/RC8.2/RSRTR/

record 4:
power: nitro
vehicle: RC8T Factory Team Kit, 1:8 scale truck
url: http://www.teamassociated.com/cars_and_trucks/RC8T/Factory_Team/

Click on the Go button at bottom to insert the values. The next page will show what values were entered, but presented in the SQL syntax.

We have now entered all the sample data for this table. Click on the Home icon at upper left then on your DB name, then on practiceTBL. You’ll see the record listing. Clicking on Edit will allow you to edit the fields.

8. If you look at the “breadcrumb”-style navigation at top, you’ll see your full database name. Mine is composed of my username, underscore, and testDB. Note that full DB name and write it down.

Make a Connection Script

1. We need four pieces of information to make a script that connects us to the server:

database type: mysql (The type of database you created. We created a MySQL DB with the above steps.)
DATABASE-NAME: username_testDB (substitute “username” for the information in step 8).
USERNAME: (Given by my server. Seen in step 2 when creating the DB.)
DB PASSWORD: (Created in step 2 when we made the table.)

2. Copy the following into a text editor (not a word processor), and substitute the terms with the content you have above. Specifically, swap out DATABASE NAME, USERNAME, and DB PASSWORD where they appear below. They are in uppercase so they’ll stand out here; use the same case as your originals. Retain the single-quotes. This is for a MySQL database. I suspect the contents will be different for other servers.

<?php
try
{
$pdo = new PDO('mysql:host=localhost;dbname=DATABASE-NAME', 'USERNAME', 'DB PASSWORD');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
echo 'Unable to connect to the database server: ' . $e->getMessage();
exit();
}
?>

3. Save it as connect.php. This is a connection script I picked up from the 2012 book by Kevin Yank: PHP & MySQL – Novice to Ninja. It’s an excellent resource if you are just starting out with PHP and MySQL, and uses the current accepted PDO scripting for MySQL. The setAttribute line sets up debugging to show error messages, and the echo line will show the error message. This is for debugging purposes only; you will remove these lines for production.

Make the PHP Display Page

In a new PHP file we’ll add the rest of the code needed to grab our table data and display it. This will go on the server in the same folder as the connect.php page. I will give steps on doing this later. The following code was adapted from http://www.w3schools.com/php/php_ajax_database.asp and page 123 of Kevin Yank’s book.

1. Open a new text file in a text editor, copy the following into it, and name it getcars.php. If you named your table other than practiceTBL, then replace it in the one place it appears.

<?php
include 'connect.php';
$q = $_GET['q'];
// Protect against form submission variables.
if (get_magic_quotes_gpc())
{
 $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
 while (list($key, $val) = each($process))
 {
 foreach ($val as $k => $v)
 {
 unset($process[$key][$k]);
 if (is_array($v))
 {
 $process[$key][stripslashes($k)] = $v;
 $process[] = &$process[$key][stripslashes($k)];
 }
 else
 {
 $process[$key][stripslashes($k)] = stripslashes($v);
 }
 }
 }
 unset($process);
}
try
{
 $sql = "SELECT * FROM practiceTBL WHERE power = '" . $q . "'";
 $result = $pdo->query($sql);
}
catch (PDOException $e)
{
 echo 'Error fetching data: ' . $e->getMessage();
 exit();
} echo "<table border='1'>
<tr>
<th>Table ID</th>
<th>Power</th>
<th>Vehicle</th>
<th>URL</th>
</tr>";
while ($row = $result->fetch())
{
 echo "<tr>";
 echo "<td>" . $row['id'] . "</td>";
 echo "<td>" . $row['power'] . "</td>";
 echo "<td>" . $row['vehicle'] . "</td>";
 echo "<td><a href='" . $row['url'] . "'>learn more</a></td>";
 echo "</tr>";
 }
echo "</table>";

Upload Files to Server

The above two files need to go onto the server. The HTML page will interact with those pages; they in turn will interact with the database to pull the information from the table and display it on the HTML page.

Here are the steps I used to upload them to the server.

1. Back in the cPanel page of your host server, click on File Manager.

2. Your folder structure is shown. Click on www/ or public_html/ to open it.

3. Click on Upload, navigate to getcars.php, and OK.

4. Click on Upload, navigate to connect.php, and OK.

Your files are now uploaded.

Modify or Create .htaccess File

We need to give permission to allow cross-origin access to our server. Since our app is on a different domain than the server, there is no way to read data from the external domain with JavaScript — this is a security precaution. This file will give permission using CORS (cross-origin resource sharing), a W3C Working Draft.

This .htaccess information comes from http://enable-cors.org/server.html, “Apache.” If you are using a different server than Apache, you’ll need to do this differently. Follow the instructions on that page for your server type.

Open the hidden .htaccess file in the www/ folder. I had to navigate to Legacy File Manager and click on “Show hidden files” to see and edit the file online. In File Manager, open to edit, or download to edit in a text editor, that file. Just insert the line at the end if you don’t see it there already.

Include this line exactly as written:

Header set Access-Control-Allow-Origin "*"

Create the App Page

Now we create the HTML page that goes in your Cordova PhoneGap app. It uses CORS, which makes it possible to get data from a different domain. (Other scripts use json-P for this instead, which is very popular. Read more here: https://developers.digitalriver.com/why-use-cors-versus-jsonp-cross-domain-requests)

The following script was compiled from http://www.w3schools.com/php/php_ajax_database.asp and http://www.html5rocks.com/en/tutorials/cors/. It uses Ajax (Asynchronous JavaScript And XML). To learn more about this script, visit http://html.net/tutorials/javascript/lesson18.php and http://www.javascriptkit.com/jsref/ajax.shtml.

Open a new text file with the following contents and call it ajaxDB.html, and save it in the www/ folder of your Cordova project. Make sure to link to this page by a button from your index.html page. This sample page does not need the cordova.js file.

Replace MYDOMAIN with your own domain name in two places.

<!DOCTYPE html>
<html>
 <head>
 <meta charset="utf-8" />
 <meta name="format-detection" content="telephone=no" />
 <!-- WARNING from Cordova: for iOS 7, remove the width=device-width and height=device-height attributes. See https://issues.apache.org/jira/browse/CB-4323 -->
 <meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=device-width, height=device-height, target-densitydpi=device-dpi" />
<title></title>
<style type="text/css">
.buttonClass, h2, p, .inputClass, #output, td, select {
 font-family: "Helvetica"; color: #000; font-size:1em; 
}
h2 {
 font-size:1.3em; font-weight: bold;
}
.buttonClass, .inputClass, #output, .buttonDel, select {
 border-radius:8px; 
 border:#878787 solid 1px; padding:.5em 1em;margin:.5em;
 -webkit-appearance:none;
} 
.buttonClass, .inputClass, #output {
 width: 46%;
} 
.buttonClass {
 text-align: center; height: 3em; background-color:#fff;
}
.select {
 text-align: center; height: 9em; background-color:#fff; line-height: 3em;
}
.inputClass {
 text-align: left; height: 2em; background-color:#ffe; width:90%; 
}
.segment {
 display:block; border-radius:8px; background-color:#eee;
 border:#878787 solid 1px; padding:1em; margin:.5em;
 -webkit-appearance:none; 
 height: auto; 
}
table {
 width:100%;
}
td {
 font-size:.8em;
 padding: .5em;
 width:25%;
 border:1px gray solid;
 overflow:auto; 
 border-radius: 4px;
 background-color: #fff;
 height:2em;
}
@media screen and (max-width:800px) {
 .buttonClass { width: 100%;}
}</style></head>

<body> 
<input type="button" class="buttonClass" onclick='window.location="index.html"' value="Return">
<div class="segment">
<h2>Serving Data from External Server</h2>
 <form action="" method="GET" id="formPowerType">
 <select name="users" onchange="showUser(this.value)">
 <option value="">Select power type</option>
 <option value="electric">Electric</option>
 <option value="nitro">Nitro</option>
 </select>
 <!-- <br><input type="button" id="button" class="buttonClass" value="GO" onSubmit="return showUser(this.form)"> -->
 </form>
 <br>
 <div id="txtHint">(Data will be displayed here)</div>
<input type="button" class="buttonClass" onclick='window.location="https://iphonedevlog.wordpress.com/2014/03/28/populate-your-phonegap-app-with-data-from-an-external-server-db/"' value="View code on iPhoneDevLog">
</div>
<script>

// --------------------------- CORS begin
// Create the XHR object.
function createCORSRequest(method, url) {
 var xhr = new XMLHttpRequest();
 if ("withCredentials" in xhr) {
// XHR for Chrome/Firefox/Opera/Safari.
 xhr.open(method, url, true);
 console.log('withCredentials');
 } else if (typeof XDomainRequest != "undefined") {
// XDomainRequest for IE.
 xhr = new XDomainRequest();
 xhr.open(method, url);
 } else {
// CORS not supported.
 xhr = null;
 }
 return xhr;
}
function makeCorsRequest() {
 var xhr = createCORSRequest('GET', url);
 if (!xhr) {
 alert('CORS not supported');
 return;
 }
 xhr.onload = function() {
 console.log("onload begin");
 }
xhr.onerror = function() {
 alert('Error accessing database.');
 };
 xhr.send();
}
// --------------------------- CORS end

function showUser(str) {
 if (str=="") {
 document.getElementById("txtHint").innerHTML = "Please make a selection.";
 return;
 } 
 document.getElementById("txtHint").innerHTML = "Getting data ...";
 
 xmlhttp.onreadystatechange=function() {
 if (xmlhttp.readyState==4 && xmlhttp.status==200) {
 var txt = xmlhttp.responseText;
 document.getElementById("txtHint").innerHTML = xmlhttp.responseText;
 }
 }
// Need full URL to page, since this HTML page is not on a server
 xmlhttp.open("GET","http://www.MYDOMAIN.com/getcars.php?q="+str,true);
 xmlhttp.send();
 }
// Start the CORS on startup
var url = "http://www.MYDOMAIN.com/";
var method = "GET";
createCORSRequest(method, url);
 </script>
 </body>
</html>

Create the App

1. In Terminal, starting from your project’s mail folder, type:

cordova build android

2. The apk file is built as platforms/android/ant-build/cordovaAPI-debug.apk

3. Install it on your device and navigate to the page. You should be able to select the power type and see a simple table listing of the cars.

First screen seen:

After selecting power type: