Code 10x More Efficiently Using Data Access Objects: Part 1 - Database DAO to the Rescue
(Page 4 of 6 )
So what is this mysterious database data access object? Simply said, it is a class that enables us to encapsulate the most frequently used methods to receive and send data to and from a database. It provides a streamlined interface for database connectivity.
With that said, the adapter should be fairly flexible, and really easy to use. The database DAO I am about to introduce to you is a part of a collection of classes eloquently named the "Core Classes." These classes form the functional basis for a framework called <eof> or "Event-driven Object-oriented Framework," which is a rapid application development framework for web applications coded in PHP.
On a side note, when we (the <eof> developers) first created this class, we happened to name it "MySQLAdapter" and at the time it made a lot of sense. Since then, however, we realized that the term "Adapter" is well coined in the OOP world as an "Adapter Design Pattern" and the functionality of our class does not have much in common with the described purpose of the design pattern.
Now, while we do not refer to the class as an "Adapter" anymore, the name of the class stuck. Hopefully this does not create a lot of confusion and perhaps we will change the name of the class down the road.
Anyway, let's get back to our "MySQL DAO" that will do the dirty work of exchanging data with the database for us. Here is the code that is required to retrieve the total number of contacts currently in the Contacts table…
<?php
// get the total number of records in the table
require("core/mysqladapter.phpclass");
$objDB = new MySQLAdapter("localhost;contact_db;user;password");
$total_contacts = $objDB->getValue("SELECT COUNT(*) FROM Contacts");
echo "Total contacts: ".$total_contacts;
?>
What? That's it? There must be something missing! What just happend here? These are some common responses I get from people when I show them how the MySQL DAO works. So let's go over what actually happened here.
First, we need to include the MySQL DAO class (MySQLAdapter) that provides the data access functionality. Then we instantiate a data access object $objDB. As you can see, we are passing a parameter to the class constructor. This is a connection string that contains the host, database, user, and password all in one string separated by semicolon (";"). I have to admit that this is to a certain degree inspired by the idea of ADODB connection string introduced by Microsoft, and is the most convenient way to specify the database access. Once we have an instance of the data access object available, all we need to do is just use it to retrieve data.
To get a single value back from a database we just call the "getValue" method of the DAO and pass it a SQL query that will produce this value (if for some reason multiple values are selected from a table, only the first one is returned). Everything else is handled by the object. Pretty neat, isn't it?
OK, now we see that returning a single value from a database is fairly simple. Let's see how we return a record and a recordset…
<?php
// get the record for the contact with id 15
require("core/mysqladapter.phpclass");
$objDB = new MySQLAdapter("localhost;contact_db;user;password");
$row = $objDB->getRecord("SELECT * FROM Contacts WHERE id=15");
echo "Contact with id 15...<br>";
echo "Name: ".$row['first_name']." ".$row['last_name']."<br>";
echo "Phone: ".$row['phone']."<br>";
echo "Email: ".$row['email'];
?>
<?php
// get the records whose first name is "John"
require("core/mysqladapter.phpclass");
$objDB = new MySQLAdapter("localhost;contact_db;user;password");
$records = $objDB->getRecords("SELECT * FROM Contacts WHERE first_name='John'");
if (!is_array($records))
{
echo "There are no Johns within the contacts";
}
else
{
echo "Contacts whose first name is 'John'<br>";
foreach($records as $row)
{
echo "Name: ".$row['first_name']." ".$row['last_name']."<br>";
echo "Phone: ".$row['phone']."<br>";
echo "Email: ".$row['email'];
}
}
?>
That's it! Not much more work than returning a single value. And, as you may have noticed, we even built in a quick check for a case when no Johns exist in our database.
When we want to retrieve a single record from the database we can use the "getRecord" method of the DAO and pass it a SQL query that will select a single record in the database (if for some reason multiple records are selected from a table, only the first record is returned). The record is returned as a one dimensional array with both the field indexes and the field names as keys. Similarly, when retrieving an entire recordset, we would use the "getRecords" method of the adapter object and pass it a SQL query that selects one or more records. The recordset is returned as an array of records, where each record is an array in itself (a two dimensional array). If no records are returned, a string is returned which lets us know that no matching records were found for our query. We used this feature in the above example to perform that "elegant" check whether there are any John records coming back from the database.
Next: Convenience and Performance >>
More PHP Articles
More By Oto Hlincik
