Using the PHP Database Object Library Functions

When you know your application will run in a MySQL environment, it makes sense to use the php_mysqli library functions to interact with the database. However, there may come a time when you want to write an application that can work using other databases as well. PHP supports quite a few database libraries, each of which interacts with a specific database server. The downside, though, is that you'd need to create a different version of your application for each database server.

Alternatively, you can use the PHP Database Object (PDO) library functions. PDO allows you to use the same code to interact with any type of underlying database server. You just need to specify the database server used for the connection, and PDO does the rest!

To open a PDO connection to a database, you instantiate a PDO object:

$con = new PDO(

"mysql:host=localhost;dbname=<em>mydata</em>;charset=utf8',

'<em>username</em>', '<em>password</em>');

The databases your application can connect to depends on which PDO database drivers are loaded in the PHP server. To determine which drivers are available, use the getAvailableDrivers() static method:

$list = PDO::getAvailableDrivers();

foreach ($list as $db) { echo "$db<br>\n";

}

After you connect to the database, to submit a simple query, use the query() method:

$query = "SELECT bidderid, lastname, firstname, address

FROM bidders";

foreach($con->query($query) as $row) {

$bidderid = $row['bidderid']; $lastname = $row['lastname'];

$firstname = $row['firstname']; $address = $row['address'];

echo "$bidderid - $lastname, $firstname<br>$adress<br><br>\n";

}

Unlike the php_mysqli library, the PDO library query() method returns the actual data records from the result set. You don't need to fetch the results. Each time you call the query() method, it returns the next data record from the result set.

You can also use prepared statements with the PDO library to help filter input data:

$sql = "INSERT INTO bidders (bidderid, lastname,

firstname, address) VALUES (?, ?, ?, ?)";

$stmt = $con->prepare($sql);

$stmt->bindParam(1, 100, PDO::PARAM_INT);

$stmt->bindParam(2, 'Blum', PDO::PARAM_STR);

$stmt->bindParam(3, 'Rich', PDO::PARAM_STR);

$stmt->bindParam(4, "123 Main St.; Chicago, IL 60633",

PDO::PARAM_STR);

$stmt->execute();

Using this method, you can submit multiple data records by binding each data set to the prepared statement and executing them individually.

With the PDO library, you can now write a single application that will work with any underlying database server your customers need to use!