correctness/efficiency

maUru

I am aware of 3 different methods for accessing data with php/mysql, eg to print of all the IDs from a table:

All this code has not been checked, just typed in, it may have syntax/typing errors etc, but the concept remains.

Precode:

mysql_connect("host", "username", "password");
mysql_select_db("database");
$query = "SELECT * FROM table";
$results = mysql_query($query);
$row = mysql_fetch_assoc($results);
$numrows = mysql_num_rows($results);

First method, direct accessing of the record data and assigning to an intermittent variable:

for ($i = 0; $i < $numrows; i++) {
  $varID = mysql_result($results,$i,'ID');
  echo $varID;
}

Second method, getting an array of the data by use of an internal pointer on the mysql server which has to be reset once data is fetched:

do {
  echo $row['ID'];
} while ($row = mysql_fetch_assoc($results));
if ($numrows > 0) {
  mysql_data_seek($results, 0);
  $row = mysql_fetch_assoc($results);
}

Third method, pulling the data once from the server into an array which can be used throughout the program:

for ($i = 0; $i < $numrows; i++) {
  $records[$i] = $row;
  $row = mysql_fetch_assoc($results);
}
foreach ($records as $key => $val) {
  echo $val['ID'];
}

At the moment I use the first method since it allows easy usage of multiple tables with same field names and a simple easy to remember variable that can be used in the html, eg: say we have two tables, 'names', 'pizzas' and they both have an 'ID' field which for whatever reason we want to link the tables using a 'SELECT FROM pizzas, names *', we can differentiate data by doing as so:

$varNameID = mysql_result($results,$i,"names.ID");
$varPizzaID = mysql_result($results,$i,"pizzas.ID");

However, wouldn't the array (3rd) method be more efficient since the data is only fetched from the server once, and then can be used locally without connecting to the server again? Is the first or second method the prefered one by experts and which is faster/more efficitent?

Also I am not aware of how to do the above trick with the Pizzas and Names using the second method, is that at all possible?

Thanks a lot for reading this. Any feedback is greatly appreciated.

khaldryck

I use often the 3rd method as it allows me to do complex search in a single page and then use templates for the output.

the templates are made here by someone who knows only basic programmtion (learned only the while and the for loop)

and so when there is a ned to change all the design of something, we don't need to change the code at all.

mattr

Do not SELECT * if you are only using the ID. SELECT ID instead.

maUru

Originally posted by mattr
Do not SELECT * if you are only using the ID. SELECT ID instead.

Yea that's pretty standard, I didn't know what code I was going to write in the post so I decided to keep it simple.

Anyone know how to use the same field name in a linked list eg with the Pizzas/Names example?

nnichols

However, wouldn't the array (3rd) method be more efficient since the data is only fetched from the server once, and then can be used locally without connecting to the server again?

The third method is not the most efficient but it can be very useful in certain circumstances. When you execute a query php stores the result set, and so you are not accessing the db server multiple times. If using an array to store all the results you should use "mysql_free_result()" as soon as the data is in the array to free up the system resources.

Is the first or second method the prefered one by experts and which is faster/more efficitent?

Neither one is preferred, nor more efficient, it depends entirely on how the data is being used. Personally I would only ever use "mysql_result" if I was only accessing a single column result. If you are accessing more than one column in the result set you should always use the "mysql_fetch_" fns as they are slightly more efficient and "MUCH quicker".

If in doubt run test cases using a couple of different methods and run a timer in the script to see if there is any significant difference.

From the manual:
mysql_fetch_array() is an extended version of mysql_fetch_row(). In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys.

If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you must use the numeric index of the column or make an alias for the column. For aliased columns, you cannot access the contents with the original column name (by using 'field' in this example).

Example:
select table1.field as foo table2.field as bar from table1, table2

Hope this helps 😉

maUru

Excellent post nnichols, answered all my question, cheers. 🙂