CPSC225/Notes/Querying Web Databases (Morelli)

We look at a sequence of examples that describe how to work with a database in PHP.

Basic PHP/MySQL Examples
In this example, we connect to the MySQL server, which in this example is stored on localhost. Once the connection is established, we select a particular database and then run a query on the database. The results of the query are stored in an associative array, which is then displayed using a foreach loop.


 * Connecting to MySQL with PHP

In this example, the same steps are followed as in the first example. However, this time error checking is performed on each access to the database server.


 * Connecting to a DB with Error Handling

Database Handling in Sahana
In this next example, we show how database access is handled in Sahana.
 * Registering a Sahana Volunteer

Note that there is no explicit connection to the MySQL server. This is done globally when Sahana is initialized at start up. (See below for details.) Thus, to access the database, we retrieve its name from a global table using the following command: global $global; $db= $global['db']; We construct a query and store it in a string variable: $q = "select id from person_seq"; And then we use the PHP Execute command to run the query: $result=$db->Execute($q); The $result variable is an object, which has a number of attributes, including an attribute named fields, which is an associative array containing the values returned by the query. We can retrieve individual values from the array as follows; $id=$result->fields[0]; In other words, the volunteer's id is stored as $result->fields[0], that is, as the first (index 0) element of the fields array.

Initializing the Database in Sahana is Done Automatically
In Sahana, database initialization is handled by the system. The code for this is stored in:
 * handler_db.inc

Sahana uses the ADOdb Library. This is a library that allows PHP to interface with multiple databases, including MySQL, thus making PHP programs more portable. The ADOdb library provides an object-oriented interface to the database.

To establish a connection to a database, the following command is used in handler_db. Note that information is extracted from a $conf, a configuration array, which stores the name of the database server, the account name and password, and other necessary information: //Make the connection to $global['db'] $global['db'] = NewADOConnection($conf['db_engine']); $global['db']->Connect($conf['db_host'].($conf['db_port']?':'.$conf['db_port']:''),     $conf['db_user'],$conf['db_pass'],$conf['db_name']);

Once these commands are run, Sahana applications can get a reference to the database using the following commands: global $global; $db= $global['db']; Since $db is a reference to an ADOdb object, we refer to its methods using object-oriented syntax: $result=$db->Execute($q); These are the conventions we will use when we begin Sahana programming.