How to split search results into pages?

Question

How to split the search results into pages? (like page 1, page 2, page 3...)

When the user searches for products on my e-commerce website, I want results to be split into several pages showing around 20 products per page. The search results are the outcome of database query.

For example: If the user searches for Samsung mobiles so my query will be:

SELECT * FROM PRODUCTS WHERE BRAND='SAMSUNG';

Suppose the above query returns 55 results, how to show them into pages (1,2 and 3)?

I am using PHP, MySQL, Apache on Windows machine.

Solution 1

The appropriate SQL would be adding:

LIMIT start, amount

You can navigate like

search.php?start=20

and then code like:

LIMIT $start, $amount

with

$start = intval($_GET['start']);

and

$amount = 20;

That will result in max 20 records a page.

Solution 2

Use SQL's LIMIT keyword to limit the amount of results from your query; for example:

SELECT * FROM PRODUCTS WHERE BRAND='SAMSUNG' LIMIT 20, 40;

This would select 20 elements, starting at the 40th

Solution 3

Here is the complete code:

<?php
// Requested page
$requested_page = isset($_GET['page']) ? intval($_GET['page']) : 1;

// Get the product count
$r = mysql_query("SELECT COUNT(*) FROM PRODUCTS WHERE BRAND='SAMSUNG'");
$d = mysql_fetch_row($r);
$product_count = $d[0];

$products_per_page = 20;

// 55 products => $page_count = 3
$page_count = ceil($product_count / $products_per_page);

// You can check if $requested_page is > to $page_count OR < 1,
// and redirect to the page one.

$first_product_shown = ($requested_page - 1) * $products_per_page;

// Ok, we write the page links  
echo '<p>';
for($i=1; $i<=$page_count; $i++) {
    if($i == $requested_page) {
        echo $i;
    } else {
        echo '<a href="/products/samsung/'.$i.'">'.$i.'</a> ';
    }
}
echo '</p>';

// Then we retrieve the data for this requested page
$r = mysql_query("SELECT * FROM PRODUCTS WHERE BRAND='SAMSUNG' LIMIT $first_product_shown, $products_per_page");

while($d = mysql_fetch_assoc($r)) {
    var_dump($d);
}
?>

Hope its help.

Comments

[Johan]

That should do it, for more information try to google for 'pagination'

[sumit]

Ok. I will do it. But how can I know how many pages are there? Should I first query and count the number of rows without the LIMIT and divide by 20? Then after showing the number of pages, I can show results on different pages. Please correct if I am wrong.

[RiaD]

SELECT SQL_CALC_FOUND_ROWS ... LIMIT 10,10. Next query: select found_rows()

[pimvdb]

@iSumitG: I must say I'm not a professional coder at all but what I would do is executing a query like SELECT * FROM table and then use ceil(mysql_num_rows() / 20). As for the current page, you can use $page = floor($start / 20) + 1 (because it would start at 0).

[Cheslab]

I know I'm 5 years late but If somebody reached this point, RiaD is right, if you want only to use mysql_num_rows() for the query you shouldn't SELECT * FROM table, use SELECT id FROM table instead and you will get the result much faster especially if your table is big.

[treyBake]

error_reporting(0); // disable the annoying error report xD you should read from a dif. book if that line was in there, the code is also open to SQL injection

[skeith]

Luckily, I only read that book to understand the concept. I haven't learned much about PHP since then, only manage to make small pages to ease my work. Thank you for your kind advice.