pagination withmySQL results from a search

limao

heres the thing:
I'm making a directory site (sort of) that includes a search option. I'm past doin the code for the search,pulling the data out of the database, modifying the tables data that's displayed from the database and limiting it to display only four items at a time. All i need to do now is add pagination.

Here's ALL the php code:

SEARCH CODE:

<?php
// set your infomation.
$dbhost='localhost:3306';
$dbusername='limao';
$dbuserpass='password';
$dbname = 'CCTVPRODUCTS';

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');
if(isset($_GET['search']))
{
$search = $_GET['search'];
}

$keywords = explode(" ", $search);

$query = "SELECT * FROM colorcameras " .
"WHERE camDescription LIKE '%".$keywords['0']."%'LIMIT 0,4";

$result = mysql_query($query) or die(mysql_error());
?>

RESULTS CODE:

<?php
echo "<tr>";
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.camera.'</font></b>';
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.Description.'</font></b>';
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.Supplier.'</font></b>';
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.Website.'</font></b>';
echo '</td>';
echo '</tr>';
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">'.$row['camName'].'</font></b>';
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">'.$row['camDescription'].'</font></b>';
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">'.$row['camSupplier'].'</font></b>';
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">
<a href="'.$row['website'].'">'.$row['website'].'</a></font></b>';
echo '</td>';
echo '</tr>';
}
?>

Thats ALL the php code, if anyone can help me learn and understand how to do this i would appreciate it. thank you!

p.s. I'm an extreme newbie aka been doin and learning this for less than a month

bogu

Did u try the search section from this forum? There are lots of threads open with pagination ...

limao

yes i have (and still am) but i haven't found something that fits into my specific problem

bogu

limao wrote:
yes i have (and still am) but i haven't found something that fits into my specific problem

U will never find something exactly how u need, but u should try the examples u find here to see how the pagination work and how to implemented in your script ...

And if it doesn't fits into your specific problem, what is your specific problem?

TobyRT

what's the actual problem you've got?

limao

I'm basically trying to add pagination to the results that are returned after the user searches for something on the website

(if you need me to be more specific, please let me know)

TobyRT

most pagination works in the same way. You just have to change the variables to what suits your code. The premise is to create a 'count' of your results, set that as a limit and foreach() result, increment your counter. When you reach that limit, insert a link to a next page which continues the count from where you left off.

There's examples everywhere!

limao

thanks, i'll keep looking

limao

well, i found some code on pagination and tried to implement it into my code and the result is this:
I type in what to search for and i get the results i want from the database. When i click next, it shows me the results I had in the beginning and not the results i typed into the search. Also, my other links won't work. At first i thought it was a problem with my queries but i guess not. So I really don't know where the problem is. I put my code up so you guys can see what i've done

Here's ALL the php code:

NEW SEARCH CODE:

<?php
// set your infomation.
$dbhost='localhost:3306';
$dbusername='limao';
$dbuserpass='password';
$dbname = 'CCTVPRODUCTS';

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');
if(isset($_GET['search']))
{
$search = $_GET['search'];
}

$keywords = explode(" ", $search);

// If current page number, use it
// if not, set one!

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Define the number of results per page
$max_results = 4;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

$query = "SELECT * FROM colorcameras 
WHERE camDescription LIKE '%".$keywords['0']."%'LIMIT $from, $max_results";


$result = mysql_query($query) or die(mysql_error());
?>

NEW RESULTS CODE:

<?php
echo "<tr>";
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.camera.'</font></b>';
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.Description.'</font></b>';
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.Supplier.'</font></b>';
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.Website.'</font></b>';
echo '</td>';
echo '</tr>';
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">'.$row['camName'].'</font></b>';
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">'.$row['camDescription'].'</font></b>';
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">'.$row['camSupplier'].'</font></b>';
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">
<a href="'.$row['website'].'">'.$row['website'].'</a></font></b>';
echo '</td>';
echo '</tr>';
}
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM colorcameras"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";
}

?>

Thats ALL the php code, if anyone can help me learn and understand how to proceed with this i would appreciate it. thank you!

p.s. I'm an extreme newbie aka been doin and learning this for less than a month

TobyRT

Here's why your links aren't working - you're not echoing your variable properly. You're including the variable as a text string, rather than what the variable contains. This seems to be the case in all of your links...

$prev = $page -1;
echo '<a href="$prev">';

would give you a link of http://mysite.com/$prev - I assume this doesn't exist!

Try this:

'<a href=\"'.$_SERVER['PHP_SELF'].'?page='.$prev.'\"><<Previous</a> ';

limao

oh! ok, thanks; i'll try it out

appreciate it all the help!

limao

nope, didn't work 🙁

i got this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-4, 4' at line 2

to see what i'm doing, here's the link:
http://www.taiwandvr.com/COLORCAMERAS2.php

so when the user types in 'bullet camera', they get the results from my database with everything that has 'bullet' or 'bullet camera' in it. Then i want to paginate those results

ps: its just a testing layout i made, i don't even know if the site is gonna look like this

fire_cracker

I am curious how you are getting -4 for your $from variable. That appears to be the problem. I looked at the code and I don't seem to see the problem, but then again I don't use paganation very often myself.

limao

i was wondering that too (among other things); i'm going to try to figure it out but keep those responses comin, i'm here to learn everything i can

thank you all!

TobyRT

can you repost your modified code - it might make it easier for us to see where it's going wrong...

limao

sure, here it is (i also put the link to this at the bottom):

NEW SEARCH CODE:

<?php
// set your infomation.
$dbhost='localhost:3306';
$dbusername='limao';
$dbuserpass='password';
$dbname = 'CCTVPRODUCTS';

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');
if(isset($_GET['search']))
{
$search = $_GET['search'];
}

$keywords = explode(" ", $search);

// If current page number, use it
// if not, set one!

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Define the number of results per page
$max_results = 4;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

$query = "SELECT * FROM colorcameras 
WHERE camDescription LIKE '%".$keywords['0']."%'LIMIT $from, $max_results";


$result = mysql_query($query) or die(mysql_error());
?>

NEW RESULTS CODE:

<?php
echo "<tr>";
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.camera.'</font></b>';
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.Description.'</font></b>';
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.Supplier.'</font></b>';
echo '<td width="156" align="center" height="20" bgcolor="#446F9D" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<b><font face="Verdana" size="2" color="#FFFFFF">'.Website.'</font></b>';
echo '</td>';
echo '</tr>';
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">'.$row['camName'].'</font></b>';
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">'.$row['camDescription'].'</font></b>';
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">'.$row['camSupplier'].'</font></b>';
echo '<td width="156" align="center" height="24" bgcolor="#5785B3" style="border-right: 1px solid #FFFFFF; border-bottom: 1px solid #FFFFFF">';
echo '<p align="center" style="margin-top: 0; margin-bottom: 0">'.'<br><b><font face="Verdana" size="1" color="#FFFFFF">
<a href="'.$row['website'].'">'.$row['website'].'</a></font></b>';
echo '</td>';
echo '</tr>';
}
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM colorcameras"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";
}

?>

http://www.taiwandvr.com/COLORCAMERAS2.php

TobyRT

OK - For the search code:

$query = "SELECT * FROM colorcameras 
WHERE camDescription LIKE '%".$keywords['0']."%'LIMIT $from, $max_results";

your syntax for $keywords is fine, but $from won't work - what SQL is reading is
"LIMIT $from, $maxresults", instead of "LIMIT 4, 12" you need to put those variables in quote marks so SQL reads what they represent, not the actual text string:

$query ="SELECT * FROM colorcameras WHERE camDescription LIKE '%keywords['0']%' LIMIT '$from','$max_results'";

results code:

Again, you're still not sending the values correctly, look at the difference between what you write and my code below:

echo '<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> ";

What you are saying is to create this link:
<a href="http://myserver.com/results.php?page=$prev>
You are telling it the the value is '$prev', rather than '4', or whatever the value of $prev is.

See below for what you need:

echo '<a href=\"'.$_SERVER['PHP_SELF'].'?page='.$prev.'\"><<Previous</a>';

limao

hey, i tried changing the value but it didn't work. now i get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '4'' at line 2

HeyRay2

When using double quotes, text is treated as literal. This means that if you do not escape out of your echo using ". ." around your variable, it will not print correctly.

$var = "foo";

echo "<a href=\"".$SERVER["PHP_SELF"]."?var=$var\">Link</a>

$var would be printed as "$var", not as it's value of "foo".

This is how you should do your echo statement...

echo "<a href=\"".$_SERVER["PHP_SELF"]."?page=".$prev."\"><<Previous</a>";

Also, in your SQL statement, you should not need to put integer variables like your $from and $max_results in single quotes. The following SQL statement should work fine for you...

 $query = "SELECT * FROM colorcameras
WHERE camDescription LIKE '%".$keywords['0']."%'LIMIT $from, $max_results";

tsinka

Hi,

HeyRay2 wrote:
When using double quotes, text is treated as literal. This means that if you do not escape out of your echo using ". ." around your variable, it will not print correctly.

That's not completely correct. Text is treated as literal when using single quotes. PHP expands variables correctly within double quote strings. Complex variables inside double quote strings need to be enclosed by curly braces.

But the use of string concatenation in most cases improves readability.

Thomas