Looping through an array problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Looping through an array problem

Post by charp »

I actually have some working code, but I don't think it's very efficient. Here's the code:

Code: Select all

<?php
foreach ( range ('A' ,'Z' ) as $letter ) { 

echo '<tr><td colspan="2" bgcolor="#ccc"><b>- '.$letter.' -</b></td></tr>';

$result=mysql_query("SELECT * FROM $table ORDER BY last ASC");

while ($row=mysql_fetch_array($result)) {
	$first=$row['first'];
	$last=$row['last'];
	$ext=$row['ext'];

if (substr ($last ,0,1)==$letter) {

echo '
	<tr><td>'. $last .', '.$first.'</td>
	<td align="center">'.$ext.'</td></tr>';

} // end of if routine

} // end of while routine

} // end of foreach routine
?>
The above correctly prints out a table of names and phone extensions in alphabetical order. Before each alphabetical group of names, there is a row with the corresponding letter of the alphabet.

My question about efficiency is that my current code queries the database 26 times -- once for each letter of the alphabet. There has to be a better method.

Originally, I had the mysql_query statement outside of all the loops. I never quite got what I wanted even when I nested the foreach loop inside the while loop. The output was correct, but only for the last names beginning with A. No other names appeared even though the rows with the letters of the alphabet did show up correctly. I figured that after the first cycle through, the pointer was at the end of the $row array and that's why only the A names showed up. To fix that problem, I tried using the reset function for $row as well as $results.

Code: Select all

<?php
reset ($row);
?>
That solution never worked regardless of where I placed the reset command. Nothing I tried worked until I place the mysql_query inside the loop.


Like I said, I have something that works, so this post simply strives for a more efficient code. Can any one give a hint as to where and/or why I went wrong?

Thanks!
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

You are right that 26 queries is likely a waste of resources, depending a bit on how big the table is, if the full result is really big it may be a good idea to get build results in chunks to keep memory and swap space usage down, but if the total amount of rows is less, lets say under 10K names perhas I would just do them all at once... If this is accessed publically and frequently yoyu definetely want to cache the result, or even better, the resulting output (no changes to data, just pull a cached file).
There is a couple of other things that arent realy that efficient in that code as well, I would do something like this (untested, just braindump)

Code: Select all

<?php
// Do not query for more fields than you need
$r = mysql_query ('SELECT last,first,ext FROM tablename ORDER BY last,first,ext ASC');
if (!$r) die ('Bill Gates!!! '.mysql_error());
$previous = '';
$current = '';

// Don't create more elements than you need, associated array makes prettier code
while ( $row = mysql_fetch_assoc ($r) )
{
   $current = strtoupper(substr($row['last'],0,1));

   // If this is the first of this one...
   if ($current !== $previous)
   {
       // Do stuff to output the pretty letter
       echo '<h1>'.$current.'</h1>'."\n";
       $previous = $current;
   }
   
   // Print the row
   echo 'htmlspecialchars($row['last']) . $row['first'] . ' : '.$row['ext']) . '<br />'."\n";

}
I am not sure if it perhaps would be more efficient to let the db engine do the concatenation of the 2, even perhaps all 3, strings/fields, that would be a thing to test out....
Post Reply