Pagination... it isn't just for breakfast anymore

[vozzek]

Hello all,

 

Some of the categories on my website have grown to 500+ items each, so it's time I paginated my master/detail display.  I'm trying to implement my own pagination php code, and I think I fried my brain - at least twice.  So if any of you can please help (without frying your own brain), I'd very much love it. 

 

When pulling things from my 'items' table, I want to display 40 items of data per page, but I want it broken down in a table so that 4 items appear side by side on each line (row) of the table.  Visually, it would look like this:

 

item1  item 2  item3  item4

item5  item 6  item7  item8

item9  item10 item11 item12...

item37 item38 item39 item40

 

I'm going to pass the pageno within the URL.  If the pageno is missing, it will be set to page 1:

<?php
if (isset($_GET['pageno'])) {  // Get the page number from the URL
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
}
?>

 

Then I get the total number of rows (items):

 

<?php
mysql_select_db($database_BLAH, $BLAH);
$sql = "SELECT * FROM items WHERE cat='whatever'";
$result = mysql_query($sql) or die(mysql_error());
$numrows = mysql_num_rows($result);  // Total number of items found
?>

 

I want to do 40 items per page, which when placed in a table would be 10 rows of 4.  I calculate the last page like so:

 

<?php
$rows_per_page = 40;
$lastpage = ceil($numrows/$rows_per_page);
?>

 

I check to make sure that the page number passed in the URL is within the proper range:

 

<?php
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
if ($pageno < 1) {
   $pageno = 1;
}
?>

 

Now I set the Limit clause:

 

<?php
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
$sql = "SELECT * FROM items WHERE cat='whatever' $limit";
$result = mysql_query($sql) or die(mysql_error());
?>

 

Okay, at this point I'd normally do something like:

<?php
while ($row = mysql_fetch_array($result)) {
// show results here...
}
?>

 

Here's where I'm confused.  I'm not sure how to go about setting up the <table> with the <tr> and <td> tags.  I know I need to probably do a <b>for</b> loop, incrementing the rows <tr's> from 1 to 4 and then the columns <td's> up until I run out of results.  But beyond that... I'm stumped as to how to code it.

 

Should I abandon my <b>while</b> loop?  Should I begin a row <tr> and then do four straight mySQL SELECTS in order to echo out the four straight items, then close the <tr> and do it over again?  If so, how would I know when I ran out of data?  Poor programming too, I'd imagine.

 

I hope I explained this okay.  If not, ask more questions and I'll try and clear it up.  Any help you guys can give is much appreciated.  Thanks!

 

 

 

[Barand]

This is the code I use for multi column output

<?php
include 'db.php';

define ("NUMCOLS",4);

$res = mysql_query("SELECT col1, col2 FROM mytable");

$count = 0;
echo "<TABLE border=1>";
while (list($col1, $col2) = mysql_fetch_row($res)) {

    if ($count % NUMCOLS == 0) echo "<TR>\n";  # new row

    echo "<TD>$col1<br>$col2</TD>\n";
    $count++;

    if ($count % NUMCOLS == 0) echo "</TR>\n";  # end row
}

# end row if not already ended

if ($count % NUMCOLS != 0) {
   while ($count++ % NUMCOLS) echo "<td> </td>";
   echo "</TR>\n";
}
echo "</TABLE>";

?>

[rhodesa]

If you want to do a table, you can keep a counter (i'm gonna call it $n), then you can test:

if($n && !($n%4))
  echo '</tr><tr>';

this will start a new row each time.

 

BUT, the better way in my opinion is to do it with DIV tags and CSS. Generate your info so it looks like:

<style type="text/css">
  .itemWrapper {
    width: 400px;
    overflow: auto;
  }
  .item {
    float: left;
    width: 96px;
    margin: 2px;
  }
</style>
<div class="itemWrapper">
  <div class="item">Info goes here</div>
  <div class="item">Info goes here</div>
  <div class="item">Info goes here</div>
  <div class="item">Info goes here</div>
  <div class="item">Info goes here</div>
  <div class="item">Info goes here</div>
</div>

[vozzek]

Both responses are good, but I have to admit I'm still confused.

 

I don't understand this line:  $res = mysql_query("SELECT col1, col2 FROM mytable");

 

I need to select an entire record when I do my query, not just certain columns from the record.  But I want to select four records at a time, since I want to print them as follows:

 

<table>

  <tr>

    <td>show info from record 1</td>

    <td>show info from record 2</td>

    <td>show info from record 3</td>

    <td>show info from record 4</td>

  </tr>

  <tr>

    <td>show info from record 5</td>

    <td>show info from record 6</td>

    <td>show info from record 7</td>

    <td>show info from record 8</td>

  </tr>

  etc...

 

Ideally, I'd LOVE to do it as rhodesa mentioned, with DIV tags and CSS, but I'm not sure where in the code my while loop would go...

 

 

[Barand]

I don't understand this line:  $res = mysql_query("SELECT col1, col2 FROM mytable");

 

I need to select an entire record when I do my query, not just certain columns from the record. 

 

Then adapt the example to meet your needs

 

Ideally, I'd LOVE to do it as rhodesa mentioned, with DIV tags and CSS, but I'm not sure where in the code my while loop would go...

After the query

[sasa]

i use this algoritam

<?php
echo "<table>\n";
$num_col = 4;
while ($row = mysql_fetch_array($result)){
echo "<tr>\n";
echo "<td>",$row['data'],"</td>\n";
for ($i = 1; $i < $num_col; $i++){
	if ($row = mysql_fetch_array($result)){
		echo "<td>",$row['data'],"</td>\n";
	} else {
		echo "<td> </td>\n";
	}
}
}
echo "</table>\n";
?>

[rhodesa]

I don't understand this line:  $res = mysql_query("SELECT col1, col2 FROM mytable");

I just assumed you knew what you wanted with the query, if you want all the columns for a record, just use SELECT * FROM mytable

 

I need to select an entire record when I do my query, not just certain columns from the record.  But I want to select four records at a time, since I want to print them as follows:

You can't select 4 records at a time. Think of it instead as selecting one record at a time, but after every 4 records, printing a </tr><tr>

[vozzek]

Success! 

But still one last question about doing it with <DIV> tags...

 

Thanks to all of you guys, I have it working perfectly in a table-driven format as follows:

 

<?php
echo "<br />";
echo "<table width='800' border='0' align='center' cellpadding='0' cellspacing='0' bgcolor='#FFFFFF'>\n";
echo "<tr>\n";
$num_col = 4;
while ($row = mysql_fetch_array($result)){
        echo "<td width='20'> </td>\n";
echo "<td width='160'>".$row['item']."</td>\n";
for ($i = 1; $i < $num_col; $i++){
	if ($row = mysql_fetch_array($result)){
	    echo "<td width='40'> </td>\n";
	    echo "<td width='160'>".$row['item']."</td>\n";			
	} else {
	    echo "<td width='40'> </td>\n";
	    echo "<td width='160'> </td>\n";
	}
}
echo "<td width='20'> </td>\n";
echo "</tr>\n<tr>\n";
}
echo "</tr>\n";
echo "</table>\n";
?>

 

Table is 800 wide, with four columns of 160 (images eventually go here), with 40px of white space between each column and 20px of space on each end.

 

This might be more of a question for the html forum, but can I do this using <DIV> tags alone or would I still need tables to separate the data into four columns?  I fully admit to being a DIV rookie.

 

 

 

[rhodesa]

You need to read up on CSS

 

this is how i would do it with DIVs:

<style type="text/css">
  .itemWrapper {
    width: 800px;
    background: #FFFFFF;
    overflow: auto;
  }
  .item {
    float: left;
    width: 160px;
    margin: 20px;
  }
</style>
<div class="itemWrapper">
<?php
while ($row = mysql_fetch_array($result)){
echo "<div class=\"item\">".htmlspecialchars($row['item'])."</div>\n";
}
?>
</div>

[vozzek]

rhodesa you absolutely ROCK. 

 

[rhodesa]

i know