Fetching Database Query Results PHP

For most of the databases supported by PHP, you can get columns in two ways. You can specify a value by row number and column name, or you can fetch rows one at a time in an array. For MySQL this involves mysql_result and mysql_fetch_row, respectively.

Using mysql_result is much slower than the fetch functions. PHP has to work harder to find the exact piece of data you need. First, the specified row must be referenced. Then the data in that row must be searched for a column with a matching name. You can imagine that executing mysql_result several times inside a loop can add up to a very slow script. Each call has to start at the beginning and find the appropriate data element.

Alternatively, you may fetch an entire row into an object, as I have done in most of the examples so far. This allows you to reference the exact element without searching through the entire data set. The challenge is to match up the results of the query with the array elements. If you have created a query such as

SELECT * FROM user u, employer e WHERE u.Employer = e.ID

you may have a hard time. You will have to examine the structure of each table to see the order of the columns. A better approach is to specify the columns you need, leaving out any you won't use. This would transform the query into something like

SELECT u.ID, u.Name, e.Name FROM user u, employer e WHERE u.Employer = e.ID

which specifies only three columns. You can be sure, regardless of the order of the columns in the table, that the user ID will be column zero.

Another advantage is that, since the resulting data have been narrowed to three columns, each fetch will be much smaller. The savings go all the way back to the database, because it will return only three pieces of data times the number of rows. None of the unused rows from the first version of the query will be sent through the network from the database server to PHP. In turn, PHP doesn't need to put them into the array.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

PHP Topics