Need help for interesting result set problem

[Anon]

I'm a first-time PHP coder, moving a client's online catalogue to a database solution. Their ISP recently added PHP/MySQL support, so I figured I'd write the catalogue in PHP so I can test and debug it locally. I've hit a really interesting bug in my code that I can't figure out how to fix.

Here's what I'm trying to do: I'm trying to format my result set using mysql_fetch_row or mysql_fetch_array and place the results in a table 3 columns wide by however many rows it takes to walk through all the results. The problem is, I don't know how to step through the result sets multiple times in the same row.

I first tried this:

$sql = mysql_query ("SELECT * FROM genus WHERE catID = $catID");$myrow = mysql_fetch_row ($sql); print "<table border=0 cellpadding=5 cellspacing=0 width=\"100%\">\n";do { print "<tr>\n"; for ($x=0; $x < 3; $x++) { print "<td align=left valign=middle width=\"33%\">"; printf("<a href=\"%s?catID=%s&genID=%s\">%s</a>",$PHP_SELF, $myrow[0], $myrow[4], $myrow[1]); print "</td>\n"; }} while ($myrow = mysql_fetch_row($sql));

mysql_free_result($sql);printf("</table>\n");

but all that did was give me the same value in all three columns.

Then I tried this:

$sql = mysql_query ("SELECT * FROM genus WHERE catID = $catID");$myrow = mysql_fetch_row ($sql); print "<table border=0 cellpadding=5 cellspacing=0 width=\"100%\">\n";do { $x = 0; print "<tr>\n"; while (($myrow = mysql_fetch_row($sql)) && ($x <= 2)) { print "<td align=left valign=middle width=\"33%\">"; printf("<a href=\"%s?catID=%s&genID=%s\">%s</a>",$PHP_SELF, $myrow[0], $myrow[4], $myrow[1]); print "</td>\n"; }} while ($myrow = mysql_fetch_row($sql));

mysql_free_result($sql);printf("</table>\n");

This does, in fact, give me different values in each cell of my table, but unfortunately it drops a value before the first cell and after the last cell of each row (IOW, it pulls 5 values from my SQL query but only displays 3).

Is there a way for me to do what I want to do here? In reading for a solution to this problem, it seems that Postgre or FrontBase have a next_result() function that I imagine would work here; is there anything analogous for MySQL?

TIA for your help.

Chris Lepore

[Anon]

As the manual says:

<?php
mysql_connect($host, $user, $password);
mysql_select_db("database");
$result = mysql_query("select user_id, fullname from table");
while ($row = mysql_fetch_array($result)) {
echo "user_id: ".$row["user_id"]."<br>\n";
echo "user_id: ".$row[0]."<br>\n";
echo "fullname: ".$row["fullname"]."<br>\n";
echo "fullname: ".$row[1]."<br>\n";
}
mysql_free_result($result);
?>

Take a close look at what's happening here. You're looping through the result set. On each pass through the loop, you're fetching another result (i.e., the equivalent of next_result()).

In order to build a table that looks like this

1 2 3
4 5 6
7 8

... you're going to have to keep track of where you are on each line of the output table, and when it's full, generate the </tr><tr> to start another line.

The modulus operator comes in handy for this. See http://www.php.net/manual/en/language.operators.php#AEN4281