Jump to content

Help needed with paged search results

Gwaihirjp

By Gwaihirjp
in PHP Coding Help

 Share

Recommended Posts

Gwaihirjp Newbie

Gwaihirjp

Posted
Posted

Hello everyone! I am a total noob here and this is my first post, so I'll introduce myself first. I'm from Japan and I design/program websites with HTML and CSS. But I've always wanted to learn how to use PHP/MySQL to build dynamic websites, and now I am trying to build my very first PHP webpage with a search form that returns data from a server. And, I think that I have been pretty successful so far considering how little I know. However, I have come to a point where I am completely stuck and desperately in need of help.

The problem is this: The search results are paged (10 results per paged) and the first page works fine, but the links to the "prev" and "next" pages don't work.

 

You see, the database contains details of many cars (from a car dealer), and my search form asks for the car's manufacturer (i.e.Toyota), model name, and year. The PHP code goes like this:

$maker=$_POST['maker'];
	$model=$_POST['model'];
	$from=$_POST['from'];
	$to=$_POST['to'];

	// rows to return
	$limit=10;

	$db=@mysql_connect("localhost","username","password");
	if (!$db)
		{die("Unable to connect to database".mysql_error());}

	mysql_select_db("database") or die("Unable to select database"); //select which database we're using
	$query="select * from table1 where manufacturer like '%$maker%' && name like '%$model%' && year>='$from' && year<='$to' order by year desc, name, stock_no";

	if ($maker=="all" && $model==null)
	{$query="select * from stocklist where year>='$from' && year<='$to' order by year desc";
	}
	if ($maker=="all" && $model!=null)
	{$query="select * from stocklist where name like '%$model%' && year>='$from' && year<='$to' order by year desc, name, stock_no";
	}


	$numresults=mysql_query($query);
	$numrows=mysql_num_rows($numresults);

	if ($numrows == 0)
	{
	die ("<br />Sorry, your search returned no results.");
	}

	if (empty($s))
	{$s=0;}

	$query.=" limit $s,$limit";

	$result=mysql_query($query) or die ("couldn't reach query");

	echo "Results for search "$maker $model"";

	$color1="#ffffff";
	$color2="#ffffaa";

	echo "<br /><br /><table cellspacing=/0/>";		
	$count=1+$s;

	while ($row=mysql_fetch_array($result)) {
	$stockno=$row['1'];
	$year=$row['2'];
	$manufacturer=$row['4'];
	$modelname=$row['5'];
	$chassis=$row['6'];
	$trans=$row['7'];
	$color=$row['8'];
	$ps=$row['9'];
	$pw=$row['10'];
	$ac=$row['11'];
	$door=$row['12'];
	$seat=$row['13'];
	$grade=$row['14'];
	$other=$row['15'];
	$fuel=$row['16'];
	$cc=$row['17'];
	$price=$row['18'];
	$russia=$row['19'];
	$exterior=$row['20'];
	$interior=$row['21'];
	$availability=$row['22'];
	$rowcolor=($count % 2) ? $color1:$color2;
	echo "<tr height=/30px/ bgcolor=/$rowcolor/ nowrap>
			<td> $count.</td>
			<td>$stockno</td>
			<td>$year</td>
			<td>$manufacturer</td>
			<td>$modelname</td>
			<td>$chassis</td>
			<td>$trans</td>
			<td>$color</td>
			<td>$ps</td>
			<td>$pw</td>
			<td>$ac</td>
			<td>$door</td>
			<td>$seat</td>
			<td>$grade</td>
			<td>$other</td>
			<td>$availability</td>
			<td><a href=/#/><b>DETAILS</b></a></td>
		</tr>" ;
	$count++;
	}

	$currpage=(($s/$limit)+1);

	echo "</table><br />";

	if ($s>=1) { // bypass PREV link if s is 0
	$prevs=($s-$limit);
	print "<a href=\"$PHP_SELF?s=$prevs&q=$maker\">Prev 5</a>  ";
	}

	$pages=intval($numrows/$limit);

	if ($numrows%$limit) {
	$pages++;
	}

	if (!(($s+$limit)==$pages) && $pages!=1) {
	$news=$s+$limit;
	print " <a href=\"$PHP_SELF?s=$news&q=$maker\">Next 5 >></a>";
	}

	$a=$s+($limit);
	if ($a>$numrows) {$a=$numrows;}
	$b=$s+1;
	echo "Showing results $b to $a of $numrows";
	?>

I know this code must be pretty junky, but this really is my first time with PHP so please forgive me. I think that the last part must be garbled up and that's why the links lead nowhere but to the initial page without results. Please, can someone help me figure this out? I really want this paged results to work.

Can anyone

Link to comment
Share on other sites
GingerRobot Regular Member

GingerRobot

Posted
Posted

At first glance, I can see a few problems.

 

1.) You pass a number in the URL to indicate which record to start at. So far, so good. But, you don't extract this variable from the $_GET array - you just use it. Assuming magic_quotes is turned off (which it should be), $s wont be defined. You need to do $s = $_GET['s'] before you can use it.

 

2.) It looks like you have intended to pass the user's search criteria through the URL, which is good. However, as far as i can see - you only pass $maker through the URL, not the model. Again, it's not extracted from the $_GET array and again, you test it before it's been defined at all.

 

3.) You want to be setting the number of pages using ceil(), not intval(). You need to always round up, otherwise you'll lose some records.

Link to comment
Share on other sites
Gwaihirjp Newbie

Gwaihirjp

Posted
  • Author
Posted

Hi GingerRobot, thanks for your help! :) I think I see what you mean about the $_GET array. Just now I did $s=$_GET['s'] before the URL and replaced intval() with ceil(). Is there anything else I need to do?

What would be the correct URL to for the "next" link if I want to pass all the search criteria to the next page? Do I need to do $_GET for "maker", "model" and years "from" & "to" as well?

 

Sorry I'm asking so many questions.

Link to comment
Share on other sites
Gwaihirjp Newbie

Gwaihirjp

Posted
  • Author
Posted

Do I need to do $_GET for "maker", "model" and years "from" & "to" as well?

 

Yep - you perform the search each time the page loads so you need to know what your searching for everytime.

Okay, I've changed the form a bit so that it's method="get" instead of "post" because I thought that maybe it might be easier. This is how it looks now: 
<?php
	$maker=$_GET['maker'];
	$model=$_GET['model'];
	$from=$_GET['from'];
	$to=$_GET['to'];

	// rows to return
	$limit=10;

	$db=@mysql_connect("localhost","username","password");
	if (!$db)
		{die("Unable to connect to database".mysql_error());}

	mysql_select_db("database") or die("Unable to select database"); //select which database we're using
	$query="select * from stocklist where manufacturer like '%$maker%' && name like '%$model%' && year>='$from' && year<='$to' order by year desc, name, stock_no";

	if ($maker=="all" && $model==null)
	{$query="select * from stocklist where year>='$from' && year<='$to' order by year desc";
	}
	if ($maker=="all" && $model!=null)
	{$query="select * from stocklist where name like '%$model%' && year>='$from' && year<='$to' order by year desc, name, stock_no";
	}


	$numresults=mysql_query($query);
	$numrows=mysql_num_rows($numresults);

	if ($numrows == 0)
	{
	die ("<br />Sorry, your search returned no results.");
	}


	if (empty($s))
	{$s=0;}

	$query.=" limit $s,$limit";

	$result=mysql_query($query) or die ("couldn't reach query");

	echo "Results for search "$maker $model"";

	$color1="#ffffff";
	$color2="#ff00aa";

	echo "<br /><br /><table cellspacing=/0/>";		
	$count=1+$s;

	while ($row=mysql_fetch_array($result)) {
	$stockno=$row['1'];
	$year=$row['2'];
	$manufacturer=$row['4'];
	$modelname=$row['5'];
	$chassis=$row['6'];
	$trans=$row['7'];
	$color=$row['8'];
	$ps=$row['9'];
	$pw=$row['10'];
	$ac=$row['11'];
	$door=$row['12'];
	$seat=$row['13'];
	$grade=$row['14'];
	$other=$row['15'];
	$fuel=$row['16'];
	$cc=$row['17'];
	$price=$row['18'];
	$russia=$row['19'];
	$exterior=$row['20'];
	$interior=$row['21'];
	$availability=$row['22'];
	$rowcolor=($count % 2) ? $color1:$color2;
	echo "<tr height=/30px/ bgcolor=/$rowcolor/ nowrap>
			<td> $count.</td>
			<td>$stockno</td>
			<td>$year</td>
			<td>$manufacturer</td>
			<td>$modelname</td>
			<td>$chassis</td>
			<td>$trans</td>
			<td>$color</td>
			<td>$ps</td>
			<td>$pw</td>
			<td>$ac</td>
			<td>$door</td>
			<td>$seat</td>
			<td>$grade</td>
			<td>$other</td>
			<td>$availability</td>
			<td><a href=/#/><b>DETAILS</b></a></td>
		</tr>" ;
	$count++;
	}

	$currpage=(($s/$limit)+1);

	echo "</table><br />";

	if ($s>=1) { // bypass PREV link if s is 0
	$prevs=($s-$limit);
	print "<a href=\"$PHP_SELF?s=$prevs&maker=$maker&model=$model&from=$from&to=$to\">Prev 10</a>  ";
	}

	$pages=ceil($numrows/$limit);


	if ($numrows%$limit) {
	$pages++;
	}

	if (!(($s+$limit)==$pages) && $pages!=1) {
	$news=$s+$limit;
	print " <a href=\"$PHP_SELF?s=$news&maker=$maker&model=$model&from=$from&to=$to\">Next 10 >></a>";
	}

	$a=$s+($limit);
	if ($a>$numrows) {$a=$numrows;}
	$b=$s+1;
	echo "Showing results $b to $a of $numrows";

	?>

But, now when I click on the "next" link, the exact same page with the exact same results comes back (as opposed to a page with no results) and leads nowhere.

Furthermore, there's a new problem: the "next" button comes up even when the number of results is less than 10 and therefore unnecessary. It started happening when I replaced intval() with ceil().

Please help!

Link to comment
Share on other sites
GingerRobot Regular Member

GingerRobot

Posted
Posted

$s is sitll undefined. In fact, i think you might be causing yourself a lot of headaches with the way you're dealing with the page numbers. Rather than passing around all these different variables (you seen to have $s, and a to and from variable in the URL(which you're doing nothing with)) - just use a single variable to use as the page number. Let's call it $page, and pass it around in the URL. For starters, you'll need to make sure it's set. If it's not, default to page 1:

 

if(isset($_GET['page'])){
    $page  = (int) $_GET['page']; //type casting to ensure $s is an int
}else{
    $page = 1;
}

 

Now, you're limits of the query can then be ascertained from the page number. The start is ($page-1) * $limit and the number of records is obviously just $limit.

 

Finally, for your pages at the bottom, you can just compare $page with 1 and the number of pages to work out if you should show previous and next links. E.g.:

 

if($page!=1){
    //show previous link with page set to $page-1;
}
if($page > $totalpages){
    //show next link with page set to $page+1. $totalpages is simply ceil($rows/$limit)
}

 

You might want to take a look at this tutorial if you're still stuck.

 

Link to comment
Share on other sites
Gwaihirjp Newbie

Gwaihirjp

Posted
  • Author
Posted

I followed your guideline and that tutorial (can't believe I hadn't looked there), and managed to get the links to move between the pages!!  I can see the "next" and "prev" links in the right places. :) But, the table and its content still remains the same (Results 1 to 10) no matter what page it's on. Is it because I'm missing the "LIMIT $offset, $rowsperpage" in the query like it's written in the tutorial?

 

<?php
	$maker=$_GET['maker'];
	$model=$_GET['model'];
	$from=$_GET['from'];
	$to=$_GET['to'];


	$db=@mysql_connect("localhost","username","password");
	if (!$db)
		{die("Unable to connect to database".mysql_error());}

	mysql_select_db("database") or die("Unable to select database"); //select which database we're using


	// rows to return
	$limit=10;

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

	$query="select * from stocklist where manufacturer like '%$maker%' && name like '%$model%' && year>='$from' && year<='$to' order by year desc, name, stock_no";

	if ($maker=="all" && $model==null)
	{$query="select * from stocklist where year>='$from' && year<='$to' order by year desc";
	}
	if ($maker=="all" && $model!=null)
	{$query="select * from stocklist where name like '%$model%' && year>='$from' && year<='$to' order by year desc, name, stock_no";
	}


	$numresults=mysql_query($query);
	$numrows=mysql_num_rows($numresults);

	if ($numrows == 0)
	{
	die ("<br />Sorry, your search returned no results.");
	}

	if (empty($s))
	{$s=0;}

	$query.=" limit $s,$limit";

	$result=mysql_query($query) or die ("couldn't reach query");

	echo "Results for search "$maker $model"";

	$color1="#ddffff";
	$color2="#ffffff";

	echo "<br /><br /><table cellspacing=/0/>";		
	$count=1+$s;

	while ($row=mysql_fetch_array($result)) {
	$stockno=$row['1'];
	$year=$row['2'];
	$manufacturer=$row['4'];
	$modelname=$row['5'];
	$chassis=$row['6'];
	$trans=$row['7'];
	$color=$row['8'];
	$ps=$row['9'];
	$pw=$row['10'];
	$ac=$row['11'];
	$door=$row['12'];
	$seat=$row['13'];
	$grade=$row['14'];
	$other=$row['15'];
	$fuel=$row['16'];
	$cc=$row['17'];
	$price=$row['18'];
	$russia=$row['19'];
	$exterior=$row['20'];
	$interior=$row['21'];
	$availability=$row['22'];
	$rowcolor=($count % 2) ? $color1:$color2;
	echo "<tr height=\"30px\" bgcolor=\"$rowcolor\" nowrap>
			<td> $count.</td>
			<td>$stockno</td>
			<td>$year</td>
			<td>$manufacturer</td>
			<td>$modelname</td>
			<td>$chassis</td>
			<td>$trans</td>
			<td>$color</td>
			<td>$ps</td>
			<td>$pw</td>
			<td>$ac</td>
			<td>$door</td>
			<td>$seat</td>
			<td>$grade</td>
			<td>$other</td>
			<td>$availability</td>
			<td><a href=/#/><b>DETAILS</b></a></td>
		</tr>" ;
	$count++;
	}//end while

	$currpage=(($s/$limit)+1);

	echo "</table><br />";

	if ($page>1) { // bypass PREV link if s is 0
	$previous=$page-1;
	print "<a href=\"$PHP_SELF?maker=$maker&model=$model&from=$from&to=$to&page=$previous\">Prev 10</a>  ";
	}

	$totalpages=ceil($numrows/$limit);
	if($page!=$totalpages) {
	$next=$page+1;
	print " <a href=\"$PHP_SELF?maker=$maker&model=$model&from=$from&to=$to&page=$next\">Next 10 >></a>";
	}

	$a=$s+($limit);
	if ($a>$numrows) {$a=$numrows;}
	$b=$s+1;
	echo "Showing results $b to $a of $numrows";

	?>

Link to comment
Share on other sites
Gwaihirjp Newbie

Gwaihirjp

Posted
  • Author
Posted

Thank you thank you!!!!! Now it works perfectly! I added the lines like you said and replaced all the $s with $offset, and now it works just like I wanted it to.

Here's the final code, in case someone comes with the same problem in the future.

 

<?php

	$maker=$_GET['maker'];

	$model=$_GET['model'];

	$from=$_GET['from'];

	$to=$_GET['to'];





	$db=@mysql_connect("localhost","username","password");

	if (!$db)

		{die("Unable to connect to database".mysql_error());}



	mysql_select_db("database") or die("Unable to select database"); //select which database we're using





	// rows to return

	$limit=10;



	if(isset($_GET['page'])) {

	$page=(int) $_GET['page'];

	} else {

	$page=1;

	}



	$query="select * from stocklist where manufacturer like '%$maker%' && name like '%$model%' && year>='$from' && year<='$to' order by year desc, name, stock_no";



	if ($maker=="all" && $model==null)

	{$query="select * from stocklist where year>='$from' && year<='$to' order by year desc";

	}

	if ($maker=="all" && $model!=null)

	{$query="select * from stocklist where name like '%$model%' && year>='$from' && year<='$to' order by year desc, name, stock_no";

	}





	$numresults=mysql_query($query);

	$numrows=mysql_num_rows($numresults);



	if ($numrows == 0)

	{

	die ("<br />Sorry, your search returned no results.");

	}



	$offset=($page-1)*$limit;

	$query.=" LIMIT $offset,$limit";



	$result=mysql_query($query) or die ("couldn't reach query");



	echo "$numrows matches found for your search "$maker $model $from ~ $to"";



	$color1="#ddffff";

	$color2="#ffffff";



	echo "<br /><br /><table cellspacing=/0/>";		

	$count=1+$offset;



	while ($row=mysql_fetch_array($result)) {

	$stockno=$row['1'];

	$year=$row['2'];

	$manufacturer=$row['4'];

	$modelname=$row['5'];

	$chassis=$row['6'];

	$trans=$row['7'];

	$color=$row['8'];

	$ps=$row['9'];

	$pw=$row['10'];

	$ac=$row['11'];

	$door=$row['12'];

	$seat=$row['13'];

	$grade=$row['14'];

	$other=$row['15'];

	$fuel=$row['16'];

	$cc=$row['17'];

	$price=$row['18'];

	$russia=$row['19'];

	$exterior=$row['20'];

	$interior=$row['21'];

	$availability=$row['22'];

	$rowcolor=($count % 2) ? $color1:$color2;

	echo "<tr height=\"30px\" bgcolor=\"$rowcolor\" nowrap>

			<td> $count.</td>

			<td>$stockno</td>

			<td>$year</td>

			<td>$manufacturer</td>

			<td>$modelname</td>

			<td>$chassis</td>

			<td>$trans</td>

			<td>$color</td>

			<td>$ps</td>

			<td>$pw</td>

			<td>$ac</td>

			<td>$door</td>

			<td>$seat</td>

			<td>$grade</td>

			<td>$other</td>

			<td>$availability</td>

			<td><a href=/#/><b>DETAILS</b></a></td>

		</tr>" ;

	$count++;

	}//end while



	$currpage=(($s/$limit)+1);



	echo "</table><br />";



	if ($page>1) { // bypass PREV link if s is 0

	$previous=$page-1;

	print "<a href=\"$PHP_SELF?maker=$maker&model=$model&from=$from&to=$to&page=$previous\"><<Prev 10</a>  ";

	}



	$totalpages=ceil($numrows/$limit);

	if($page!=$totalpages) {

	$next=$page+1;

	print " <a href=\"$PHP_SELF?maker=$maker&model=$model&from=$from&to=$to&page=$next\">Next 10 >></a>  ";

	}



	$a=$offset+($limit);

	if ($a>$numrows) {$a=$numrows;}

	$b=$offset+1;

	echo "Showing results $b to $a of $numrows";




	?>

Thanks again! :D

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.