3 comlumn display results with pagination

[bluewaves]

I have the following code that works perfectly.  I would like to add an easy pagination feature that

limits the number of items, counts the number of records and makes links and pages for the next page.

 

<?php

include('../myconnection.inc');

  $database = "mydatabasename";

  $cxn = mysql_connect($host,$user,$password)

        or die ("couldn't connect to server");

  mysql_select_db($database);

  $query = "SELECT * FROM baghaus ORDER BY Price";

  $result = mysql_query($query)

            or die ("Couldn't execute query.");

 

define ("NUMCOLS", 3);

 

$res = mysql_query("SELECT Thumbnail, Link, Name, Price FROM baghaus WHERE custom3='Marc Jacobs' ORDER BY Price");

 

$count = 0;

echo "<center><TABLE border=0 cellpadding='10'>";

while (list($Thumbnail, $Link, $Name, $Price) = mysql_fetch_row($res))

{

 

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

 

    echo "<TD width='150' valign='top'><a href='$Link'><img src='$Thumbnail' border='0'><br>$Name</a><br>\$$Price</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>";

 

?>

 

I have this script that works for counting and pagination...but I need to merge the two and don't know how:

 

<?php

 

  include('../myconnection.inc');

  $database = "voipvide_checks";

  $cxn = mysql_connect($host,$user,$password)

        or die ("couldn't connect to server");

  mysql_select_db($database);

 

// Determine how many records there are:

if (isset($_GET['np'])) {

 

$num_pages = (int) $_GET['np'];

 

} else {

 

// Find out how many records there are.

$q = "SELECT COUNT(*) FROM checks";

 

// Get the number.

$r = mysql_query($q);

list($num_records) = mysql_fetch_array($r, MYSQL_NUM);

mysql_free_result($r);

 

// Calculate the number of pages:

if ($num_records > $display_number) {

$num_pages = ceil ($num_records/$display_number);

} else {

$num_pages = 1;

}

 

}

 

// Determine where in the database to start returning results:

if (isset($_GET['s'])) {

$start = (int) $_GET['s'];

} else {

$start = 0;

}

 

// Define the query:

$q = "SELECT * FROM checks WHERE SubCategory='Check' ORDER BY Name ASC LIMIT $start, $display_number";

 

// Run the query:

$r = mysql_query ($q);

 

// Display all of the records:

while ($row = @mysql_fetch_array ($r, MYSQL_ASSOC)) {

  echo "<table width='380' cellpadding='10'><tr><td width='400'><a href='{$row['Link']}'>

    <font face='Arial' size='2'><img border='0' src='{$row['Image']}'><P>{$row['Name']}</a><P>

    {$row['Description']}</font></td>

    <td> <br /></td></tr></table>\n";

  }

 

// Clean up:

mysql_close($cxn);

 

// Make the links to other pages, if necessary:

if ($num_pages > 1) {

 

echo '<hr width="50%" align="left" />';

 

// Determine what page the script is on:

$current_page = ($start/$display_number) + 1;

 

// If it's not the first page, make a Previous button:

if ($current_page != 1) {

echo '<a href="browse_checks.php?s=' . ($start - $display_number) . '&np=' . $num_pages . '"> Previous</a>';

}

 

// Make all the numbered pages:

for ($i = 1; $i <= $num_pages; $i++) {

 

// Don't link the current page:

if ($i != $current_page) {

echo '<a href="browse_checks.php?s=' . (($display_number * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';

} else {

echo $i . ' ';

}

 

}

 

// If it's not the last page, make a Next button:

if ($current_page != $num_pages) {

echo '<a href="browse_checks.php?s=' . ($start + $display_number) . '&np=' . $num_pages . '"> Next</a> ';

}

 

}

 

?>

</body>

</html>

 

[corbin]

You can use limit in combination with get variables to do something like the following.

 

$start = $_GET['page'];
if(!is_numeric($start) || $start <= 0) {
$start = 0;
}

$q = mysql_query("SELECT Thumbnail, Link, Name, Price FROM baghaus WHERE custom3='Marc Jacobs' ORDER BY Price LIMIT {$start}, <number of rows you want to pull>");

 

Then after you do that just loop through those results....

 

Then use a COUNT() statement in mysql to see the number of rows, and make links for those pages accordingly, incrementing ?page as you go ;p.

[bluewaves]

This is what I've tried.  This actually produces pagination at the bottom of the page and the three columns, but it displays all of the products instead of limiting them to say 9 or whatever.

 

If you could help me get this script working, I'd be very appreciative. 

 

<?php

 

 

// Number of records to show per page:

$display_number = 15;

 

// Connect to MySQL:

 

  include('../misc.inc');

  $database = "voipvide_checks";

  $cxn = mysql_connect($host,$user,$password)

        or die ("couldn't connect to server");

  mysql_select_db($database);

 

// Determine how many records there are:

if (isset($_GET['np'])) {

 

$num_pages = (int) $_GET['np'];

 

} else {

 

// Find out how many records there are.

$q = "SELECT COUNT(*) FROM trendy WHERE PRICE > 10 AND PRICE < 50";

 

// Get the number.

$r = mysql_query($q);

list($num_records) = mysql_fetch_array($r, MYSQL_NUM);

mysql_free_result($r);

 

// Calculate the number of pages:

if ($num_records > $display_number) {

$num_pages = ceil ($num_records/$display_number);

} else {

$num_pages = 1;

}

 

}

 

// Determine where in the database to start returning results:

if (isset($_GET['s'])) {

$start = (int) $_GET['s'];

} else {

$start = 0;

}

 

 

 

//Defined 3 columns:

 

define ("NUMCOLS", 3);

 

$q = mysql_query("SELECT Thumbnail, Link, Name, Price FROM trendy WHERE PRICE > 10 AND PRICE < 50 ORDER BY Price");

$start = $_GET['page'];

if(!is_numeric($start) || $start <= 0) {

$start = 0;

}

 

 

$count = 0;

echo "<center><TABLE border=0 cellpadding='10'>";

while (list($Thumbnail, $Link, $Name, $Price) = mysql_fetch_row($q))

{

 

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

 

    echo "<TD width='150' valign='top'><a href='$Link'><img src='$Thumbnail' border='0'>'$Name'[/url]

    \$$Price</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>";

 

 

// Clean up:

mysql_close($cxn);

 

// Make the links to other pages, if necessary:

if ($num_pages > 1) {

 

echo '<hr width="50%" align="center" />';

 

// Determine what page the script is on:

$current_page = ($start/$display_number) + 1;

 

// If it's not the first page, make a Previous button:

if ($current_page != 1) {

echo '<center><a href="paginationtrendy1.php?s=' . ($start - $display_number) . '&np=' . $num_pages . '"> Previous</a>';

}

 

// Make all the numbered pages:

for ($i = 1; $i <= $num_pages; $i++) {

 

// Don't link the current page:

if ($i != $current_page) {

echo '<a href="paginationtrendy1.php?s=' . (($display_number * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';

} else {

echo $i . ' ';

}

 

}

 

// If it's not the last page, make a Next button:

if ($current_page != $num_pages) {

echo '<a href="paginationtrendy1.php?s=' . ($start + $display_number) . '&np=' . $num_pages . '"> Next</a> ';

}

 

}

 

?>