Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

PHP

Posted Oct 22, 2002

PHP and Working with Databases (for the Lazy Sod) - Page 2

By DatabaseJournal.com Staff


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!




PHP Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 



















Thanks for your registration, follow us on our social networks to keep up-to-date