Displaying query row results vertically.

is this the right way?

[deejay]

I have a table that has about thirty fields in it - it's a sod to display conventionally on a webpage in full because of the width of it.

However, the query I use will select a maximum of five rows to be displayed, so the logical thing is to display the table vertically = a table five columns wide (six, including a left heading column), and thirty rows long.

I've cracked how to display it, but want to run the abridged code by the eyes here and see if there is a more efficient way I could be doing this.

$blkmetricsquery = "SELECT a.* FROM a WHERE a.blockid='".$bid."' ORDER BY year DESC";
$blkmetricsresult = mysql_query($blkmetricsquery) or die("Error: " . mysql_error());
$num_rows=(mysql_num_rows($blkmetricsresult));
$x=0;
if(mysql_num_rows($blkmetricsresult) == 0){
echo("No block metrics registered");
} ELSE {

echo "<table><tr><td>Year</td>";
while ($row = mysql_fetch_array($blkmetricsresult)) {

echo '<td>'.$row['year'].'</td>';
$x++;
if($x==ceil($num_rows)){
echo '</tr>';
$x=0;
}
}

if(!mysql_data_seek($blkmetricsresult,$x))continue;
echo "<tr><td class=left>TCA</td>";
while ($row = mysql_fetch_array($blkmetricsresult)) {
echo '<td>'.$row['tca'].'</td>';
$x++;
if($x==ceil($num_rows)){
echo '</tr>';
$x=0;
}
}

if(!mysql_data_seek($blkmetricsresult,$x))continue;
echo "<tr><td class=left>TRV</td>";
while ($row = mysql_fetch_array($blkmetricsresult)) {
echo '<td>'.$row['trv'].'</td>';
$x++;
if($x==ceil($num_rows)){
echo '</tr>';
$x=0;
}
}

/* REPEAT FROM if(!mysql_data_seek.... FOR EACH FIELD TO BE DISPLAYED AS A ROW */

echo "</table>"; //end metrics table

Any input gratefully received.

[IanKelley]

Accessing the database 3 times is a very inefficient way to accomplish what you're after.

Instead, access the database once, store all the data you're going to need for all 3 rows in an array (or arrays) and then do your looping.

[deejay]

Thanks Ian - that's just the sort of input I was after.

Here's an indication of my novice status for you though - Can you please tell me which bits require a database call? The query itself is only there once, so presumably mysql_fetch_array or mysql_data_seek? both?

[IanKelley]

My mistake, I was skimming too quickly. I saw the additional mysql_fetch_array calls and assumed that there were additional queries to go with them.

So, really your code isn't so inefficient.

However, here are some ways to make it more efficient:

1) It appears that all 3 rows are of equal length. This means that you don't need to do the

if($x==ceil($num_rows))

check because each loop is the same number of iterations. You just need to output the </tr> after each loop is finished.

2) If, for whatever reason, the loops need to be different lengths, you would want to do the ceil() call outside of the loop. The return value is going to be the same each time so there's no reason to run the function each iteration of the loop.

3) Consider reading the TCA and TRV data into their own arrays during the first loop (the year loop), and then run through those arrays instead of using the mysql_fetch_array function.

By avoiding the two extra calls to fetch_array, and also all of the calls to mysql_data_seek, you would speed up the app slightly.

The reason being (note that I haven't actually benchmarked) that both functions have a lot of extra logic that needs to be run which you don't need. The difference won't be huge, it would only really matter if the app was getting a large number of hits.

[deejay]

Thanks so much for coming back to this Ian - great advice. :) Have it a bit tidier now.