October 22, 2002

PHP and Working with Databases (for the Lazy Sod)

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		amy		amy@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		amy		amy@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] => "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";

Page 3: Use PHP Functions not DB Functions!

Go to page: Prev 1 2 3 4 5 6 Next

Tools: