- How Web Database Architectures Work
- Querying a Database from the Web
- Putting New Information in the Database
- Using Prepared Statements
- Using Other PHP-Database Interfaces
- Further Reading
- Next
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using Other PHP-Database Interfaces
PHP supports libraries for connecting to a large number of databases, including Oracle, Microsoft SQL Server, and PostgreSQL.
In general, the principles of connecting to and querying any of these databases are much the same. The individual function names vary, and different databases have slightly different functionality, but if you can connect to MySQL, you should be able to easily adapt your knowledge to any of the others.
If you want to use a database that doesn’t have a specific library available in PHP, you can use the generic ODBC functions. ODBC, which stands for Open Database Connectivity, is a standard for connections to databases. It has the most limited functionality of any of the function sets, for fairly obvious reasons. If you have to be compatible with everything, you can’t exploit the special features of anything.
In addition to the libraries that come with PHP, available database abstraction classes such as MDB2 allow you to use the same function names for each type of database.
Using a Generic Database Interface: PEAR MDB2
Let’s look at a brief example using the PEAR MDB2 abstraction layer. This is one of the most widely used of all the PEAR components. Instructions for installing the MDB2 abstraction layer can be found in the “PEAR Installation” section in Appendix A, “Installing PHP and MySQL.”
For comparative purposes, let’s look at how you could write the search results script differently using MDB2.
Listing 11.5. results_generic.php—Retrieves Search Results from the MySQL Database and Formats Them for Display
<html>
<head>
<title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>
<?php
// create short variable names
$searchtype=$_POST['searchtype'];
$searchterm=trim($_POST['searchterm']);
if (!$searchtype || !$searchterm) {
echo "You have not entered search details. Please go back and try again.";
exit;
}
if (!get_magic_quotes_gpc()) {
$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);
}
// set up for using PEAR MDB2
require_once('MDB2.php');
$user = 'bookorama';
$pass = 'bookorama123';
$host = 'localhost';
$db_name = 'books';
// set up universal connection string or DSN
$dsn = "mysqli://".$user.":".$pass."@".$host."/".$db_name;
// connect to database
$db = &MDB2::connect($dsn);
// check if connection worked
if (MDB2::isError($db)) {
echo $db->getMessage();
exit;
}
// perform query
$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
$result = $db->query($query);
// check that result was ok
if (MDB2::isError($result)) {
echo $db->getMessage();
exit;
}
// get number of returned rows
$num_results = $result->numRows();
// display each returned row
for ($i=0; $i <$num_results; $i++) {
$row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
echo "<p><strong>".($i+1).". Title: ";
echo htmlspecialchars(stripslashes($row['title']));
echo "</strong><br />Author: ";
echo stripslashes($row['author']);
echo "<br />ISBN: ";
echo stripslashes($row['isbn']);
echo "<br />Price: ";
echo stripslashes($row['price']);
echo "</p>";
}
// disconnect from database
$db->disconnect();
?>
</body>
</html>
Let’s examine what you do differently in this script.
To connect to the database, you use the line
$db = MDB2::connect($dsn);
This function accepts a universal connection string that contains all the parameters necessary to connect to the database. You can see this if you look at the format of the connection string:
$dsn = "mysqli://".$user.":".$pass."@".$host."/".$db_name;
After this, you check to see whether the connection was unsuccessful using the isError() method and, if so, print the error message and exit:
if (MDB2::isError($db)) {
echo $db->getMessage();
exit;
}
Assuming everything has gone well, you then set up a query and execute it as follows:
$result = $db->query($query);
You can check the number of rows returned:
$num_results = $result->numRows();
You retrieve each row as follows:
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
The generic method fetchRow()can fetch a row in many formats; the parameter MDB2_FETCHMODE_ASSOC tells it that you would like the row returned as an associative array.
After outputting the returned rows, you finish by closing the database connection:
$db->disconnect();
As you can see, this generic example is similar to the first script.
The advantages of using MDB2 are that you need to remember only one set of database functions and that the code will require minimal changes if you decide to change the database software.
Because this is a MySQL book, we use the MySQL native libraries for extra speed and flexibility. You might want to use the MDB2 package in your projects because sometimes the use of an abstraction layer can be extremely helpful.