Skip to main content
B
Bang Tut
Participant
November 29, 2009
Answered

Need help in advanced recordset pagination!

  • November 29, 2009
  • 1 reply
  • 740 views

Dear Sir!

Could you help me in pagination (Dreamweaver functionality) with advanced Recordset?
All data form posts gathering in array (need because some variables may be empty):
$Wheres = array();
And after join to recordset:
$query_Recordset1 = "SELECT * FROM vacancy";
if (count($Wheres))
{
$query_Recordset1 .= " WHERE ".join( ' AND ', $Wheres );
$query_Recordset1 .= "  ORDER BY dolghn ASC, date DESC";
}
This way it works great but only on the first page (results 1-10), when click next page (11-20) working only this part of query "SELECT * FROM vacancy" and all array data ignored. Is it possible solve this problem, and where I could read about this?

Thank you!

This topic has been closed for replies.
Correct answer David_Powers

Dear David,

Thank you for help!

I make it as you say but this code still working only on the first page, and next page give now such error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') > NOW() ORDER BY dolghn ASC, date DESC LIMIT 10, 10' at line 1

------------- Full page code --------------

<?php require_once('Connections/staffer1.php'); ?>
<?php

$Wheres = array();
if(!empty($_POST['regions']))
{
$Wheres[] = "region = '".mysql_real_escape_string( $_POST['regions'] )."'";
}
if(!empty($_POST['sexs']))
{
$Wheres[] = "sex = '".mysql_real_escape_string( $_POST['sexs'] )."'";
}
if(!empty($_POST['opyts']))
{
$Wheres[] = "opyt = '".mysql_real_escape_string( $_POST['opyts'] )."'";
}
if(!empty($_POST['dolghnost']))
{
$Wheres[] = "dolghn = '".mysql_real_escape_string( $_POST['dolghnost'] )."'";
}
if(!empty($_POST['graphiks']))
{
$Wheres[] = "graphik = '".mysql_real_escape_string( $_POST['graphiks'] )."'";
}
if(!empty($_POST['age1']))
{
$age1 = $_POST['age1'];
}
else
{
$age1 = 15;
}
if(!empty($_POST['age2']))
{
$age2 = $_POST['age2'];
}
else
{
$age2 = 100;
}
$Wheres[] = "age > $age1 && age < $age2";

if(!empty($_POST['days']))
{
$days = $_POST['days'];
}
$Wheres[] = "DATE_ADD(date, INTERVAL $days DAY) > NOW()";


if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;   
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_staffer1, $staffer1);

// Логика
$query_Recordset1 = "SELECT * FROM vacancy";
if (count($Wheres))
{
$query_Recordset1 .= " WHERE ".join( ' AND ', $Wheres );
$query_Recordset1 .= "  ORDER BY dolghn ASC, date DESC";
}
//
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $staffer1) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1);
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_Recordset1") == false &&
        stristr($param, "totalRows_Recordset1") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>

<body>
<?php echo $query_Recordset1; ?>
<table border="1" cellpadding="0" cellspacing="0">
  <tr>
    <td>id</td>
    <td>num</td>
    <td>date</td>
    <td>region</td>
    <td>zarp</td>
    <td>sex</td>
    <td>age</td>
    <td>educ</td>
    <td>opyt</td>
    <td>opyt1</td>
    <td>opyt2</td>
    <td>dolghn</td>
    <td>graphik</td>
    <td>email</td>
    <td>tel</td>
  </tr>
  <?php do { ?>
    <tr>
      <td><?php echo $row_Recordset1['id']; ?></td>
      <td><?php echo $row_Recordset1['num']; ?></td>
      <td><?php echo $row_Recordset1['date']; ?></td>
      <td><?php echo $row_Recordset1['region']; ?></td>
      <td><?php echo $row_Recordset1['zarp']; ?></td>
      <td><?php echo $row_Recordset1['sex']; ?></td>
      <td><?php echo $row_Recordset1['age']; ?></td>
      <td><?php echo $row_Recordset1['educ']; ?></td>
      <td><?php echo $row_Recordset1['opyt']; ?></td>
      <td><?php echo $row_Recordset1['opyt1']; ?></td>
      <td><?php echo $row_Recordset1['opyt2']; ?></td>
      <td><?php echo $row_Recordset1['dolghn']; ?></td>
      <td><?php echo $row_Recordset1['graphik']; ?></td>
      <td><?php echo $row_Recordset1['email']; ?></td>
      <td><?php echo $row_Recordset1['tel']; ?></td>
    </tr>
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
<p> <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, 0, $queryString_Recordset1); ?>">First</a> <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, max(0, $pageNum_Recordset1 - 1), $queryString_Recordset1); ?>">Previous</a> <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, min($totalPages_Recordset1, $pageNum_Recordset1 + 1), $queryString_Recordset1); ?>">Next</a> <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, $totalPages_Recordset1, $queryString_Recordset1); ?>">Last</a> <?php echo ($startRow_Recordset1 + 1) ?>  <?php echo min($startRow_Recordset1 + $maxRows_Recordset1, $totalRows_Recordset1) ?>  <?php echo $totalRows_Recordset1 ?></p>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

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

Don't understand what is wrong. The first page works, next and last giving error. Where is my mistake? Thank you!


The problem is that you're populating the $Wheres array from the $_POST array. The $_POST array is populated only when the form is first submitted. Consequently, it works on the first page, but is empty when you use the recordset navigation bar.

The simple way to fix this is to change the method of the search form from POST to GET. You will also need to change $_POST to $_GET in the code at the top of your page. The recordset navigation bar code automatically preserves the URL parameters, so the values should be passed correctly to the subsequent pages.

1 reply

David_Powers
David_Powers
Inspiring
November 29, 2009

You need to create the recordset in the ordinary way, and then add the Recordset Navigation Bar to the page. That will create all the code necessary to paginate through the recordset. Finally, add the code that adds your array to the end of the query. The part that builds the query will eventually look like this (I'm leaving out all the other code):

$Wheres = array();
// Build your array

$query_Recordset1 = "SELECT * FROM vacancy";
if (count($Wheres))
{
  $query_Recordset1 .= " WHERE ".join( ' AND ', $Wheres );
  $query_Recordset1 .= "  ORDER BY dolghn ASC, date DESC";
}
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
B
Bang TutAuthor
Participant
November 29, 2009

Dear David,

Thank you for quick answer!

I add the Recordset Navigation Bar to the page (and the first page working well but others... Ignoring array):

<a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, 0, $queryString_Recordset1); ?>">First</a>

<a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, max(0, $pageNum_Recordset1 - 1), $queryString_Recordset1); ?>">Previous</a>

<a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, min($totalPages_Recordset1, $pageNum_Recordset1 + 1), $queryString_Recordset1); ?>">Next</a>

<a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, $totalPages_Recordset1, $queryString_Recordset1); ?>">Last</a>

But I don't understand how I can add array to the end of the query.

Could you help me, will you? Thank you!

David_Powers
David_Powers
Inspiring
November 30, 2009

The code that you have posted here is from the recordset navigation bar links in the body of the page. You add your array to the recordset at the top of the page as indicated in my previous post.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices