how to make this pagination work with search results?

lovephp · July 10, 2016

Mates help out in this please on pagination for search results would appreciate it and also if my codes look ok or not to you guy?

<?php
  $title = clean($_REQUEST['title']);
  $name = clean($_REQUEST['name']);
  $description = clean($_REQUEST['description']);
 
  $criteria = array();
            if($title !='')
            {
                $criteria[] = "title = '".$title."'";
            }elseif($title =='All')
                criteria[] = "title = ''";
            }
            if($name !='')
            {
                $criteria[] = "name = '".$name."'";
            }elseif($name =='All')
                criteria[] = "name = ''";
            }
            if($description !='')
            {
                $criteria[] = "description = '".$description."'";
            }elseif($description =='All')
                criteria[] = "description = ''";
    }
 
  try {
    $total = $db->query('SELECT COUNT(*) FROM table WHERE '.implode(' AND ', $criteria).'')->fetchColumn();
    
    global $per_page;
    
    $pages = ceil($total / $per_page);
    $page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
        'options' => array(
            'default'   => 1,
            'min_range' => 1,
        ),
    )));

    $offset = ($page - 1)  * $per_page;
    $start = $offset + 1;
    $end = min(($offset + $per_page), $total);

   $stmt = $db->prepare('SELECT * FROM table WHERE '.implode(' AND ', $criteria).' ORDER BY id DESC LIMIT :per_page OFFSET :offset');

 
    $stmt->bindParam(':per_page', $per_page, PDO::PARAM_INT);
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    
    if ($stmt->rowCount() > 0) {
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $iterator = new IteratorIterator($stmt);

        foreach ($iterator as $row) {
                
                echo $row['id'];
        }
    echo '<div id="pagination">
    <div id="pagiCount">';
        $prevlink = ($page > 1) ? '<span id="prev"><a href="?page=1" title="First page">First</a></span> <span id="prev"><a href="?page=' . ($page - 1) . '" title="Previous page"><<</a></span>' : '';
        $nextlink = ($page < $pages) ? '<span id="next"><a href="?page=' . ($page + 1) . '" title="Next page">>></a></span> <span id="next"><a href="?page=' . $pages . '" title="Last page">Last</a></span>' : '';
        echo '<div id="paging"><p><small>', $prevlink, ' Page ', $page, ' of ', $pages, '', $nextlink, '</small></p></div>';        
    echo '</div></div>';
    } else {
        echo '<p>Nothing found.</p>';
    }
} catch (Exception $e) {
    echo '<p>Nothing found.</p>';
}

?>

thanks alot

lovephp · July 10, 2016

something like this will work right?

<a href="?page=' . ($page + 1) . '"&$title="'.$_GET'['title'].'" ">

but the thing is supposing i do something like filter i select just title and name or description is blank will the pagination still work?

Barand · July 10, 2016

If the user wants "All", leave that column out of the criteria.

mac_gyver · July 10, 2016

you need to build the query string part of the url (the part after the ?), using http_build_query() and any existing $_GET parameters -

$q = $_GET; // get a copy of any existing $_GET parameters - do this once before the start of your pagination links

// when you build each link - first, last, previous, next do this -
$q['page'] = // set the page value to the value you want for the link you are building from whereever the code is getting the value now
$qs = http_build_query($q,'','&'); // build the query string part of the url
echo "<a href='?$qs'>the label you want for the link you are building from whereever the code is getting the lable now</a>"; // output the link

lovephp · July 10, 2016

If the user wants "All", leave that column out of the criteria.

so i remove the elseif then?

will this be right?

if($name !='')
            {
                $criteria[] = "name = '".$name."'";
            }

lovephp · July 10, 2016

you need to build the query string part of the url (the part after the ?), using http_build_query() and any existing $_GET parameters -

$q = $_GET; // get a copy of any existing $_GET parameters - do this once before the start of your pagination links

// when you build each link - first, last, previous, next do this -
$q['page'] = // set the page value to the value you want for the link you are building from whereever the code is getting the value now
$qs = http_build_query($q,'','&'); // build the query string part of the url
echo "<a href='?$qs'>the label you want for the link you are building from whereever the code is getting the lable now</a>"; // output the link

quite confusing for me let me try and get back

lovephp · July 10, 2016

you need to build the query string part of the url (the part after the ?), using http_build_query() and any existing $_GET parameters -

$q = $_GET; // get a copy of any existing $_GET parameters - do this once before the start of your pagination links

// when you build each link - first, last, previous, next do this -
$q['page'] = // set the page value to the value you want for the link you are building from whereever the code is getting the value now
$qs = http_build_query($q,'','&'); // build the query string part of the url
echo "<a href='?$qs'>the label you want for the link you are building from whereever the code is getting the lable now</a>"; // output the link

this bit is given to me by someone

$q = $_GET;
unset($q["page"]);
$currentlink = strtok($_SERVER["REQUEST_URI"],"?") . "?" . http_build_query($q, "", "&");
$prevlink  = $currentlink . (empty($q) ? "" : "&") . "page=" . ($page - 1);
$nextlink  = $currentlink . (empty($q) ? "" : "&") . "page=" . ($page + 1);
$lastlink = $currentlink . (empty($q) ? "" : "&") . "page=$pages";

but im confused on how to apply it to my existing codes

QuickOldCar · July 11, 2016

You want to see if page is set in the GET and not empty, otherwise set it to 1

if(isset($_GET['page'] && trim($_GET['page'] != '')){
    $page = $_GET['page'];
}else{
    $page = 1;
}

QuickOldCar · July 11, 2016

You also want to never use a negative value so if less than 1 make it one again.

if($page < 1){
    $page = 1;
}

mac_gyver · July 11, 2016

the OP's code is actually setting a default value of 1, if there is no page get parameter, validating the value as an integer (within php's integer range), and is limiting the value between 1 and the total number of pages.

this bit is given to me by someone ...

don't copy or use code unless you understand it. this bit of code is repetitive and unnecessarily bespoke/hard-coded, and isn't using the correct entity version of an & for links on a page.

your code still has a number of unnecessary and problematic things in it, which i am betting you have been informed of most of them in previous threads -

1) use the correct input variables where you expect the data to be. use $_GET, not $_REQUEST.

2) i don't know what your clean() function code is, but i'm betting it isn't effective and is best left out.

3) the try/catch block will catch errors with the prepare/execute methods. this means that there was a sql syntax error or wrong bound parameters. this does not mean that the query ran, but didn't match anything. there's no need for a local try/catch block unless you are specifically handling a type of query error in your code, such as a duplicate key error from and INSERT/UPDATE query.

4) the global keyword only has meaning when used inside a function definition and even there it indicates bad coding. don't use global $var_name;

5) the code to build the WHERE term for the sql query statement should not be repeated. build the term in a variable, then use that variable each place it is needed.

6) the external data you are putting into the WHERE term needs to be handled using a prepared query with bound input parameters.

7) the $start and $end variables are not used in the posted code and should be removed.

the sql query statements should be built in a php variable, this supports debugging (you can echo/log the statement, though the pdo statement object has a queryString property you can use) and it leads to writing general purpose code.

9) you need to set the default fetch mode in your connection code, so that you don't need to specify it every time you run a query.

10) the pdostatement object is already traversable. you don't need and shouldn't be applying the new IteratorIterator() to it.

11) for queries that will return a set of data, just fetch all the data using the fetchAll() method. you can then use count() on the data to find how many rows the query matched (the ->rowCount() method doesn't work with SELECT queries for all database types, so, using fetchAll()/count() will work regardless of which type of database you are using.)

12) to produce the pagination output, you need to test how many pages were calculated, not if the data retrieval query returned any rows (there's a condition that's mentioned in the code where you would need to produce the pagination links even if the data retrieval query didn't match any data)

13) your <span> tags are reusing the same id='...' value, which is invalid. if you are doing something unique with each span (which is doubtful), you would need to use unique id values. if you are not doing anything with the span, don't include it in the markup.

the following example code shows how you might do this. this code adds the following features -

1) shows how to do a data driven design, where you define somewhere (an array or database table) a set of data that general purpose code operates on. this eliminates repeating block after block of same functioning code. this also has the benefit of helping to implement DRY (Don't Repeat Yourself) programming, since it eliminates the repetitive logic.

2) it implements the suggestion of leaving a particulate field out of the sql query statement when it is the 'ALL' choice or when the choice isn't present at all in the $_GET parameters.

3) shows how you would apply http_build_query() when building each link.

// define the possible search fields - this is used to produce a data driven/dynamic design, where you don't write out block after block of code that only differs in the value it operates on
$search_fields = array('title','name','description'); 

$and_terms = array(); // WHERE terms to be AND'ed
$params = array(); // bound input parameters for a prepared query

foreach($search_fields as $field)
{
    if(isset($_GET[$field]) && $_GET[$field] != 'ALL') // only if the field is set and it's not 'ALL'
    {
        // add the search field to the WHERE terms
        $and_terms[] = "$field = :$field";
        $params[] = array(":$field",$_GET[$field],PDO::PARAM_STR);
    }
}

$where_term = '';
if(!empty($and_terms))
{
    $where_term = "WHERE " . implode(' AND ', $and_terms);
}

// get the total matching rows
$query = "SELECT COUNT(*) FROM table $where_term";

// note: the following logic should be in a general purpose prepared query method that you extend the PDO class with
if(empty($params))
{
    // no bound inputs, just execute the query
    $stmt = $db->query($query);
}
else
{
    // there are bound inputs, produce a prepared query, bind the inputs, and execute the query
    $stmt = $db->prepare($query);
    foreach($params as $param)
    {
        $stmt->bindValue($param[0],$param[1],$param[2]);
    }
    $stmt->execute();
}

$total = $stmt->fetchColumn();

// calculate total number of pages
$pages = ceil($total / $per_page);

// limit the page number 
$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default'   => 1,
'min_range' => 1,
),
)));

// calculate starting row for LIMIT
$offset = ($page - 1)  * $per_page; 

// add limit values to the array of bound parameters
$params[] = array(':per_page',$per_page, PDO::PARAM_INT);
$params[] = array(':offset',$offset, PDO::PARAM_INT);

// query for the data
$query = "SELECT * FROM table $where_term ORDER BY id DESC LIMIT :per_page OFFSET :offset";

// note: the following logic should be in a general purpose prepared query method that you extend the PDO class with
if(empty($params))
{
    // no bound inputs, just execute the query
    $stmt = $db->query($query);
}
else
{
    // there are bound inputs, produce a prepared query, bind the inputs, and execute the query
    $stmt = $db->prepare($query);
    foreach($params as $param)
    {
        $stmt->bindValue($param[0],$param[1],$param[2]);
    }
    $stmt->execute();
}

$result = $stmt->fetchAll();

if(!count($result))
{
    // query didn't return any row(s) - this doesn't mean there isn't any matching data, just that the query for the requested LIMIT range didn't return anything (there's a race condition, where if data gets deleted between the COUNT() query and the data retrieval query, queries for data near the end can return nothing)
    echo '<p>Nothing found.</p>';
}
else
{
    // query matched one or more row(s), display the data
    foreach ($result as $row) {
        echo $row['id'];
    }
}


// if there are any pages, display the pagination
if($pages)
{
    echo '<div id="pagination">
    <div id="pagiCount">';
    
    $q = $_GET; // get a copy of any existing $_GET parameters - do this once before the start of your pagination links

    $prevlink = '';
    if($page > 1) // not on the first page
    {
        $q['page'] = 1;
        $qs = http_build_query($q,'','&');
        $prevlink = "<a href='?$qs' title='First page'>First</a> ";

        $q['page'] = $page - 1;
        $qs = http_build_query($q,'','&');
        $prevlink .= "<a href='?$qs' title='Previous page'><<</a>";
    }
    
    $nextlink = '';
    if($page < $pages) // not on the last page
    {
        $q['page'] = $page + 1;
        $qs = http_build_query($q,'','&');
        $nextlink = "<a href='?$qs' title='Next page'>>></a> ";

        $q['page'] = $pages;
        $qs = http_build_query($q,'','&');      
        $nextlink .= "<a href='?$qs' title='Last page'>Last</a></span>";
    }
        echo "<div id='paging'><p><small>$prevlink Page $page of $pages $nextlink </small></p></div>";
    echo '</div></div>';
}

lovephp · July 11, 2016

the OP's code is actually setting a default value of 1, if there is no page get parameter, validating the value as an integer (within php's integer range), and is limiting the value between 1 and the total number of pages.

don't copy or use code unless you understand it. this bit of code is repetitive and unnecessarily bespoke/hard-coded, and isn't using the correct entity version of an & for links on a page.

your code still has a number of unnecessary and problematic things in it, which i am betting you have been informed of most of them in previous threads -

1) use the correct input variables where you expect the data to be. use $_GET, not $_REQUEST.

2) i don't know what your clean() function code is, but i'm betting it isn't effective and is best left out.

3) the try/catch block will catch errors with the prepare/execute methods. this means that there was a sql syntax error or wrong bound parameters. this does not mean that the query ran, but didn't match anything. there's no need for a local try/catch block unless you are specifically handling a type of query error in your code, such as a duplicate key error from and INSERT/UPDATE query.

4) the global keyword only has meaning when used inside a function definition and even there it indicates bad coding. don't use global $var_name;

5) the code to build the WHERE term for the sql query statement should not be repeated. build the term in a variable, then use that variable each place it is needed.

6) the external data you are putting into the WHERE term needs to be handled using a prepared query with bound input parameters.

7) the $start and $end variables are not used in the posted code and should be removed.

the sql query statements should be built in a php variable, this supports debugging (you can echo/log the statement, though the pdo statement object has a queryString property you can use) and it leads to writing general purpose code.

9) you need to set the default fetch mode in your connection code, so that you don't need to specify it every time you run a query.

10) the pdostatement object is already traversable. you don't need and shouldn't be applying the new IteratorIterator() to it.

11) for queries that will return a set of data, just fetch all the data using the fetchAll() method. you can then use count() on the data to find how many rows the query matched (the ->rowCount() method doesn't work with SELECT queries for all database types, so, using fetchAll()/count() will work regardless of which type of database you are using.)

12) to produce the pagination output, you need to test how many pages were calculated, not if the data retrieval query returned any rows (there's a condition that's mentioned in the code where you would need to produce the pagination links even if the data retrieval query didn't match any data)

13) your <span> tags are reusing the same id='...' value, which is invalid. if you are doing something unique with each span (which is doubtful), you would need to use unique id values. if you are not doing anything with the span, don't include it in the markup.

the following example code shows how you might do this. this code adds the following features -

1) shows how to do a data driven design, where you define somewhere (an array or database table) a set of data that general purpose code operates on. this eliminates repeating block after block of same functioning code. this also has the benefit of helping to implement DRY (Don't Repeat Yourself) programming, since it eliminates the repetitive logic.

2) it implements the suggestion of leaving a particulate field out of the sql query statement when it is the 'ALL' choice or when the choice isn't present at all in the $_GET parameters.

3) shows how you would apply http_build_query() when building each link.
// define the possible search fields - this is used to produce a data driven/dynamic design, where you don't write out block after block of code that only differs in the value it operates on
$search_fields = array('title','name','description'); 

$and_terms = array(); // WHERE terms to be AND'ed
$params = array(); // bound input parameters for a prepared query

foreach($search_fields as $field)
{
    if(isset($_GET[$field]) && $_GET[$field] != 'ALL') // only if the field is set and it's not 'ALL'
    {
        // add the search field to the WHERE terms
        $and_terms[] = "$field = :$field";
        $params[] = array(":$field",$_GET[$field],PDO::PARAM_STR);
    }
}

$where_term = '';
if(!empty($and_terms))
{
    $where_term = "WHERE " . implode(' AND ', $and_terms);
}

// get the total matching rows
$query = "SELECT COUNT(*) FROM table $where_term";

// note: the following logic should be in a general purpose prepared query method that you extend the PDO class with
if(empty($params))
{
    // no bound inputs, just execute the query
    $stmt = $db->query($query);
}
else
{
    // there are bound inputs, produce a prepared query, bind the inputs, and execute the query
    $stmt = $db->prepare($query);
    foreach($params as $param)
    {
        $stmt->bindValue($param[0],$param[1],$param[2]);
    }
    $stmt->execute();
}

$total = $stmt->fetchColumn();

// calculate total number of pages
$pages = ceil($total / $per_page);

// limit the page number 
$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default'   => 1,
'min_range' => 1,
),
)));

// calculate starting row for LIMIT
$offset = ($page - 1)  * $per_page; 

// add limit values to the array of bound parameters
$params[] = array(':per_page',$per_page, PDO::PARAM_INT);
$params[] = array(':offset',$offset, PDO::PARAM_INT);

// query for the data
$query = "SELECT * FROM table $where_term ORDER BY id DESC LIMIT :per_page OFFSET :offset";

// note: the following logic should be in a general purpose prepared query method that you extend the PDO class with
if(empty($params))
{
    // no bound inputs, just execute the query
    $stmt = $db->query($query);
}
else
{
    // there are bound inputs, produce a prepared query, bind the inputs, and execute the query
    $stmt = $db->prepare($query);
    foreach($params as $param)
    {
        $stmt->bindValue($param[0],$param[1],$param[2]);
    }
    $stmt->execute();
}

$result = $stmt->fetchAll();

if(!count($result))
{
    // query didn't return any row(s) - this doesn't mean there isn't any matching data, just that the query for the requested LIMIT range didn't return anything (there's a race condition, where if data gets deleted between the COUNT() query and the data retrieval query, queries for data near the end can return nothing)
    echo '<p>Nothing found.</p>';
}
else
{
    // query matched one or more row(s), display the data
    foreach ($result as $row) {
        echo $row['id'];
    }
}


// if there are any pages, display the pagination
if($pages)
{
    echo '<div id="pagination">
    <div id="pagiCount">';
    
    $q = $_GET; // get a copy of any existing $_GET parameters - do this once before the start of your pagination links

    $prevlink = '';
    if($page > 1) // not on the first page
    {
        $q['page'] = 1;
        $qs = http_build_query($q,'','&');
        $prevlink = "<a href='?$qs' title='First page'>First</a> ";

        $q['page'] = $page - 1;
        $qs = http_build_query($q,'','&');
        $prevlink .= "<a href='?$qs' title='Previous page'><<</a>";
    }
    
    $nextlink = '';
    if($page < $pages) // not on the last page
    {
        $q['page'] = $page + 1;
        $qs = http_build_query($q,'','&');
        $nextlink = "<a href='?$qs' title='Next page'>>></a> ";

        $q['page'] = $pages;
        $qs = http_build_query($q,'','&');      
        $nextlink .= "<a href='?$qs' title='Last page'>Last</a></span>";
    }
        echo "<div id='paging'><p><small>$prevlink Page $page of $pages $nextlink </small></p></div>";
    echo '</div></div>';
}

this is exactly what i was looking for bro really appreciate it. ok so i did this

<?php
//database credentials
error_reporting(E_ALL);
$servername = "localhost";
$username = "root";
$password = "";
$per_page = 1;
try {
    $conn = new PDO("mysql:host=$servername;dbname=pagitest", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //echo "Connected successfully";
    }
catch(PDOException $e)
    {
    //echo "Connection failed: " . $e->getMessage();
    }
?>

<form method="get">

                <select name="cat">
                <option value="" selected="selected" disabled="disabled">Select Cat</option>
                    <?php
                    $stmt = $conn->prepare('SELECT DISTINCT cat FROM test');
                    $stmt->execute();
                    $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    echo '<option value="All">All</option>';
                    foreach($row as $rows){
                    echo '<option value="'.$rows['cat'].'">'.$rows['cat'].'</option>';
                    }
                    ?>
                </select>
                <br/>
                <select name="area">
                <option value="" selected="selected" disabled="disabled">Select Area</option>
                    <?php
                    $stmt = $conn->prepare('SELECT DISTINCT area FROM test');
                    $stmt->execute();
                    $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    echo '<option value="All">All</option>';
                    foreach($row as $rows){
                    echo '<option value="'.$rows['area'].'">'.$rows['area'].'</option>';
                    }
                    ?>
                </select>
                <br/>
                <select name="type">
                <option value="" selected="selected" disabled="disabled">Select Type</option>
                    <?php
                    $stmt = $conn->prepare('SELECT DISTINCT type FROM test');
                    $stmt->execute();
                    $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    echo '<option value="All">All</option>';
                    foreach($row as $rows){
                    echo '<option value="'.$rows['type'].'">'.$rows['type'].'</option>';
                    }
                    ?>
                </select>
<br/>
<input type="submit" name="submit" value="submit"/>
</form>

<br/><br/>

<?php

if(isset($_GET['submit'])){
$cat = $_GET['cat'];
$area = $_GET['area'];
$type = $_GET['type'];

// define the possible search fields - this is used to produce a data driven/dynamic design, where you don't write out block after block of code that only differs in the value it operates on
$search_fields = array('cat','area','type');

$and_terms = array(); // WHERE terms to be AND'ed
$params = array(); // bound input parameters for a prepared query

foreach($search_fields as $field)
{
    if(isset($_GET[$field]) && $_GET[$field] != 'All') // only if the field is set and it's not 'ALL'
    {
        // add the search field to the WHERE terms
        $and_terms[] = "$field = :$field";
        $params[] = array(":$field",$_GET[$field],PDO::PARAM_STR);
    }
}

$where_term = '';
if(!empty($and_terms))
{
    $where_term = "WHERE " . implode(' AND ', $and_terms);
}

// get the total matching rows
$query = "SELECT COUNT(*) FROM test $where_term";

// note: the following logic should be in a general purpose prepared query method that you extend the PDO class with
if(empty($params))
{
    // no bound inputs, just execute the query
    $stmt = $conn->query($query);
}
else
{
    // there are bound inputs, produce a prepared query, bind the inputs, and execute the query
    $stmt = $conn->prepare($query);
    foreach($params as $param)
    {
        $stmt->bindValue($param[0],$param[1],$param[2]);
    }
    $stmt->execute();
}

$total = $stmt->fetchColumn();

// calculate total number of pages
$pages = ceil($total / $per_page);

// limit the page number
$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default'   => 1,
'min_range' => 1,
),
)));

// calculate starting row for LIMIT
$offset = ($page - 1)  * $per_page;

// add limit values to the array of bound parameters
$params[] = array(':per_page',$per_page, PDO::PARAM_INT);
$params[] = array(':offset',$offset, PDO::PARAM_INT);

// query for the data
$query = "SELECT * FROM test $where_term ORDER BY id DESC LIMIT :per_page OFFSET :offset";

// note: the following logic should be in a general purpose prepared query method that you extend the PDO class with
if(empty($params))
{
    // no bound inputs, just execute the query
    $stmt = $conn->query($query);
}
else
{
    // there are bound inputs, produce a prepared query, bind the inputs, and execute the query
    $stmt = $conn->prepare($query);
    foreach($params as $param)
    {
        $stmt->bindValue($param[0],$param[1],$param[2]);
    }
    $stmt->execute();
}

$result = $stmt->fetchAll();

if(!count($result))
{
    // query didn't return any row(s) - this doesn't mean there isn't any matching data, just that the query for the requested LIMIT range didn't return anything (there's a race condition, where if data gets deleted between the COUNT() query and the data retrieval query, queries for data near the end can return nothing)
    echo '<p>Nothing found.</p>';
}
else
{
    // query matched one or more row(s), display the data
    foreach ($result as $row) {
        echo '<br/>'.$row['name'].'<br/>'.$row['number'];
    }
}


// if there are any pages, display the pagination
if($pages)
{
    echo '<div id="pagination">
    <div id="pagiCount">';
    
    $q = $_GET; // get a copy of any existing $_GET parameters - do this once before the start of your pagination links

    $prevlink = '';
    if($page > 1) // not on the first page
    {
        $q['page'] = 1;
        $qs = http_build_query($q,'','&');
        $prevlink = "<a href='?$qs' title='First page'>First</a> ";

        $q['page'] = $page - 1;
        $qs = http_build_query($q,'','&');
        $prevlink .= "<a href='?$qs' title='Previous page'><<</a>";
    }
    
    $nextlink = '';
    if($page < $pages) // not on the last page
    {
        $q['page'] = $page + 1;
        $qs = http_build_query($q,'','&');
        $nextlink = "<a href='?$qs' title='Next page'>>></a> ";

        $q['page'] = $pages;
        $qs = http_build_query($q,'','&');      
        $nextlink .= "<a href='?$qs' title='Last page'>Last</a></span>";
    }
        echo "<div id='paging'><p><small>$prevlink Page $page of $pages $nextlink </small></p></div>";
    echo '</div></div>';
}
}
?>

all looks ok but from address bar the url looks like

http://localhost/jobs/pagi.php?cat=reptile&area=All&type=pro&submit=submit

how do i remove the &submit=submit and also the part

if(!count($result))
{
    // query didn't return any row(s) - this doesn't mean there isn't any matching data, just that the query for the requested LIMIT range didn't return anything (there's a race condition, where if data gets deleted between the COUNT() query and the data retrieval query, queries for data near the end can return nothing)
    echo '<p>Nothing found.</p>';
}
else
{

wont show the message nothing found when there is no value and i get following error which is

( ! ) Fatal error: in C:\wamp\www\jobs\pagi.php on line 152 ( ! ) PDOException: in C:\wamp\www\jobs\pagi.php on line 152

$stmt = $conn->prepare($query);
    foreach($params as $param)
    {
        $stmt->bindValue($param[0],$param[1],$param[2]);
    }
    $stmt->execute();
}

this is there in line 152 $stmt->execute();

Edited July 11, 2016 by lovephp

Muddy_Funster · July 11, 2016

There should be more to that PDOException error that tells you exactly what the problem is - such as parameter type mismatch, different number of parameters to placeholders etc.

Some other things though:

Purely dynamic WHERE clauses are a big potential security flaw, not to mention an absolute ball ache to manage, and you should try really hard to avoid using them at all.

If you want to be able to use COUNT(*) in a meaningful way you should alias it eg. "SELECT COUNT(*) as totalNumber FROM ..."

There is no need to prepare() a flat SQL statement such as "SELECT something FROM somewhere" as there are no parameters being passed to it - you should user PDO::query - for SELECTs - or PDO::exec for other query types in this situation.

Also, rather than looping through the $param array it would be more efficient if you just passed the array into the $stmt->execute($param) like that. To do this though you will probably have to change your $and_terms[] to "{$field} = ? " and then your $param[] = array($_GET[$field]) As PARAM_STR is the default for PDO parameter types and you are not applying that conditionally there is no need to have it in there.

mac_gyver · July 11, 2016

there's two reasons you are getting an error at the ->execute() method call -

1) emulated prepared queries are on by default (and not well documented, thanks php). meaning, you are not running real prepared queries. the ->execute() method is forming the complete sql query statement, with the bound data inserted into it, then calling the ->query() method internally to execute the query.

when you make the database connection, you need to set emulated prepared queries to false. add this line after the connection -

$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries

and you might as well do something else i stated and add this line -

$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode

2) the error you are getting should contain information about an sql syntax error (type, the name of one of your columns, is a reserved keyword), though the error information at the execute() method is sometimes lacking, more than normal..once you set emulated prepared queries to false, you will be getting an explicate sql syntax error at the ->prepared() method call.

you should avoid using reserved keywords as database, table, and column names, but if you must, you can add back-ticks around the $field name in the following line of code (in each place the query is being prepared, or in one place, if you make the general purpose prepared query method as suggested) -

$and_terms[] = "`$field` = :$field";

how do i remove the &submit=submit

by removing the name='submit' from the submit button in the form. for a get method form, there's no need for a name for the submit button. the form field data will either exist or it won't and your code just needs to check for the field data, not a submit button. you also need to remove the if(isset($_GET['submit'])){ and the matching } from the code. and remove the three lines of code that are right after the if(isset($_GET['submit'])){ statement.

lovephp · July 11, 2016

there's two reasons you are getting an error at the ->execute() method call -

1) emulated prepared queries are on by default (and not well documented, thanks php). meaning, you are not running real prepared queries. the ->execute() method is forming the complete sql query statement, with the bound data inserted into it, then calling the ->query() method internally to execute the query.

when you make the database connection, you need to set emulated prepared queries to false. add this line after the connection -
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
and you might as well do something else i stated and add this line -
$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode
2) the error you are getting should contain information about an sql syntax error (type, the name of one of your columns, is a reserved keyword), though the error information at the execute() method is sometimes lacking, more than normal..once you set emulated prepared queries to false, you will be getting an explicate sql syntax error at the ->prepared() method call.

you should avoid using reserved keywords as database, table, and column names, but if you must, you can add back-ticks around the $field name in the following line of code (in each place the query is being prepared, or in one place, if you make the general purpose prepared query method as suggested) -
$and_terms[] = "`$field` = :$field";
by removing the name='submit' from the submit button in the form. for a get method form, there's no need for a name for the submit button. the form field data will either exist or it won't and your code just needs to check for the field data, not a submit button. you also need to remove the if(isset($_GET['submit'])){ and the matching } from the code. and remove the three lines of code that are right after the if(isset($_GET['submit'])){ statement.

thanks a ton bro it does what i was looking for. really appreciate your time and kindness, all of you have always been great full to me with my issues.

lovephp · July 13, 2016

there's two reasons you are getting an error at the ->execute() method call -

1) emulated prepared queries are on by default (and not well documented, thanks php). meaning, you are not running real prepared queries. the ->execute() method is forming the complete sql query statement, with the bound data inserted into it, then calling the ->query() method internally to execute the query.

when you make the database connection, you need to set emulated prepared queries to false. add this line after the connection -
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
and you might as well do something else i stated and add this line -
$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode
2) the error you are getting should contain information about an sql syntax error (type, the name of one of your columns, is a reserved keyword), though the error information at the execute() method is sometimes lacking, more than normal..once you set emulated prepared queries to false, you will be getting an explicate sql syntax error at the ->prepared() method call.

you should avoid using reserved keywords as database, table, and column names, but if you must, you can add back-ticks around the $field name in the following line of code (in each place the query is being prepared, or in one place, if you make the general purpose prepared query method as suggested) -
$and_terms[] = "`$field` = :$field";
by removing the name='submit' from the submit button in the form. for a get method form, there's no need for a name for the submit button. the form field data will either exist or it won't and your code just needs to check for the field data, not a submit button. you also need to remove the if(isset($_GET['submit'])){ and the matching } from the code. and remove the three lines of code that are right after the if(isset($_GET['submit'])){ statement.

bro how do i show total results displaying in total even if its paginated? i did this

if(!count($result))
{
    echo '<p>Nothing found.</p>';
}else{
   echo $result = $stmt->rowCount();  // this shows just records in the page now all in total
    foreach ($result as $row) {   
        echo $row['id'];
    }
}

lovephp · July 13, 2016

sorry it was already there i missed it now found $total = $stmt->fetchColumn(); thanks

lovephp · July 20, 2016

there's two reasons you are getting an error at the ->execute() method call -

1) emulated prepared queries are on by default (and not well documented, thanks php). meaning, you are not running real prepared queries. the ->execute() method is forming the complete sql query statement, with the bound data inserted into it, then calling the ->query() method internally to execute the query.

when you make the database connection, you need to set emulated prepared queries to false. add this line after the connection -
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
and you might as well do something else i stated and add this line -
$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode
2) the error you are getting should contain information about an sql syntax error (type, the name of one of your columns, is a reserved keyword), though the error information at the execute() method is sometimes lacking, more than normal..once you set emulated prepared queries to false, you will be getting an explicate sql syntax error at the ->prepared() method call.

you should avoid using reserved keywords as database, table, and column names, but if you must, you can add back-ticks around the $field name in the following line of code (in each place the query is being prepared, or in one place, if you make the general purpose prepared query method as suggested) -
$and_terms[] = "`$field` = :$field";
by removing the name='submit' from the submit button in the form. for a get method form, there's no need for a name for the submit button. the form field data will either exist or it won't and your code just needs to check for the field data, not a submit button. you also need to remove the if(isset($_GET['submit'])){ and the matching } from the code. and remove the three lines of code that are right after the if(isset($_GET['submit'])){ statement.

again got a question

is this line

$search_fields = array('title','name','description');

and any ways related to this

$stmt = $db->prepare($query);
    foreach($params as $param)
    {
        $stmt->bindValue($param[0],$param[1],$param[2]); //this line
    }
    $stmt->execute();
}

this is if i try to add more fields like say mobile,email in $search_fields = array('title','name','description',mobile,email);so i also need to increment here

$stmt->bindValue($param[0],$param[1],$param[2],$param[3],$param[4]); like but doing so the page turns blank

Muddy_Funster · July 20, 2016

No, you can't bind column names as parameters. This is going back to what I said about maintaining dynamic where clauses.

lovephp · July 20, 2016

So to be able to search more rows i just add only to this line $search_fields = array('title','name','description'); ?

mac_gyver · July 20, 2016

again got a question

is this line

$search_fields = array('title','name','description');

and any ways related to this

$stmt = $db->prepare($query);
    foreach($params as $param)
    {
        $stmt->bindValue($param[0],$param[1],$param[2]); //this line
    }
    $stmt->execute();
}

no, the number of entries in the array definition is not related to the bindValue() list of parameters. you must know, by reading the php.net documentation, what parameters the bindValue() method takes, in order to understand what the code is doing and why there are three parameters.

So to be able to search more rows i just add only to this line $search_fields = array('title','name','description'); ?

to search more columns, yes, you only add them to the $search_fields array.

lovephp · July 25, 2016

no, the number of entries in the array definition is not related to the bindValue() list of parameters. you must know, by reading the php.net documentation, what parameters the bindValue() method takes, in order to understand what the code is doing and why there are three parameters.

to search more columns, yes, you only add them to the $search_fields array.

ok cool, and then i tried this without search fields just simple pagination but i get the following error

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2031 ' in C:\wamp\www\script\pagi.php on line 28

and the code i tried was like thid

$uid = $_SESSION['memberID'];    
    
$query = "SELECT COUNT(*) FROM table WHERE memberID = '".$uid."'";
$stmt = $db->query($query);
$stmt->bindParam(':memberID', $uid,PDO::FETCH_ASSOC);
$stmt->execute();

global $per_page;

$total = $stmt->fetchColumn();

$pages = ceil($total / $per_page);

$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default'   => 1,
'min_range' => 1,
),
)));

$offset = ($page - 1)  * $per_page;

$query = "SELECT * FROM table WHERE memberID = '".$uid."' ORDER BY id DESC LIMIT :per_page OFFSET :offset";
$stmt = $db->query($query);
$stmt->bindParam(':per_page', $per_page, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

$result = $stmt->fetchAll();

what am i doing wrong here?

Jacques1 · July 25, 2016

And line 28 is what?

Your prepared statements are definitely shaky. Why do you keep inserting $uid straight into the query strings, risking an SQL injection vulnerability? You know how to safely bind parameters.

And then there's that at the top of the script:

$query = "SELECT COUNT(*) FROM table WHERE memberID = '".$uid."'";
$stmt = $db->query($query);
$stmt->bindParam(':memberID', $uid,PDO::FETCH_ASSOC);
$stmt->execute();

You execute the query. Then you suddenly decide to bind a parameter which doesn't exist and execute the statement again?

mac_gyver · July 25, 2016

i don't really think i can help you 'get' what it is you need to do to be able to write code that does something useful. you are randomly changing code, because you haven't taken the time to learn what the statements in the code actually mean and do, then dumping the code on a forum for someone to tell you what to do to fix it. you won't learn anything by getting some to fix your code for you.

some problems in the posted code - you are no longer preparing the query, yet you are trying to bind parameters to it. you are using a wrong PDO defined constant in the bindParm() statement, you are using global $per_page; that does absolutely nothing in the context of this code.

the only thing you are currently doing different, from at the start of this thread, is changing the sql query statement, specifically just the WHERE ... term. the only code you need to change are the lines that are building the WHERE term. the rest of the code needs to remain the same. and even you were doing this for a completely different sql query statement, the only things you would change in the code are the two queries, the SELECT COUNT(*) ... query , that gets the total number of matching rows and the SELECT list of columns ... query, that retrieves the matching data.

this is the code, from reply #10, that is building the WHERE term -

// define the possible search fields - this is used to produce a data driven/dynamic design, where you don't write out block after block of code that only differs in the value it operates on
$search_fields = array('title','name','description'); 

$and_terms = array(); // WHERE terms to be AND'ed
$params = array(); // bound input parameters for a prepared query

foreach($search_fields as $field)
{
    if(isset($_GET[$field]) && $_GET[$field] != 'ALL') // only if the field is set and it's not 'ALL'
    {
        // add the search field to the WHERE terms
        $and_terms[] = "$field = :$field";
        $params[] = array(":$field",$_GET[$field],PDO::PARAM_STR);
    }
}

$where_term = '';
if(!empty($and_terms))
{
    $where_term = "WHERE " . implode(' AND ', $and_terms);
}

all you have to do is change this section of code so that it produces WHERE memberID = :memberID as the where term and then add the correct entry to the $params array for the $_SESSION['memberID'] value. in its simplest, bespoke form, this is what you would have left -

$params = array(); // bound input parameters for a prepared query

$where_term = "WHERE memberID = :memberID";
$params[] = array(":memberID",$_SESSION['memberID'],PDO::PARAM_INT);

the rest of the code, starting with - $query = "SELECT COUNT(*) FROM table $where_term"; doesn't change, unless you want it to do something differently, such as output the data differently or you want the pagination links to be different.

lovephp · July 25, 2016

And line 28 is what?

Your prepared statements are definitely shaky. Why do you keep inserting $uid straight into the query strings, risking an SQL injection vulnerability? You know how to safely bind parameters.

And then there's that at the top of the script:
$query = "SELECT COUNT(*) FROM table WHERE memberID = '".$uid."'";
$stmt = $db->query($query);
$stmt->bindParam(':memberID', $uid,PDO::FETCH_ASSOC);
$stmt->execute();
You execute the query. Then you suddenly decide to bind a parameter which doesn't exist and execute the statement again?

you are right i made a mistake there now fixed thank you

lovephp · July 25, 2016

i don't really think i can help you 'get' what it is you need to do to be able to write code that does something useful. you are randomly changing code, because you haven't taken the time to learn what the statements in the code actually mean and do, then dumping the code on a forum for someone to tell you what to do to fix it. you won't learn anything by getting some to fix your code for you.

some problems in the posted code - you are no longer preparing the query, yet you are trying to bind parameters to it. you are using a wrong PDO defined constant in the bindParm() statement, you are using global $per_page; that does absolutely nothing in the context of this code.

the only thing you are currently doing different, from at the start of this thread, is changing the sql query statement, specifically just the WHERE ... term. the only code you need to change are the lines that are building the WHERE term. the rest of the code needs to remain the same. and even you were doing this for a completely different sql query statement, the only things you would change in the code are the two queries, the SELECT COUNT(*) ... query , that gets the total number of matching rows and the SELECT list of columns ... query, that retrieves the matching data.

this is the code, from reply #10, that is building the WHERE term -
// define the possible search fields - this is used to produce a data driven/dynamic design, where you don't write out block after block of code that only differs in the value it operates on
$search_fields = array('title','name','description'); 

$and_terms = array(); // WHERE terms to be AND'ed
$params = array(); // bound input parameters for a prepared query

foreach($search_fields as $field)
{
    if(isset($_GET[$field]) && $_GET[$field] != 'ALL') // only if the field is set and it's not 'ALL'
    {
        // add the search field to the WHERE terms
        $and_terms[] = "$field = :$field";
        $params[] = array(":$field",$_GET[$field],PDO::PARAM_STR);
    }
}

$where_term = '';
if(!empty($and_terms))
{
    $where_term = "WHERE " . implode(' AND ', $and_terms);
}
all you have to do is change this section of code so that it produces WHERE memberID = :memberID as the where term and then add the correct entry to the $params array for the $_SESSION['memberID'] value. in its simplest, bespoke form, this is what you would have left -
$params = array(); // bound input parameters for a prepared query

$where_term = "WHERE memberID = :memberID";
$params[] = array(":memberID",$_SESSION['memberID'],PDO::PARAM_INT);
the rest of the code, starting with - $query = "SELECT COUNT(*) FROM table $where_term"; doesn't change, unless you want it to do something differently, such as output the data differently or you want the pagination links to be different.

well what i was trying was to do the pagination without the parameters because i just wanted to search a table for a particular user. please take a look if this looks ok to you?

it now works as i wanted it to but to be sure if you guys thing i did it right way or not

$per_page = 1;
    
$query = "SELECT COUNT(*) FROM table WHERE memberID = :memberID";
        $stmt = $db->prepare($query);
        $stmt->bindParam(':memberID', $uid, PDO::PARAM_INT);
        $stmt->execute();
        $total = $stmt->fetchColumn();    

$pages = ceil($total / $per_page);

$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default'   => 1,
'min_range' => 1,
),
)));

$offset = ($page - 1)  * $per_page;

$query = "SELECT * FROM table WHERE memberID = :memberID ORDER BY id DESC LIMIT :per_page OFFSET :offset";
        
         $stmt = $db->prepare($query);
         $stmt->bindParam(':memberID', $uid, PDO::PARAM_INT);
         $stmt->bindParam(':per_page', $per_page, PDO::PARAM_INT);
         $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
         $stmt->execute();
    
$result = $stmt->fetchAll();

also one question in url pages are like file.php?page=2 for example and if i try to add a " even if the page is last why doing php?page=2" another page shows though if i click it goes back to page=2 again.

Edited July 25, 2016 by lovephp

Sign In

how to make this pagination work with search results?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information