Pagination and where clause

[ijyoung]

My pagination script works fine until we put a where clause in.

Here it is:

<?php
$jc=$_REQUEST['category'];
// Number of records to show per page:
$display = 10;

// Determine how many pages there are.
if (isset($_GET['np'])) { // Already been determined.

$num_pages = $_GET['np'];

} else { // Need to determine.

//define variables

// Count the number of records
$query = "SELECT COUNT(*) FROM table WHERE category='$jc' ";
$result = mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_NUM);
$num_records = $row[0];

// Calculate the number of pages.
if ($num_records > $display) { // More than 1 page.
$num_pages = ceil ($num_records/$display);
} else {
$num_pages = 1;
}

} // End of np IF.

// Determine where in the database to start returning results.
if (isset($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}

// Make the query.
$query = "SELECT * FROM table WHERE category = '$jc' LIMIT $start, $display";
$result = mysql_query ($query); // Run the query.

// Table header.

// Fetch and print all the records.
$bg = '#eeeeee'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
//table with results

echo '</table>';

mysql_free_result ($result); // Free up the resources.

mysql_close(); // Close the database connection.

// Make the links to other pages, if necessary.
if ($num_pages > 1) {

echo '<br /><p>';
// Determine what page the script is on.
$current_page = ($start/$display) + 1;

// If it's not the first page, make a Previous button.
if ($current_page!= 1) {
echo '<a href="page.php?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
}

// Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i!= $current_page) {
echo '<a href="page.php?s=' . (($display * ($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="page.php?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a>';
}

echo '</p>';

} // End of links section.

?>

Can't work out how to pass the variable for the pages.

any thoughts?

Ian

[aaron_d]

Just add another variable to your pagination string that has the where clause in it.

[ijyoung]

Where tho?

I have tried all sorts. I tried putting page.php?category='$jc'&s= etc but that doesn't work (even tho the parameter is there).

Ian

[stee]

hi ijyoung,

you've a mix of quotation marks in your mysql query string below:

$query = "SELECT * FROM table WHERE category = '$jc' LIMIT $start, $display";

try using one style or the other, so either:

$query = "SELECT * FROM table WHERE category = \"$jc\" LIMIT $start, $display";

or:

$query = 'SELECT * FROM table WHERE category = \'$jc\' LIMIT $start, $display';

hope this helps,

stee

[ijyoung]

Why should that make any difference? The query as written works if there is no pagination. No quotations - no work.

Cheers

Ian

[jatar_k]

is this the query we are looking at?

$query = "SELECT * FROM table WHERE category = '$jc' LIMIT $start, $display";

that should be just fine, no probs with quotes there

is it just getting the vars to the next page that is giving you trouble?

>> I tried putting page.php?category='$jc'&s=

what isn't coming up? What do you end up with in the url? You should be able to add it the url with no problems. What kind of data does $jc hold?

[ijyoung]

nah! even if you hard code $jc it doesn't work.