results in 2 table columns

Anon

hello!
am trying to get my results from a query and format the results into 2 table columns(5 per column). somehow i figured out a way to do it but can somebody pls tell me if the code shown below can be made better?

<?php
// get results and rows
$results = mysql_query("select * from table order by rank", $db_connect);
$numrows = mysql_num_rows($results);
$limit = 5;
$col = intval($numrows/$limit);
?>

<table border="1" width="100%"><tr><td>
<?
// first column
for ($i=1;$i<=$col;$i++)
{
list($id, $img, $comment, $date, $link, $rank) = mysql_fetch_row($results);
echo "$id, <img src=\"$img\">, $comment, $date, $link, $rank<br>";
}
?>
</td><td>
<?php
// second column
for ($i=i+1;$i<=$col;$i++)
{
list($id, $img, $comment, $date, $link, $rank) = mysql_fetch_row($results);
echo "$id, <img src=\"$img\">, $comment, $date, $link, $rank<br>";
}
?>
</td></tr></table>

and wat do i do if i want the results separated to multiple pages?
thanks so much

dstockto

There are a couple suggestions I would make for you to improve your code.

1) Use mysql_fetch_array() instead of mysql_fetch_row(). This way you don't need a call to list, but you can output your values like the following:

If you use ($row=mysql_fetch_array($results)) then your field names will be the index of $row.

echo ("$row['id'], $row['img'], $row['$comment'] .... ");

2) It looks like you want to have a maximum of 10 results per page. You can use the LIMIT SQL clause to accomplish this, and this will also help you with multiple pages.

Use
$results = mysql_query("select * from table order by rank limit 10, $offset", $db_connect);

This will limit you to 10 records per page. $offset should be the starting record, or 10 * page number. If you are on the 3rd page, the limit part would be "limit 10, 30" so it would get 10 records starting with the 30th record.

3) I'm not sure exactly how you want your columns formatted, but since you are sorting by rank, it looks like you want the top 5 starting in the left column and the lower 5 in the right column. What happens if you have less than 10 records on a page? Do you still want it to fill the left column first, or to split the records evenly between the two columns?

Also, do you want it to have a layout like follows:

1 2
3 4
5 6
...

instead of

1 5
2 6
3 7
4 8
5 9
(which is will be now)

If so, your code will get even easier. Let me know and I can show you either way...

4) In your code
// second column
for ($i=i+1;$i<=$col;$i++)
{

It looks like you are missing a $ in front of the second time you reference $i.

Hope this helps.

Dave

Anon

actually david... i originally thought of displaying the results like the first layout u showed but decided against cos this looping stuff is making my head hurt...
i'm interested in displaying the results like
1 2
3 4
5 6
...
if u can show me 🙂

i've edited some things to my code now so it comes in rows.

<?php
$results = mysql_query("select * from table order by rank", $db_connect);
$numrows = mysql_num_rows($results);
$limit = 2;
$col = intval($numrows/$limit);
?>

<?php
for ($i=1;$i<=$col;$i++)
{
$row = mysql_fetch_array($results);
echo "<td>$row[img], $row[comment], $row[date], $row[link], $row[rank]</td>";
}
?>

</tr><tr>
<?php
for ($i=i+1;$i<=$col;$i++)
{
$row = mysql_fetch_array($results);
echo "<td>$row[img], $row[comment], $row[date], $row[link], $row[rank]</td>";
}
?>
</tr></table>

but strange thing u pointed out about the missing $ because it returns me only one row if i were to put it in...

dstockto

Ok, here we go:

<?php

$max_records=10;
$query = "select * from table order by rank";

// We'll be passing in a variable called
// $offset if we not on the first page

if (!isset($offset))
$offset=0;

$query .= " LIMIT $max_records, $offset";
$results = mysql_query($query, $db_connect);
$numrows = mysql_num_rows($results);
// We don't really need $numrows right now
//$limit = 2;
//$col = intval($numrows/$limit);
?>

<table border=1><tr>
<?php
while ($row=mysql_fetch_array($results))
{
echo ("<tr><td>$row['img'], $row['comment'], $row['date'], $row['link'], $row['rank']</td>\n");

if ($row=mysql_fetch_array($results))
{
echo ("<td>$row['img'], $row['comment'], $row['date'], $row['link'], $row['rank']</td>\n");
}
else
{
echo ("<TD> </TD>\n");
}
echo ("</TR>\n");
}
?>
</table>

That's it..

The reason you were only getting one record back is because you are initializing $i to be $i+1, so after it has run through the first loop, it is 5. That means it will only run once on the second loop. Without the $i, i by itself probably evaluates to 0 which means the loop would initialize correctly.

Hope this helps.

Dave