Code 10x More Efficiently Using Data Access Objects: Part 1 - Code Samples, The Old Way
(Page 3 of 6 )
OK, with the logistics out of the way, let's get the total number of contacts currently in the table. To do this the old way, we would need to do something like this…
<?php
// get the total number of records in the table
$hostname = "localhost";
$username = "user";
$password = "password";
$databasename = "contact_db";
$query="SELECT COUNT(*) FROM Contacts"
$connection = @ mysql_connect($hostname, $username,$password)
or die("Cannot connect to database");
if (! mysql_selectdb($databasename, $connection))
die("Cannot open table");
if (!($result = @ mysql_query($strsql, $connection)))
die("Cannot select record");
$row = mysql_fetch_row($result);
$total_contacts = $row[0];
echo "Total contacts: ".$total_contacts;
?>
Ok, so it’s not as bad you may think. So let’s see how would we go about getting a record for the contact with id 15…
<?php
// get the record for the contact with id 15
$hostname = "localhost";
$username = "user";
$password = "password";
$databasename = "contact_db";
$query="SELECT * FROM Contacts WHERE id=15"
$connection = @ mysql_connect($hostname, $username,$password)
or die("Cannot connect to database");
if (! mysql_selectdb($databasename, $connection))
die("Cannot open table");
if (!($result = @ mysql_query($strsql, $connection)))
die("Cannot select record");
$row = mysql_fetch_array($result);
echo "Contact with id 15...<br>";
echo "Name: ".$row['first_name']." ".$row['last_name']."<br>";
echo "Phone: ".$row['phone']."<br>";
echo "Email: ".$row['email'];
?>
Hm… That wasn't so bad either. But, here comes the fun. Let's get all the contacts whose first name are "John"…
<?php
// get the records whose first name is "John"
$hostname = "localhost";
$username = "user";
$password = "password";
$databasename = "contact_db";
$query="SELECT * FROM Contacts WHERE first_name='John'"
$connection = @ mysql_connect($hostname, $username,$password)
or die("Cannot connect to database");
if (! mysql_selectdb($databasename, $connection))
die("Cannot open table");
if (!($result = @ mysql_query($strsql, $connection)))
die("Cannot select record");
echo "Contacts whose first name is 'John'<br>";
while($row = mysql_fetch_array($result)){
$row = mysql_fetch_array($result);
echo "Name: ".$row['first_name']." ".$row['last_name']."<br>";
echo "Phone: ".$row['phone']."<br>";
echo "Email: ".$row['email']."<br>";
}
?>
Well, that is still not so bad. However, what if I wanted to format the results in a nice way? To do that we would need to intersperse the code that returns the data from database with a lot of formatting code, and that leads to very sloppy code. We could possibly retrieve the entire recordset and pass it to a template which will make it look pretty, but again, we would need to add more code. What if there were no records returned? We would need to check for this and add some decision mechanism inside the code that communicates with the database. A good programming practice is to separate the data access layer of the program from the data manipulation and presentation layers. By doing that the business logic of your script is not intermixed with architecture specific, low level statements, such as native PHP database access functions. The data access and data processing separation is quite difficult and awkward without the use of some data access encapsulation.
With that said, how can we do these three tasks of returning a value, a record, and a recordset from the Contacts table nicer, neater, easier, and simply better?
Next: Database DAO to the Rescue >>
More PHP Articles
More By Oto Hlincik
