Paginate search results - help needed please!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Quink
Forum Newbie
Posts: 2
Joined: Wed Apr 25, 2012 2:21 pm

Paginate search results - help needed please!

Post by Quink »

Hello.
Very new to coding and I thought I was trying to do something pretty basic/common (?!) but I seem to have come up against a complete wall now and after hours searching for an answer I'm completely stuck!
I'm trying to write some code to search a MySQL database of products, then display the results. For some search results there will be lots of products so I want to display 10 products on the first page then allow visitors to go to the next page to see another 10, and so on - a type of pagination, as they should then be able to click back to see the last page etc.
I've got to the point of being able to display the first 10 search results, but I can't figure out at all how to create some kind of page scrolling/pagination system.
Please, does anybody have any ideas?? Bearing in mind I am complete beginner (aka stupid) when it comes to all this, so simpler the better!!

Here's the code...
Thanks for any help you can offer.

Code: Select all


<?php
//opens connection to mysql server
$dbc = mysql_connect('localhost');
if (!$dbc) {
	die('Not connected :' . mysql_error());
	}
echo "Connected to mysql database<br />";
//select database
$db_selected = mysql_select_db("NAME_OF_DATABASE", $dbc);
if (!$db_selected)
{
	die ("Cannot connect :" . mysql_error());
}
echo "Connected to database<br /><hr />";
echo "Here are your results";

$term = $_POST['term'];
$category = $_POST['category'];
$brand = $_POST['brand_name'];
 
  
$sql = mysql_query("SELECT * FROM products where product_name like '%$term%' AND category_name like '%$category%' AND brand_name like '%$brand%' LIMIT 0, 10");
 
{ 
while ($row = mysql_fetch_array($sql)){
	echo "<table border='1' width='100%'> ";
	echo "<tr>"; 
	echo "<td style='vertical-align:top' width='25%'>" . '<img src="', $row['image_url'], '" alt="', $row['product_name'], '"  width="100" height="100" />' . "</td>";
	echo "<td style='vertical-align:top' width='50%'>" . $row['product_name'];
	echo "<br />";
	echo  "<span style='font-size: 10px'>" . $row['description'] . "</span>" . "</td>"; 
	echo "<td style='vertical-align:top' width='25%'>" . $row['price'];
	echo "<br />";
	echo "<br />";
	echo "<hr />";
	echo "$row['merchant_name'] </td>";
	echo "</tr>";
    }
echo "</table>"; 
}	
?>

User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Paginate search results - help needed please!

Post by Celauran »

You don't want to use LIMIT 0, 10 as that will always give you the first 10 results. You need to replace that zero with an offset variable which can be set via GET request.

Code: Select all

<?php

$offset = (1 - (int) $_GET['page']) * 10;
...
$query = "SELECT foo, bar
          FROM table_name
          WHERE whatever
          LIMIT {$offset}, 10";
Then you simply create your links like somescript.php?page=2
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: Paginate search results - help needed please!

Post by x_mutatis_mutandis_x »

Celauran wrote:Then you simply create your links like somescript.php?page=2
Make sure you save the filter parameters ($term, $category, and $brand) in session, or cookies.
Quink
Forum Newbie
Posts: 2
Joined: Wed Apr 25, 2012 2:21 pm

Re: Paginate search results - help needed please!

Post by Quink »

Thank you very much for your advice! Really appreciate it. But perhaps I didn't quite explain how rubbish I am at all this!!

Celauran - could you just spare a few more minutes to explain that piece of code for me please??!! What does 'page' do? What does 'foo bar' mean? And what do I type instead of 'whatever'?
I think I've got to tackle that before I even look at "simply creating your links like somescript.php?page=2" and saving my parameters as x_mutatis says!!!

Really sorry for being so bad at all this!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Paginate search results - help needed please!

Post by Celauran »

Quink wrote:Celauran - could you just spare a few more minutes to explain that piece of code for me please??!! What does 'page' do? What does 'foo bar' mean? And what do I type instead of 'whatever'?
Your current query looks like this:

Code: Select all

$sql = mysql_query("SELECT * FROM products where product_name like '%$term%' AND category_name like '%$category%' AND brand_name like '%$brand%' LIMIT 0, 10");
First, SELECT * is always wrong; specify which columns you need. I just used foo and bar as obvious placeholders. Similarly, I used 'whatever' in place of your actual WHERE clause. Page simply specifies which page you're currently viewing.

Your actual query would look like this:

Code: Select all

$query = "SELECT product_name, description, price, image_url, merchant_name
          FROM products
          WHERE product_name LIKE '%{$term}%'
            AND category_name LIKE '%{$category}%'
            AND brand_name LIKE '%{$brand}%'
          LIMIT {$offset}, 10";
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: Paginate search results - help needed please!

Post by x_mutatis_mutandis_x »

Quink wrote:What does 'foo bar' mean?
Foo, bar are used usually by programmers as names for a property, variable, class, resource, (columns of a table in this case) etc to show an example in code.
Quink wrote:What does 'page' do?
"page" is the request parameter that indicates which page is it currently showing. Link to page 1 of your pagination = <http://www.foo-bar.com/search?page=1>, Link to page 2 of your pagination = <http://www.foo-bar.com/search?page=2> so on and so forth..
Post Reply