Mobile Web Development with PHP, MySQL and jQTouch

Introduction

In this article, we'll be using a JavaScript database API for the client-side database. This API provides a simple method of storing, retrieving, and marinating user-supplied information. In addition, it provides a relational database model, and so can easily handle complex data. We can use standard SQL statements to perform all database-related tasks, such as creating databases and tables, as well as inserting, fetching, deleting, and updating rows in the tables.

Let’s start off by creating and opening a database. The code for creating and opening the client-side database is shown below:

var datab;
var shortName = 'tmpCart';
var version = '1.0';
var displayName = 'tmpCart';
var maxSize = 200000;
datab = openDatabase(shortName, version, displayName, maxSize);

The usage of the variables used in the code fragment is shown Table 1.

Table 1. Client-Side Database Variables

Variable	Usage
datab	Used to hold the reference to the database connection when it is established.
shortName	Stores the name of the database we will be creating on the client side.
version	Stores the version number assigned to the database. The version number is often needed for upgrades or changes to the database.
displayName	Stores the database name available to the user.
maxSize	Stores the expected size of our database in kilobytes. Should the size exceed the limit in this variable, we will be notified and asked if the increase should be allowed or denied.

The variable values defined in our code fragment say that we want to create a client-side database named tmpCart with a version number of 1.0 and a size limit of 200000 KB. After assigning these variable values, we can pass them to the openDatabase command, which actually creates the tmpCart database and stores the connection in datab variable.

The code fragment can also be written as:

datab = openDatabase('tmpCart', '1.0', 'tmpCart', 200000);

That is, we can directly specify the parameter values in the openDatabase function without using variables at all.

Now that we've created and opened a server-side database, let’s create a table in it.

Creating Client-Side Tables

In this example, we'll be using the client-side database for storing cart information. Let’s first create a table called shopcart in our client-side database tmpCart, as shown in this code fragment:

datab.transaction(

function(transaction) {

        transaction.executeSql(

'CREATE TABLE  IF NOT EXISTS shopcart ' +

               ' (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +

               ' cart_sess varchar(50), cart_isbn varchar(30),  cart_item_name

               varchar(100), cart_qty integer, cart_price float );'
         );
     }
 );

The JavaScript database API supports SQL transactions and all database queries must take place in the context of a transaction. To execute a standard SQL query, we need to call the executeSql method of the transaction object. To do this, we call the transaction method of the datab object and pass it an anonymous function. We then pass the transaction to the anonymous function so that we can call executeSql method of the transaction object.

The executeSql method creates a table called shopcart if it doesn't already exist. The table has six fields: id, cart_sess, cart_ISBN, cart_item_name, cart_qty and cart_price. The id field is the primary key. Its value cannot be null and is unique for each row of the table. We set this field to AUTOINCREMENT, so its value will increase by 1 for each table row added.

Inserting Rows in Client-Side Tables

Let’s assume that a user has dropped a book in the cart, so we now have some cart data to process—session id, ISBN, title, quantity, and price. This data is stored in the variables sid, ISBN, title, qty, and price respectively. We'll transfer this data to a row in the client-side shopcart table, as shown in the following code fragment:

datab.transaction(

function(transaction) {

        transaction.executeSql(

           'INSERT INTO shopcart (cart_sess, cart_isbn,  cart_item_name, cart_qty,

               cart_price) VALUES (?,?,?,?,?);',

           [sid, isbn, title, qty, price],

           function(){

           },

           displayerrormessage
        );
    }
 );

Note: Session id is used for session management—a mechanism used for tracking users visiting our web application. The Server generates a unique id for the current session.

First we use the executeSql method of the transaction object to execute a SQL query. We need to pass the data for five fields, or columns, to this method. The five question marks (?) in the VALUES parentheses are placeholders and take on the values in the array [sid, ISBN, title, qty, price]. The first question mark is replaced by the value in the sid variable, the second question mark is replaced by the ISBN value, and so on.

The array of values is followed by an anonymous function called the data callback function, which may contain statements to execute after successful execution of the SQL statement. For example, data callback may contain calls to other functions that display a confirmation message or navigate to an other panel. If we don't want any action to take place after the successful execution of the SQL statement, we can leave this function empty, as we have actually done in the code fragment.

The last part of the fragment, displayerrormessage, is a call to the error handling function we want to evoke if the SQL statement fails. Here is an example of displayerrormessage usage:

function displayerrormessage(transaction, error) {

   alert('Error:  '+error.message+' has occurred with Code: '+error.code);

   return true;

}

Two parameters are passed to the displayerrormessage function: the transaction object and the error object. The error object displays the error message and the error code. The reason for passing transaction object to the displayerrormessage function is to allow more SQL statements to be executed from within the function, if desired. The displayerrormessage function may return true to halt the execution and roll back the entire transaction, or false, in which case the transaction will continue.

Error Codes

Table 2 contains a quick look at the common error codes generated while executing a transaction on client-side database tables.

Table 2 Error Codes and Occurrence

Error Code	Occurs When
0	The transaction failed for other non-database-related error.
1	The transaction failed for other database-related error.
2	The transaction failed because the version of the database didn't match the one user requested.
3	The transaction failed because the data returned from the database was too large.
4	The transaction failed because either there was not enough storage space left or the user didn't want the database to grow beyond the existing limit.
5	The transaction failed because the transaction included a syntax error, number of parameters mismatch, statement modifying the database in a read-only transaction, and so on.
6	The transaction failed because of constraint failure, for example, assigning two rows have the same value in the primary field.

If we aren't interested in capturing errors, we can omit both data callback and error handling callback functions. If we do that, the code fragment will appear as follows:

datab.transaction(

    function(transaction) {

       transaction.executeSql(

          'INSERT INTO shopcart (cart_sess, cart_isbn,  cart_item_name, cart_qty,

              cart_price) VALUES (?,?,?,?,?);',

          [sid, isbn, title, qty, price]

       );

   }

);

For more information, refer to my book: “Beginning Web Development for Smartphones: Developing Web Applications with PHP, MySQL and jQTouch” available at Amazon.

History

• 30^th September, 2010: Initial version