 |
Technical Documents >> Database
Posted:
16 December 2002
Rating:
[N/A]
PHP and Working with Databases (Part II)
Hopefully by now you are beginning to get the idea of why query result sets are useful. The point is this, if you extract your results into an array, like the ones shown above, you no longer need to play around with lots of different types of database specific functions in order to work with extracted data. The only functions you need to use (99.9% of the time) are PHP functions. The really great thing about this is that you can be darn sure that your code is much more portable between databases. Another benefit is that you need so much less code! (Which is, of course, great news if you're a lazy sod like me.)
 |
|
|
.gif) |
RELATED ARTICLES |
|
|
|
|
|
SEARCH MAGAZINE |
|
|
|
|
MAGAZINE LINKS |
|
|
Use PHP Functions not DB Functions!
Lets look at some 'meat and potato' ways to work with query result sets using built-in PHP functions, bearing in mind that the result sets are in the same format as the ones described above.
Count how many rows of results have been returned
echo count($results); Print all results
foreach ( $results as $result )
{
echo $result->id;
echo $result->name;
echo $result->email;
} Print one row of results
echo $results[0]->id;
echo $results[0]->name;
echo $results[0]->email; Print one variable from one row of results
echo $results[0]->name; Re-sort results (sorts on first element of second dimension)
assort($results); // or any other sort function Interlude
Now that we have defined our main atomic functions and we have a new way of working with query results, we need a nice new code library that turns all the standard database gunk into a few neat, atomic functions. If we do this correctly, the only code we will ever need to write again is:
1) A little bit of code to send a query to the database
2) A little bit of code to deal with results
And that, my friends, is a lazy sod's dream come true. But Where's the Class?
What we really need is a PHP class that does all of the above and makes it very easy to do so. You guessed it, it just so happens that I've already made one! Of course, you don't have to use it -- you're welcome to make your own -- but for the sake of this article I am going to use it as an example of how to be as lazy as possible when working with databases.
The class in question is called ezSQL and is available from http://php.justinvincent.com/. To install it, you'll need to:
- Download it.
- Change the database settings at the top of it.
- Include it in the start of your PHP script.
You don't need to worry about what ezSQL is actually doing. All you need to know is that it takes care of gunk. It deals with connections, row fetching, freeing space, etc. Let's have a look at how easy it is to print out some results using this new class:
include_once "ez_sql.php"; $users = $db->get_results("SELECT * FROM users"); foreach ( $users as $user )
{
echo $user->name;
} ?>
As you can see, the new class takes care of everything gunk related. The connection stuff happens simply by including the class in the first place. The only thing we need to do is use one simple function that takes a SQL query as an argument and outputs a query result set. From that point forward, we simply use PHP functions to work with the data.
Abstraction
It's not hard to see how this makes it very easy to port your code from one database to another (as long as you use ANSI SQL in your database queries). In the ezSQL class itself there are only three functions that are database specific. I have already ported the class from mySQL to Oracle8. It took me about 30 minutes to change the three functions that needed changing. This means that I can run exactly the same script using either an Oracle8 database or a mySQL database. All I have to do is to replace the include file ez_sql.php.
A more obvious example might be:
if ( $server == "oracle8" )
include_once "oracle8/ez_sql.php";
else
include_once "mySQL/ez_sql.php"; $users = $db->get_results("SELECT * FROM users"); foreach ( $users as $user )
{
echo $user->name;
} ?>
Functions You Might Need
When I wrote ezSQL, the atomic functions I included were:
- $db->get_results -- Get multiple row result set from the database
- $db->get_row -- Get one row from the database
- $db->get_col -- Get one column from query based on column offset
- $db->get_var -- Get one variable, from one row, from the database
- $db->query -- Send a query to the database (and if any results, cache them)
- $db->debug -- Print last SQL query and returned results (if any)
- $db->vardump -- Print the contents and structure of any variable
- $db->select -- Select a new database to work with
- $db->get_col_info -- Get information about columns such as column name
- $db = new db -- Initiate new db object
The only database specific functions are $db->query, $db->select and $db->db (the constructor function). All other functions use standard PHP code only. The other point to note is that the two main functions that return rows of results take an optional argument to specify whether to return the results as an associative array, numerical array or object (which is the default). So, now let's have a look at a few more useful ways to work with this new class.
Say I want to validate a user's password against a password stored in the database. I could do this:
if ( $pwd == $db->get_var("SELECT pwd FROM users WHERE id = 2") )
{
echo "You are logged in!";
}
else
{
echo "Sorry. Bad user name or bad password.";
}
But wait, we can go one better. This time we can check the password while at the same time pulling in extra user details that we can use if the password is valid:
if ( $pwd == $db->get_var("SELECT pwd, name, id FROM users WHERE id = 2") )
{
$user = $db->get_row(null);
echo "Hello $user->name your ID is $user->id and you are now logged in!";
}
else
{
echo "Sorry. Bad user name or bad password.";
}
Neat! There are a number of reasons why this works. - The function $db->get_var() always returns the variable that is stored in the first column of the first row of the results (unless otherwise specified).
- Even though we have only extracted one variable using $db->get_var(), the query itself asked for three columns of information. The full results have been cached as a query result set within the db object, ready for any other ezSQL function to use.
- We have taken full advantage of this caching technique by using the function $db->get_row with a null query. This executes the main code of the function, but instead of getting the results from the database, it gets the results from the previously cached result set.
Reference: www.internet.com
Article ID: 452
|
