Display search results on multiple pages

[Quink]

Hello everyone,

 

Very new to coding - enjoying it but struggling! I think I'm trying to do something pretty common but I seem to have come up against a complete wall now and after hours/days searching the internet and reading books 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??

I've attached my code, I hope this is the correct way of doing things here.

Many thanks for your time!

 

The PHP search code...

<?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>"; 
}	
?>

 

[litebearer]

I use an exceptionally easy to implement pagination script (NOT MINE) for photos; however, it is easily adapted to other content - http://www.nstoia.com/sat/disp_pag/

[MMDE]

You got a limit at the end of your mysql query, instead of 0, 10 you should probably write 0, 9. Next page would be 10, 19. After that 20, 29.

 

first number: (page-1)*10

second number: (page*10)-1

 

you should get this using $_GET[].

remember to sanitize input to your database.

[Jessica]

You got a limit at the end of your mysql query, instead of 0, 10 you should probably write 0, 9. Next page would be 10, 19. After that 20, 29.

 

first number: (page-1)*10

second number: (page*10)-1

0,10 would retrieve the first 10. it's start/offset and number of records. 10,19 would be 19 records starting with # 10.

 

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

 

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

 

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

 

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

 

SELECT * FROM tbl LIMIT 95,18446744073709551615;

 

With one argument, the value specifies the number of rows to return from the beginning of the result set:

 

SELECT * FROM tbl LIMIT 5;    # Retrieve first 5 rows

 

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

http://dev.mysql.com/doc/refman/5.1/en/select.html