Display results in a 4 column table AND paginate, limiting to 2 rows

[jaxdevil]

Ok, this one is tricky (for me anyways, one of you guys probably knows this and can do it in your sleep) What I am trying to do is display the results of a mysql query with 8 items on a page and in the following format:

 

------------------------------------------------------------

|  first data  |  second data  |    third data    |  fourth data  |

-------------------------------------------------------------

|  fifth data  |  sixth data    |  seventh data  |  eigth data    |

-------------------------------------------------------------

                      <- previous page | next page ->

 

Now I know how to put query data into columns, and I know how to paginate query data, but they seem to conflict combining them, since they both effect the query itself, sort of driving the query.

 

Below is the code (which is on this site, phpfreaks) for making a multi-column query output. So how can I make that code also show only 8 results per page (i.e. 4 columns by 2 rows)?

 

<table cellspacing="3" cellpadding="3">
<?php
$query = "SELECT product FROM selling_items ORDER BY prod_id";
$result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error()); 
if($result && mysql_num_rows($result) > 0)
{
    $i = 0;
    $max_columns = 4;
    while($row = mysql_fetch_array($result))        
   {
       // make the variables easy to deal with
       extract($row);

       // open row if counter is zero
       if($i == 0)
          echo "<tr>";

       // make sure we have a valid product
       if($product != "" && $product != null)
          echo "<td>$product</td>";
    
       // increment counter - if counter = max columns, reset counter and close row
       if(++$i == $max_columns) 
       {
           echo "</tr>";
           $i=0;
       }  // end if 
   } // end while
} // end if results

// clean up table - makes your code valid!
if($i < $max_columns)
{
    for($j=$i; $j<$max_columns;$j++)
        echo "<td> </td>";
}
?>
</tr>
</table>

[jaxdevil]

No one has any idea how to do this? I am really at a standstill till I can figure this out, I tried combining the following with it but either I am not figuring out what variables on here should be changed or those two just don't work together. Anyone? Please help.

[sasa]

look pagination

[jaxdevil]

I can do pagination, and I can do the tableing, what I can't do is both combined. I found some code and modified it, it almost works. It does show pagination on the bottom, and shows the right number of pages for the query, AND it displays the data in a nice 4x2 table. What it DOES not do is actually show the data. Can someone see whats wrong and why it always shows 'nothing' (the nothing part is the else statement if it does not find anything to display, the funny thing is it does find the right number number of entries for the query, it just does not display the data.

 

<?php

mysql_connect(localhost,xxx_xxx,xxxxxx); // Use correct stuff there
mysql_select_db(xxx_xxx) or die(mysql_error()); // Use Database Name
$cat=products;

// Select total results for pagination
$result = mysql_query("SELECT count(*) FROM products");
$num_records = mysql_result($result,0,0);

// Set maximum number of rows and columns
$max_num_rows = 2;
$max_num_columns = 4;
$per_page = $max_num_columns * $max_num_rows;

// Work out how many pages there are
$total_pages = ceil($num_records / $per_page);

// Get the current page number
if (isset($_GET['page'])) $page = $_GET['page']; else $page = 1;

// Work out the limit offset
$start = ($page - 1) * $per_page;

// Select the results we want including limit and offset
$result = mysql_query("SELECT mod FROM products ORDER BY mod LIMIT $start, $per_page");
$num_columns = ceil(mysql_num_rows($result)/$max_num_rows);
$num_rows = ceil(mysql_num_rows($result)/$num_columns);

// Echo the results
echo "<table bgcolor=\"#D5D5D5\" border=\"2\">\n";
for ($r = 0; $r < $max_num_rows; $r++){
echo "<tr>\n";
for ($c = 0; $c < $max_num_columns; $c++){ // 1
	$x = $r * $max_num_columns + $c;
	if ($x < mysql_num_rows($result)){
//                   $y = mysql_result($result, $x, 0); // Commented out so I could show your example
                     $num = mysql_result($result, $x, 0);

                   $y = <<<HTML
<table align="center" border="0" cellpadding="0" cellspacing="0" width="145">
  <tr>
    <td width="16">{$row[mod]}</td>
  </tr>
</table>
HTML;

                }
                else {
                   $y = '<table align="center" border="0" cellpadding="0" cellspacing="0" width="145">
      <tr>
        <td width="16">nothing</td>
      </tr>
    </table>';
                }
	echo "<td>";
                echo "$y";
                echo "</td>";
}
echo "</tr>\n";
}

// Echo page numbers
echo "</table>\n";
for ($i=1;$i <= $total_pages;$i++) {
if ($i == $page) echo " $i "; else echo " <a href=\"?cat=$cat&page=$i\">$i</a> ";
}
?>

[jaxdevil]

Well, I finally figured it out. I copied the code below. I can not believe no one on here couldn't figure this out. I am certainly not the expert that a lot of you are so i don't know why no one helped me out, I have been trying to figure this out for 2 days and no one gave me any ideas.

 

<?php
mysql_connect(localhost,xxx_xxx,xxx); // Use correct stuff there
mysql_select_db(xxx_xxx) or die(mysql_error()); // Use Database Name
$cat=products;

// Select total results for pagination
$result = mysql_query("SELECT count(*) FROM products");
$num_records = mysql_result($result,0,0);

// Set maximum number of rows and columns
$max_num_rows = 2;
$max_num_columns = 4;
$per_page = $max_num_columns * $max_num_rows;

// Work out how many pages there are
$total_pages = ceil($num_records / $per_page);

// Get the current page number
if (isset($_GET['page'])) $page = $_GET['page']; else $page = 1;

// Work out the limit offset
$start = ($page - 1) * $per_page;

// Select the results we want including limit and offset
$result = mysql_query("SELECT `mod` FROM products ORDER BY `mod` LIMIT $start, $per_page");
$num_columns = ceil(mysql_num_rows($result)/$max_num_rows);
$num_rows = ceil(mysql_num_rows($result)/$num_columns);

// Echo the results
echo "<table bgcolor=\"#D5D5D5\" border=\"2\">\n";
for ($r = 0; $r < $max_num_rows; $r++){
echo "<tr>\n";
for ($c = 0; $c < $max_num_columns; $c++){ // 1
	$x = $r * $max_num_columns + $c;
	if ($x < mysql_num_rows($result)){
//                   $y = mysql_result($result, $x, 0); // Commented out so I could show your example
                     $num = mysql_result($result, $x, 0);

                   $y = <<<HTML
<table align="center" border="0" cellpadding="0" cellspacing="0" width="145">
  <tr>
    <td width="16">{$num}</td>
  </tr>
</table>
HTML;

                }
                else {
                   $y = '<table align="center" border="0" cellpadding="0" cellspacing="0" width="145">
      <tr>
        <td width="16">nothing</td>
      </tr>
    </table>';
                }
	echo "<td>";
                echo "$y";
                echo "</td>";
}
echo "</tr>\n";
}

// Echo page numbers
echo "</table>\n";
for ($i=1;$i <= $total_pages;$i++) {
if ($i == $page) echo " $i "; else echo " <a href=\"?cat=$cat&page=$i\">$i</a> ";
}
?>