extract more than one row in a WHILE loop

[dooper3]

I have a table "categories" in a table in my database and I want to show the data from it in an HTML page. I want to have 2 columns in each row of the HTML table, so I would have data from 2 separate rows in the mySQL table echoed in each HTML table row. At the moment, I can happily echo the table contents one at a time with:

while ($row = mysql_fetch_array($result)) {
extract($row);
echo ("<td><a href=\"adverts.php?cat=$id\" title=\"$category\">$category</a></td>");
}

but how can I extract the next row from the database and add it to the same table row on the website?

Thanks!

[fenway]

First, using extract() is dangerous -- you should use the proper $row['fieldName'] instead, trust me.

In order to do what you want, you'll have to keep track (in PHP) of the current "state" of your table -- i.e. whether you've outputted the first part or not -- and built the string appropriately, opening/closing TRs as necessarily. Alternatively (read: perferably), it would be much easier if you simply stored the entire result set as an array in PHP, and then went through it afterwards, two at a time.

Hope that helps.

[dooper3]

basically, the mysql table has the following data:

+-----------+
| id | fruit |
+-----------+
| 1 | Apples |
| 2 | Bananas |
| 3 | Pears |
| 4 | Peaches |
| 5 | Apricots |
| 6 | Kiwis |
+----------+

and i want to display the data on my website as follows:

+---------------------+
| Apples | Bananas |
| Pears | Peaches |
| Apricots | Kiwis |
+---------------------+

but the way i'm doing it, i'd need to do the whole table row within the WHILE loop in order for it to work, as otherwise i'd only be able to open the <tr> and have one <td> before closing the WHILE loop, which would obviously not give me the 2 column table I want to display. I'm basically trying to create a table of categories like the "sub-categories" on this page [a href=\"http://www.phpfreaks.com/tutorials.php\" target=\"_blank\"]http://www.phpfreaks.com/tutorials.php[/a]

I'm sorry if that's still confusing!

[fenway]

Not true -- open the TR if you're on an even row, and close it if you're on an add row. What's the problem? Like I said above, if you do this using your own for() loop, you can do it two at a time.

[danielforsyth]

[!--quoteo(post=341236:date=Jan 30 2006, 02:28 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 30 2006, 02:28 PM) [snapback]341236[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Not true -- open the TR if you're on an even row, and close it if you're on an add row. What's the problem? Like I said above, if you do this using your own for() loop, you can do it two at a time.
[/quote]


Fenway - I am having the same problem. Could you tell me what is wrong with this code? I want to have a 3x3 table with each cell showing a different value.

echo '<table border="1"><tr>';
while(list($price) = mysql_fetch_array($result))
{
for ( $i = 1; $i < 4; $i++ ) {
echo "<td>$price</td>";
}
echo '</tr><tr>';
}
echo '</tr></table>';

The results of the code above can be see here: [a href=\"http://root.danielforsyth.com/mysql/image_upload/view2.php?page=1\" target=\"_blank\"]http://root.danielforsyth.com/mysql/image_...iew2.php?page=1[/a]

I also tried the code below, but it had a different problem. It would put three columns in the first row, but four columns in the second row, as you can see at this link: [a href=\"http://root.danielforsyth.com/mysql/image_upload/view3.php?page=1\" target=\"_blank\"]http://root.danielforsyth.com/mysql/image_...iew3.php?page=1[/a]

echo '<table border="1"><tr>';
$thenum = 1;
while(list($price) = mysql_fetch_array($result))
{
if($thenum > 3){ $thenum = 1; echo '</tr><tr>'; }
else{ $thenum++; }
echo "<td>$price</td>";
}
echo '</tr></table>';

As I'm sure you can tell, I'm new at php. Any help would be appreciated. Thanks

[fenway]

The first code snippet you posted looks just fine (3 column across) -- the fact the $price is the same for each may have to do with your query. Post it here.

[danielforsyth]

Thanks - Here is the entire script:

// how many rows to show per page
$rowsPerPage = 9;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$query = "SELECT price FROM inventory ";

$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Error, query failed');

// print the inventory info in table
echo '<table border="1"><tr>';
while(list($price) = mysql_fetch_array($result))
{
for ( $i = 1; $i < 4; $i++ ) {
echo "<td>$price</td>";
}
echo '</tr><tr>';
}
echo '</tr></table>';
echo '<br>';

// how many rows we have in database
$result = mysql_query($query) or die('Error, query failed');
$numrows = mysql_num_rows($result);

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];

// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link

// print 'previous' link only if we're not
// on page one
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = ' [Prev] '; // we're on page one, don't enable 'previous' link
$first = ' [First Page] '; // nor 'first page' link
}

// print 'next' link only if we're not
// on the last page
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = ' [Next] '; // we're on the last page, don't enable 'next' link
$last = ' [Last Page] '; // nor 'last page' link
}

// print the page navigation link
echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;

mysql_close($conn);

[wickning1]

Both of the code snippets you posted in your first post were wrong. Use this one.

[code]echo '<table border="1">';
$i=0;
while($row = mysql_fetch_array($result)) {
   if ($i % 3 == 0) echo '<tr>';
   echo "<td>" . $row['price'] . "</td>";
   if ($i % 3 == 2) echo '</tr>';
   $i++;
}
// pad out the rest of the columns if result wasn't a multiple of 3
if ($i % 3 != 0) {
   $colspan = 3 - $i % 3;
   echo '<td colspan=$colspan>&nbsp;</td></tr>';
}
echo '</table>';[/code]
Or if you prefer, do this:
[code]echo '<table border="1">';
$i=0;
while($row = mysql_fetch_array($result)) {
   $row2 = mysql_fetch_array($result) or $row2['price'] = "&nbsp;";
   $row3 = mysql_fetch_array($result) or $row3['price'] = "&nbsp;";
   echo '<tr><td>' . $row['price'] . '</td><td>' . $row2['price'] . '</td><td>' . $row3['price'] . '</td></tr>';
}
echo '</table>';[/code]