An introduction to the ADOdb class library for PHP - Page 2

June 17, 2003

Connecting to the database in your scripts

Before you rush off and add the above few lines to every script that needs them, take a step back. The point of the library is to make your applications flexible, and portable. Hardcoding the connection paramaters, or the path to the adodb library, is a general beginner mistake that immediately undermines part of the point of using a library like this. If your password changes, you would need to make changes to each of those scripts. Also, your password information is in the webtree, which is less secure in case of server hiccups. Rather, place the password information in a separate include file, perhaps in the same location as the rest of the adobdb installation. The same applies to the path value. If you had to rollout your application on a different server, you cannot guarantee the directory structure would be the same, so it pays to store that in only one location too. I suggest getting your webserver to automatically include a file containing this basic information each time a PHP script is called.

include("$adodb_path/db_values.inc.php");
include("$adodb_path/adodb.inc.php");
$db = NewADOConnection("$database_type");
$db->Connect("$host", "$user", "$password", "employees");

The database name would of course be different in many of your scripts, so that can stay hardcoded. You may also want to use persistent connections, instead of creating a new connection each time (this speeds up many web applications, but its effectiveness will differ with each database/application/load combination). To do this, replace Connect with PConnect. The file db_values.inc.php contains something like:

<?
$database_type="mysql";
$host = "localhost"; // the database server 
   and web server are on the same machine
$user = "ian"
$password = "let_me_in"
?>

The value for $adodb_path can be automatically included in a prepend file. You can set your version of PHP to automatically prepend a file in the php.ini configuration file, for example as follows:

; Automatically add files before or after any PHP document.
auto_prepend_file = /usr/local/build/apache/www/tool_lib/defaults.inc
auto_append_file =

The file defaults.inc would contain the value for $adbdb_path, for example:

<?
$adodb_path = "/usr/local/build/apache/www/tool_lib/";
?>

There are other ways of doing it, but I find this way relatively painless when migrating.

Selecting from a database

As with any well-developed library, and with the native PHP functions themselves, there are a number of ways to access results, depending on what you plan to do with them. Here's one way:

$sql = "SELECT surname, age FROM employees";
$rs = &$db->Execute($sql);
if (!$rs) {
  print $db->ErrorMsg(); // Displays the error message if no results could be returned
}
else {
  while (!$rs->EOF) {
    print $rs->fields[0].' '.$rs->fields[1].'<BR>'; 
     // fields[0] is surname, fields[1] is age
    $rs->MoveNext();  //  Moves to the next row
  }  // end while
} // end else

In this example, $rs->fields is an array containing the values returned. The array index is numeric by default, but you can also make it an associative array, by specifying the mode before you Execute the query, as follows:

$sql = "SELECT surname, age FROM employees";
$db->SetFetchMode(ADODB_FETCH_ASSOC); // Return associative array
$rs = &$db->Execute($sql);
if (!$rs) {
  print $db->ErrorMsg(); // Displays the error message if no results could be returned
}
else {
  while (!$rs->EOF) {
    print $rs->fields['surname']." ".$rs->fields['age']."<BR>";
    $rs->MoveNext();  //  Moves to the next row
  }  // end while
} // end else

An alternative way to browse results is to return each row as on object. ADOdb has a function called FetchNextObject(), which does this, as well as automatically move to the next row.

$sql = "SELECT surname, age FROM employees";
$db->SetFetchMode(ADODB_FETCH_ASSOC); // Return associative array
$rs = &$db->Execute($sql);
if (!$rs) {
  print $db->ErrorMsg(); // Displays the error message if no results could be returned
}

// loop through results
while ($row = $rs->FetchNextObject()) {
	// The field names need to be uppercase
	print $row->SURNAME." ".$row->AGE."<BR>";
}

Inserting and updating records

The basic INSERT is quick and easy, with the syntax identical to when you SELECT:

$sql = "INSERT INTO employees (surname, age) values ('Clegg','43')";
if (!($db->Execute($sql))) {
	print 'Error inserting: '.$db->ErrorMsg().'<BR>';
}

The real advantage of the library is that it allows you to insert records into different databases with the same syntax, where this would normally be impossible. There are two common situations where this occurs. Firstly, quoting. All quotes need to be escaped so as not to cause a syntax error, but some databases insert a single quote, others two single quotes. So instead of using a PHP function such as addslashes(), as many of you may be used to, you can rather use ADOdb's qstr(), which takes a string, and returns it correctly formated for the DBMS you're using.

Secondly, dates. Many databases accept differing, incompatible formats for their date type. ADOdb has a function called DBDate(), which accepts a date in either Unix timestamp, or ISO (Y-m-d) format, and converts it to whatever format is appropriate for your specific database. Below is an example showing these two functions in action:

$employee_surname = $db->qstr("d'Angelo");
$arrival_time = $db->DBDate(time());
// The above two functions also add the enclosing quotes, so, $arrival_time, not '$arrival_time'
$sql = "INSERT INTO employee_arrival (arrival_time,surname) values ($arrival_time,$employee_surname)";
if (!($db->Execute($sql))) {
	print 'Error inserting: '.$db->ErrorMsg().'<BR>';
}

You update in exactly the same way, for example:

$sql = "UPDATE employees SET age='44' WHERE id='121')";
if (!($db->Execute($sql))) {
	print 'Error updating: '.$db->ErrorMsg().'<BR>';
}

This has just been the bare bones - next time we'll look at some of the more advanced features ADOdb provides. If I've whetted your appetite and you can't wait, I suggest you take a look at PHP Everywhere site, home of ADOdb, which contains much helpful information.

» See All Articles by Columnist Ian Gilfillan








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers