Quick StartMost PopularNew StuffPeopleTagsAbout Us

PHP and Working with Databases (for the Lazy Sod)

Rate this:
By JV (Contact - View My Woyano)
Published Sun 11 Feb 2007, 15851 Views, 7 Comments

Takes an in-depth look at the benefits of atomic operations, database abstraction and the use of classes, all in the hope of helping the reader realize the dream of being a really 'lazy sod' (albeit a successful one) when it comes to databases and PHP.

Introduction

Maybe it's just me, but after building database driven websites in PHP for the past six years I am starting to get more than a little tired of repeating myself. What I mean to say is...how many times, on how many different projects, and with how many different databases do I have to write something along the lines of:

    mysql_connect("localhost", "mysql_user", "mysql_password")
    or
    die("could not connect");

    mysql_select_db("mydb");

    $result = mysql_query("SELECT id, name FROM mytable");

    while ($row = mysql_fetch_array($result))
    {

      printf ("ID: %s Name: %s", $row[0], $row["name"]);
    }

    mysql_free_result($result);

Is there really any need to make my fingers type this kind of gunk so often? Surely there must be an easier way of working with databases than this, and if so, where do I find that kind of information?

You might say 'abstraction' and bring up names like PEAR and ADOdb, and you might be right (if the only thing important to you is being able to use the same code with different databases). The problem is you still have to type out lots of stuff whenever you want to deal with a database.

For example, here is the required code using ADOdb:

    include('adodb.inc.php');

    $conn = &ADONewConnection('access');

    $conn->PConnect('northwind');

    $recordSet = &$conn->Execute('select * from products');


      if (!$recordSet)
      {

        print $conn->ErrorMsg();
      }
      else
      {

        while (!$recordSet->EOF)
        {

          print $recordSet->fields[0].' '.$recordSet->fields[1].' '; $recordSet->MoveNext();
        }

      }

    $recordSet->Close();
    $conn->Close();

Nothing wrong with that, you say. I agree. You can use the same code on lots of different databases, you say. Fair enough, but I want to be even lazier than that. Ah, you say.

Atomic Operations

After extensively scouring the net for examples of how to be a really 'lazy sod' with databases and PHP, I found absolutely nothing that could help me. So like the old adage goes, "it's hard work being lazy", I set about putting some real mental

What I realized was that I should first break down the problem into a list of atomic operations and requirements (for those not in the know, an atomic operation is an operation that does one thing only - and does it well).

After all, when you stand back and look at it, what does working with databases using PHP really mean? As far as I can see, the majority of the time you only need four or five atomic operations to do 'most things' that you need to do:

  • Perform a non-result query such as Insert/Update/Commit
  • Get a single variable from the database
  • Get a single row/column from the database
  • Get a list of results from the database

In fact, when I think about it, all the commercial and non-commercial PHP projects that I have ever worked on have never needed any other operation. I'm not even sure you can do any other operation with a database...

Before you scream blue murder, I am not talking about SQL queries here, I am talking about the functions that wrap up the SQL queries. Because no matter how complex the SQL query you write, only one set of results will ever be returned -- and as we'll see, that's a good thing.

Query Result Sets

What are query result sets? Good question! I'm not sure I know exactly, but I do have some idea about what 'I think' they are and how they can be useful.

Let me try to explain. Imagine that we have a table called users and in that table there are three rows of data like the following:

    id name email
    1 joe joe@foo.com
    2 tyson tyson@bar.com
    3 maggie magie@simpsons.com

When we issue the query "SELECT * FROM users" the results we get back are:

    id name email
    1 joe joe@foo.com
    2 tyson tyson@bar.com
    3 maggie magie@simpsons.com

If we then extracted these results into an array, we would be the proud new owners of a query result set.

Here is an example:

$results[0] = Array
(
    [id] => 1
    [name] => "joe"
    [email] => "joe@foo.com"
    )
    $results[1] = Array
    (
      [id] => 2
      [name] => "tyson"
      [email] => "tyson@bar.com"
      )
    $results[2] = Array
    (
      [id] => 3
      [name] => "maggie"
      [email] => "maggie@simpsons.com"
      )

      As you can see, the main array ($results) is a numerical array with an index of array[n], and each element of the main array is an associative array equating to one row of results.

      This is useful because we can do things like print out the second field of each row simply by doing this:

        foreach ($results as $result)
        {
          echo $result['name'];
        }

      Another useful type of result set is as an indexed numerical array. The above results would then be expressed as:

        $results[0] = Array
        (
          [0] => 1
          [1] => "joe"
          [2] => "joe@foo.com"
        )
        $results[1] = Array
        (
          [0] => 2
          [1] => "tyson"
          [2] => "tyson@bar.com"
        )
        $results[2] = Array
        (
          [0] => 3
          [1] => "maggie"
          [2] => "magie@simpsons.com"
        )

      The disadvantage of this type of result set is that we no longer have access to column names. The advantage is that we don't need to know the column name in order to get access to a value.

      For example, we can print the second field of each row simply by coding the following (no matter what the column is called):

        foreach ($results as $result)
        {
          echo $result[1];
        }

      Perhaps the most useful result set of all is the one that uses the same overall format, with the exception being each row is an object instead of an array, like so:

        $results[0] = stdClass Object
        (
          [id] => 1
          [name] => "joe"
          [email] => "joe@foo.com"
        )
        $results[1] = stdClass Object
        (
          [id] => 2
          [name] => "tyson"
          [email] => "tyson@bar.com"
        )
        $results[2] = stdClass Object
        (
          [id] => 3
          [name] => "maggie"
          [email] => "magie@simpsons.com"
        )

      To print out values we can use object syntax, which has the advantage of working inside strings without needing any special formatting.

      So, to print out the second field of each row we could do this:

        foreach ($results as $result)
        {
          echo $result->name;
        }

      Here is an example of why it is easier to use object syntax rather than associative array syntax.

        // associative array style
        echo "Print this users " . $result['name'] . " and " . $result['email'];
        // object style
        echo "Print this users $result->name and $result->email";

      Use PHP Functions not DB Functions!

      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.)

      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 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:

      • A little bit of code to send a query to the database
      • 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:

      1. Download it.
      2. Change the database settings at the top of it.
      3. 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.

      Getting Even Lazier

      Have you ever tried to include the query results from one query inside another query? It can get quite hairy. For example, say we wanted to select a random user from the "our users" table.

      First, we would have to count how many user there were in the users table (query 1) and then using this value as our random max, we would select a random user (query 2). Here's one way of doing it using traditional methods:

          mysql_connect("localhost", "mysql_user", "mysql_password")
          or
          die("could not connect");

          mysql_select_db("mydb");

          $result = mysql_query("SELECT count(*) FROM users");

          $row = mysql_fetch_array($result);

          $num_users = $row[0];

          mysql_free_result($result);

          $result = mysql_query("SELECT name FROM users LIMIT rand(0, $num_users-1) ,1");

          $row = mysql_fetch_object($result);

          mysql_free_result($result);

          echo $row->name;
      Now, let's do exactly the same thing using our new class:
        include_once "ez_sql.php";

        $name = $db->get_var("SELECT name FROM users LIMIT ".rand(0,$db->get_var("SELECT count(*) FROM users")-1).",1");

        echo $name;
      You've got to admit, if you're a lazy sod, this class stuff makes a lot of sense. Another neat function that I use regularly is $db->get_col.

      This is useful because it returns the contents of one column as a one-dimensional numerical array. I use it for things like product lists that are stored in the database.

      If you really want to be lazy and you are positive that the query will always return results, you can even include the function directly inside your "for each" brackets (which you can do with any of the functions that return result sets):

        foreach ( $db->get_col("SELECT name FROM products") as $name)
        {
          echo $name;
        }

      Most times you will want to display something different depending on whether you have any results or not. The good news is that since we are using single functions to get our result, we can now include our query directly within an “if” statement, once again meaning that we need much less code.

      Here is an example:

        if ( $users = $db->get_results("SELECT name, email FROM users") )
        {

          foreach($users as $user)
          {
            echo $user->name;
            echo $user->email;
          }
        }
        else
        {
          echo "No users!";
        }

      Within the above “if” statement we are assigning a value to $users (the result set) and then evaluating if any value was assigned, all in the same line of code.

      Conclusion

      In this article we have spoken about the benefits of atomic operations, database abstraction and the use of classes.

      atomic operations - We have converted the usual gunk of working with databases into a small number of key functions that do no less and no more than needed.

      database abstraction - We have created a new set of functions that are entirely database independent (simply by switching one include file). Even better, these new functions require much less code than traditional methods of working with databases (due to our atomic operation implementation).

      classes - We have wrapped all of the above up into a nice, easy to use, class/object. This is handy because it allows us to do things like easily manage two concurrent server connections (should we so wish) as well as cache results and offers us neat functions that won't get confused with other global scope functions.

      Final Note

      If you would like to see more examples of how to work with the class I've written (ezSQL) and how it can make your life easier, just go and have a look at http://www.woyano.com/jv/ezsql.

      For a full-blown example of a busy website built using all the above concepts and the ezSQL class, visit http://www.woyano.com/.

      If you would like to help by porting ezSQL to another database platform please feel free to do so and mail me the code (jv AT woyano.com).

      I will include it in any future distributions. As stated, the only functions you need to change are $db->db, $db->select and $db->query. At the time of writing this article, ezSQL is available for mySQL, Oracle, Interbase, MS-SQL, Postgress, SQLite.




        This Item
        Category: Knowledge, Snippets, Technology
        Tags: ezsql, php, lazy, sod
        Share it
        Link to this item:
        Bookmark this item: RSS Feed

        People who liked this item

          7 Comments

        1. tobycatlin ~ 19 months ago
          0 votes thumbs up thumbs down
          jv i use your ezsql class loads and love it, because basically i am well lazy. I use it with Oracle and have written some methods to work with bind variables. i'll mail you the code when its done.
          [ reply ]
          1. JV ~ 19 months ago
            0 votes thumbs up thumbs down
            Sweeeeet :D
            [ reply ]
            1. teqqles ~ 17 months ago
              0 votes thumbs up thumbs down
              Nice article about a great project, consider this featured ;)
              [ reply ]
              1. hotbelgo ~ 12 months ago
                0 votes thumbs up thumbs down
                Hi,
                I'm having some trouble using ezsql to import utf text into a wordpress mysql database.

                I use this command
                $wpdb->query("INSERT INTO $wpdb->posts
                (post_author, post_date, post_date_gmt, post_content, post_title, post_status, comment_status, ping_status, post_name, guid)
                VALUES
                ('$post_author', '$post_date', DATE_ADD('$post_date', INTERVAL '$add_hours:$add_minutes' HOUR_MINUTE), '$content', '$title', 'publish', '$comment_status', '$ping_status', '$post_name', '$guid')");

                and the result is that for this segement of a $comment
                a goat’s cheese ‘head’, a good combination
                becomes
                a goat’s cheese ‘head’, a good combination
                Can you advise
                HB
                [ reply ]
                1. quaxxon ~ 11 months ago
                  0 votes thumbs up thumbs down
                  Wheres the OR DIE statement for the mysql_connect?

                  all I see is this bit:
                  // Try to establish the server database handle
                  else if ( ! $this->dbh = @mysql_connect($dbhost,$dbuser,$dbpassword,true) )

                  and when my database server was down, I was presented with the default connection error text that PHP produces, I couldnt change this error. As all there was, was a blank white screen, it would have been nice to be able to insert a default error page here instead.

                  But apart from this, cool class anyway
                  [ reply ]
                  1. tomyum ~ 11 months ago
                    0 votes thumbs up thumbs down
                    Hi ,
                    It's very good class,

                    For the ezsql 2.03,

                    I tried $db->hide_errors method. And use $EZSQL_ERROR as in the document
                    "// Using a custom error function

                    $db->hide_errors();



                    // Make a silly query that will produce an error

                    $db->query(“INSERT INTO my_table A BAD QUERY THAT GENERATES AN ERROR”);



                    // And another one, for good measure

                    $db->query(“ANOTHER BAD QUERY THAT GENERATES AN ERROR”);



                    // If the global error array exists at all then we know there was 1 or more ezSQL errors..

                    if ( $EZSQL_ERROR )

                    {

                    // View the errors

                    $db->vardump($EZSQL_ERROR);

                    }

                    else

                    {

                    echo “No Errors”;

                    }

                    "

                    And I got undefined variable rror for the $EZSQL_ERROR

                    How can I solved this problem?
                    Thanks
                    [ reply ]
                    1. New Member ~ 4 months ago
                      -1 vote thumbs up thumbs down
                      The article says

                      Here is an example of why it is easier to use object syntax rather than associative array syntax.

                      
      // associative array style
                      
      echo "Print this users " . $result['name'] . " and " . $result['email']; 
                      
      // object style
                      
      echo "Print this users $result->name and $result->email";



                      However, you can also use an alternative syntax to access the associative arrays...


                      
error_reporting(E_ALL);
                      
$row1 = array('var1' => 'testing', 'var2' => '123');
                      
echo "$row1[var1] $row1[var2]";
                      
echo "{$row1['var1']} {$row1['var2']}";
                      
$row2->var1 = 'testing';
                      
$row2->var2 = '123';
                      
echo "$row2->var1 $row2->var2";
                      [ reply ]
                      1. AdGuy
                        22 votes thumbs up thumbs down
                        This is my two cents...

                           
                        Hey you know AdGuy always gets the last word! ;)

                      Please Login to Add Your Comment   ..or..  

                      Replying to comment by