how to make this pagination work with search results?

[lovephp]

 

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>';
}

back again to bug

 

all is ok but just one thing i need in the sql queries to be added by default like the followin

 

$query = "SELECT COUNT(*) FROM table $where_term AND show ='Yes'";

 

also with the other query

 

like

 

$query = "SELECT * FROM table $where_term AND show = 'Yes' ORDER BY id DESC LIMIT :per_page OFFSET :offset";

 

all i want in the queries to have the show = 'Yes' in it so that it gets records only with the ones where show = 'Yes'

Edited November 24, 2016 by lovephp

[lovephp]

oh ok done did it thanks

[mac_gyver]

you can either add that term as an entry in the $and_terms array or create and use a 'view' on your database table.