Problem with pagination script

[eMonk]

Here's the code. It was working before I edited $strqry and $data_p

 

old queries:

 

$strqry = "SELECT id from model WHERE status = 'Active' ";

$data_p = "SELECT * FROM model WHERE status = 'Active' ORDER BY RAND($rand) $max";

 

new queries:

 

$strqry = "SELECT DISTINCT model.id from model
           INNER JOIN model_in_city ON (model_in_city.model_id = model.id)
           INNER JOIN city ON (city.city_id = model_in_city.city_id)
           INNER JOIN province on (city.province_id = province.id)
           WHERE province.name = '$region'
           AND model.status = 'Active' ";

$data_p = "SELECT DISTINCT(model.id), model.thumbnail, model.name, model.location FROM model
           INNER JOIN model_in_city ON (model_in_city.model_id = model.id)
           INNER JOIN city ON (city.city_id = model_in_city.city_id)
           INNER JOIN province on (city.province_id = province.id)
           WHERE province.name = '$region'
           AND model.status = 'Active'
   ORDER BY RAND($rand) $max";

 

pagination script:

 

<?php

session_start();

$rand = $_SESSION['rand'];

// session code here

if (isset($_GET['region'])) {
  $region = ucwords($_GET['region']);
}

?>

<-- html tables -->

<?php

$LIMIT = 5;

$page = (isset($_REQUEST['page']) ? intval($_REQUEST['page']) : 1);

$page = ($page < 1 ? 1 : $page);

include("includes/connect.php");

$LimitValue = $page * $LIMIT - ($LIMIT);
$strqry = "SELECT DISTINCT model.id from model
           INNER JOIN model_in_city ON (model_in_city.model_id = model.id)
           INNER JOIN city ON (city.city_id = model_in_city.city_id)
           INNER JOIN province on (city.province_id = province.id)
           WHERE province.name = '$region'
           AND model.status = 'Active' ";
$result = $db->query($strqry);
$TOTALROWS = $result->num_rows;
$NumOfPages = $TOTALROWS / $LIMIT;
$max = 'limit ' .($page - 1) * $LIMIT .',' .$LIMIT;
$data_p = "SELECT DISTINCT(model.id), model.thumbnail, model.name, model.location FROM model
           INNER JOIN model_in_city ON (model_in_city.model_id = model.id)
           INNER JOIN city ON (city.city_id = model_in_city.city_id)
           INNER JOIN province on (city.province_id = province.id)
           WHERE province.name = '$region'
           AND model.status = 'Active'
	   ORDER BY RAND($rand) $max";
$result_2 = $db->query($data_p);

echo "<p>Number of models found: ".$TOTALROWS."</p>";

while ($list = $result_2->fetch_assoc()) {
  echo "<table width=\"480\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">" ;
  echo "<tr>" ; 
  echo "<td width=\"50\"><a href=\"view.php?id=" . $list["id"] . "\"><img src=\"" . $list["thumbnail"] . "\" class=\"img\" border=\"0\" width=\"60\" height=\"60\"></a></td>" ;
  echo "<td valign=\"middle\" class=\"text\"><a href=\"view.php?id=" . $list["id"] . "\">" . stripslashes($list['name']) . "</a><br /><font color=\"#999999\">" . stripslashes($list['location']) . "</font></td>" ;
  
  echo "</tr>" ;
  echo "<tr>" ; 
  echo "<td colspan=\"2\"><hr class=\"hr\" /></td>" ;
  echo "</tr>" ;
  echo "</table>" ;
}

echo "<div id=\"paginating\" align=\"left\">Page:";

// Check to make sure we’re not on page 1 or Total number of pages is not 1
if ($page == ceil($NumOfPages) && $page != 1) {
  for($i = 1; $i <= ceil($NumOfPages)-1; $i++) {
    // Loop through the number of total pages
    if($i > 0) {
      // if $i greater than 0 display it as a hyperlink
      echo "<a href=\"".$_SERVER['PHP_SELF']."?page={$i}\">{$i}</a> "; // ?region=".strtolower($region)."&
      }
    }
}
if ($page == ceil($NumOfPages) ) {
  $startPage = $page;
} else {
  $startPage = 1;
}
for ($i = $startPage; $i <= $page+6; $i++) {
  // Display first 7 pages
  if ($i <= ceil($NumOfPages)) {
    // $page is not the last page
    if($i == $page) {
      // $page is current page
      echo " [{$i}] ";
    } else {
      // Not the current page Hyperlink them
      echo "<a href=\"".$_SERVER['PHP_SELF']."?page={$i}\">{$i}</a> "; // ?region=".strtolower($region)."&
    }
  }
}
echo "</div>";

?>

 

The problem is when I click on page 2 it comes up blank with 0 results.

 

The first page however shows the correct results which is currently 6.

 

When I use my old queries it works fine. Why doesn't the script like my new queries? They are fetching the results correctly from what I know. Does anyone see anything wrong with the code?

[jcbones]

What does this line return?

echo "<p>Number of models found: ".$TOTALROWS."</p>";

 

Also, have you echo'd out your queries and run them straight in the database console (or software)?

[eMonk]

It returns 6 which is correct on the first page but when I click on page 2 it returns 0 and doesn't display the final link.

[eMonk]

Any ideas why the script works with my old queries but not the new ones?

[eMonk]

The problem seems to be when I use $region:

 

if (isset($_GET['region'])) {
  $region = ucwords($_GET['region']);
}

 

I tried changing:

 

$_SERVER['PHP_SELF']

 

to

 

$_SERVER['REQUEST_URI']

 

because ?region= doesn't appear in the url on page 2 but it's still displaying a blank page.

[jcbones]

Sorry, been gone a few days.

 

You need to add the region to your url.

echo "<a href=\"?page={$i}&region={$region}\">{$i}</a> ";