PHP and Databases for the Lazy Sod - Atomic Operations

(Page 2 of 6 )

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 effort into resolving this problem.

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:

1. Perform a non-result query such as Insert/Update/Commit
2. Get a single variable from the database
3. Get a single row/column from the database
4. 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:



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



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] => "amy"
[email] => "amy@foo.com"
)

$results[1] = Array
(
[id] => 2
[name] => "tyson"
[email] => "tyson@bar.com"
)

$results[2] = Array
(
[id] => 3
[name] => "maggie"
[email] => "magie@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] => "amy"
[2] => "amy@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] => "amy"
[email] => "amy@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";

Next: Use PHP Functions not DB Functions! >>
 

More MySQL Articles
More By Justin Vincent

Comments On: PHP and Databases for the Lazy Sod

>>> Be the FIRST to comment on this article!