Need help in advanced recordset pagination!

Report · Nov 29, 2009

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!

Report · Nov 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);

Report · Nov 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>

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

Could you help me, will you? Thank you!

Report · Nov 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.

Report · Nov 30, 2009

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!

Report · Dec 01, 2009

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.

Report · Dec 01, 2009

Dear David,

Yes, it works!!! Great! Thank you very much!

Incredible, for two months I read (RTFM), ask, and nobody tell me use $_GET for sending data.

The final question, is this method safe for sending data (sql-injection and so on)? URL in Adress bar looks like: http://localhost/data/search.php?dolghnost=%E1%F3%F5%E3%E0%EB%F2%E5%F0&regions=%C1%F0%E5%F1%F2&graph...

David, all the best and thank you very much! Bang Tut

Report · Dec 01, 2009

It should be fine, because you're passing everything to mysql_real_escape_string() before using the URL parameters in the SQL query.

Report · Dec 01, 2009

Dear David,

Thank you very much! All the best, Bang Tut!

Need help in advanced recordset pagination!

1 Correct answer