Printing results to view results over multiple pages

Question

I am trying to create a piece of code that will limit the results from my query to for example 2 results per page, and rest of the results can be seen by clicking on a page number, exactly as google have with their search facility so if I want more results I just go to page 2. How that can be done using PhP. No javascript.

<!DOCTYPE html>
<html>
    <head>
        <title>Database</title>
        <link href="style.css" rel="stylesheet" type="text/css"> <!-- This is linking style sheet (css)into this HTML page-->
        <link href='https://fonts.googleapis.com/css?family=PT+Serif:400italic' rel='stylesheet' type='text/css'>
    </head>
    <body>
    <div class="navigation">
        <form action="index.php" method="get">
            <input type="submit" name="mainpage" value="Main Page" class="submitbut" id="but1" />
        </form>
    </div>
    <form action="index.php" method="post">
        <input type="text" name="search" id="searching" />
        <input type="submit" name="data_submit" value="Search" id="scan" />
    </form>

<?php

if( isset( $_GET['mainpage'] ) ) exit( header( "Location: mainpage.php" ) );

if ( isset( $_POST["data_submit"] ) ){
$search_term = ( $_POST['search']);
if($search_term == ""){
    echo "You need to enter a value";
}else{
$conn = new PDO( 'mysql:host=localhost;dbname=u1358595', 'root' );
/*
 replace root with this
 'u1358595'
 '26nov94'
*/
$stmt = $conn->prepare("SELECT * FROM `guest` g
                       INNER JOIN `booking` b ON g.`guest_id`=b.`guest_id`
                       INNER JOIN `hotel` h ON b.`hotel_id`=h.`hotel_id`
                       WHERE g.`last_name` LIKE :search_term OR g.`first_name` LIKE :search_term;");
$stmt->bindValue(':search_term', '%' . $search_term . '%');
$stmt->execute();


$count=($stmt->rowCount());
echo "There are ".$count." results that match your search";

    echo "
    <table>
    <tr>  
    <th>Guests Matched</th>
    </tr>";

while($hotel = $stmt->fetch()) {
    echo "
    <tr>
    <td><a href='details.php?name=".$hotel['last_name']."'>".$hotel['first_name']." ".$hotel['last_name']."</a></td>
    </tr>";
}
echo "</table>";

$conn = NULL;
}
    }
?>
</body>
</html>

See http://stackoverflow.com/questions/3705318/simple-php-pagination — Chris, Nov 26 '15 at 12:39
Note that while you're absolutely right to use prepared statements in production, I prefer to avoid them while in development, so that it's easier to echo the actual query being sent to mysql. — Strawberry, Nov 26 '15 at 12:41

Oliver Tappin · Answer 1 · 2015-11-26T12:55:23.197

0

Within your SQL, use the LIMIT clause:

Directly from the docs: [LIMIT {[offset,] row_count | row_count OFFSET offset}] https://dev.mysql.com/doc/refman/5.5/en/select.html

So in your example, you would do something like this:

$results_per_page = 24;
$current_page = isset($_GET['page']) ? (int) $_GET['page'] : 0;

$stmt = $conn->prepare("SELECT * FROM `guest` g
                       INNER JOIN `booking` b ON g.`guest_id`=b.`guest_id`
                       INNER JOIN `hotel` h ON b.`hotel_id`=h.`hotel_id`
                       WHERE g.`last_name` LIKE :search_term OR g.`first_name` LIKE :search_term
                       LIMIT :offset, :row_count");

$stmt->bindValue(':search_term', '%' . $search_term . '%');
$stmt->bindValue(':offset', ($current_page * $results_per_page));
$stmt->bindValue(':row_count', $results_per_page);
$stmt->execute();

Using the $_GET variable to change pages: http://localhost/?page=2

With regard to your count for total results, you'll have to run another query to select all the results of that search query but only returning the COUNT(*) output:

SELECT COUNT(*) FROM `guest` g
INNER JOIN `booking` b ON g.`guest_id`=b.`guest_id`
INNER JOIN `hotel` h ON b.`hotel_id`=h.`hotel_id`
WHERE g.`last_name` LIKE :search_term OR g.`first_name` LIKE :search_term

edited Nov 26 '15 at 12:55

answered Nov 26 '15 at 12:44

Oliver Tappin

2,511
1
24
43

how would you use $_GET in this case to change the page on the same page if you get what I mean – Przemek Wojtas Nov 26 '15 at 12:47
If I understand your question correctly, you would use the `$_GET['page']` variable to see if the current page is that page, and do not make that link clickable. If that's now the answer to your question please elaborate. – Oliver Tappin Nov 26 '15 at 12:50
I am trying to display for example 1 result per page, so result 1 is on page 1 and then if I click a link 2 it will take me to the same page but with 2nd result, other than that it looks fine to me and works – Przemek Wojtas Nov 26 '15 at 12:53
If you're trying to change the number of results per page, simply change the `$results_per_page` variable to 1 (I have updated the variable name to make it easier to understand). – Oliver Tappin Nov 26 '15 at 12:54

Printing results to view results over multiple pages

1 Answers1