By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,949 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,949 IT Pros & Developers. It's quick & easy.

Dividing up search results

100+
P: 196
Hi all, i have a php script which retrieves listings from a database and then displays them on a page, the problem is that sometimes the number of listings can be in excess of 100 or more.

So what i wanted to do was split up the resultant listings onto more than 1 page or perhaps on a single page but limit the results visible at any one time, i.e. you show the first 25 listings and the person hits next page and it just updates the page with the next 25 listings; though one problem is that im not sure how to identify which listing number is currently been displayed, my script sort of just dump the information onto a page and now im trying to clean it up a bit.

Any direction or help on how to acomplish this would be great thanks,
Oct 16 '08 #1
Share this Question
Share on Google+
20 Replies


Atli
Expert 5K+
P: 5,058
This is called Paging, and there are a number of useful articles about this available via Google.

Start there, but if you run into any problems, let us know and we will try to help.
Oct 17 '08 #2

100+
P: 196
Thanks heaps this looks exactly what i want, oh also do u know of a way to tell how many search results you get instead of actually getting the search results.
I think i need do find the number of results for the paging code.


Thanks again heaps,
Oct 17 '08 #3

Atli
Expert 5K+
P: 5,058
Something like this should work on most SQL databases.
Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(*) FROM myTable;
  2.  
It would return a single row with a single column that contains the total amount of rows in that table.

You can of course add whatever conditions you like (like WHERE or LIMIT for example) to the query and it will be reflected in the count.
Oct 17 '08 #4

chelvan
P: 90
hi you can find a thread here "Problem on paging". thats may help for you.

chel-1
Oct 17 '08 #5

100+
P: 196
Sweet thanks for info and thanks for the heads up on the previous discussion on this subject aswell.
Oct 17 '08 #6

100+
P: 196
Ok i think i got most of the code work for this put when i click the next page or last page button i get this error --> "Error, query failed" <--

for reference ill put all the code here that im usings (note the code im using is from "http://www.php-mysql-tutorial.com/php-mysql-paging.php") -

[PHP]<?php
$suburb = $_POST["suburb"];
$state = $_POST["state"];
$pcode = $_POST["pcode"];



$con = mysql_connect("127.0.0.1","USERNAME","PASSWORD");
if (!$con)
{

die('Could not connect: ' . mysql_error());
}

else {
echo "stage 1 complete";
mysql_select_db("DATABASENAME", $con);


echo $suburb . '<br />' . $state . '<br />' . $pcode . '<br />';


// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;


$query1 = "SELECT prices, beds, baths, car, logo, houseimage, propadd, blur, moreinfo, suburb FROM `search_results` WHERE `state` ='".$state."' && `pcode` =".$pcode." && `suburb` ='".$suburb."' LIMIT $offset, $rowsPerPage";

$result1 = mysql_query($query1,$con) or die('Error, query failed');

// print the listings
while($row=mysql_fetch_array($result1)) {
echo '<br />' . $row[0] . '<br />';
echo '<br />' . $row[1] . '<br />';
echo '<br />' . $row[2] . '<br />';
echo '<br />' . $row[3] . '<br />';
echo '<br />' . $row[4] . '<br />';
echo '<br />' . $row[5] . '<br />';
echo '<br />' . $row[6] . '<br />';
echo '<br />' . $row[7] . '<br />';
echo '<br />' . $row[8] . '<br />';
echo '<br />' . $row[9] . '<br />';
}

echo '<br>';

// how many rows we have in database
$query = "SELECT COUNT(*) FROM `search_results` WHERE `state` ='".$state."' && `pcode` =".$pcode." && `suburb` ='".$suburb."' ";
$result = mysql_query($query,$con) or die('Error, query failed');

//--------------------------------------------------------------------------
if (!$result)
{

die('Error: ' . mysql_error());
}

while($row=mysql_fetch_array($result)) {
echo '<br />' . $row[0] . '<br />';
$numrows = $row[0];
}
//------------------------------------------------------------------------



// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];

// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link

// print 'previous' link only if we're not
// on page one
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = ' [Prev] '; // we're on page one, don't enable 'previous' link
$first = ' [First Page] '; // nor 'first page' link
}

// print 'next' link only if we're not
// on the last page
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = ' [Next] '; // we're on the last page, don't enable 'next' link
$last = ' [Last Page] '; // nor 'last page' link
}

// print the page navigation link
echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;

mysql_close($con);

}
?>[/PHP]

Any Help with this error would be great cause i have absolutely no idea, thanks heaps
Oct 17 '08 #7

Atli
Expert 5K+
P: 5,058
Try displaying the query that is failing by adding the mysql error and the query to the error message.
For example:
Expand|Select|Wrap|Line Numbers
  1. $query = "SELECT * FROM myTable";
  2. $result = mysql_query($query) 
  3.   or die("Query failed:<pre>". mysql_error() ."\n\n$query</pre>");
  4.  
That should show you why the query is failing.
Oct 17 '08 #8

100+
P: 196
sweet thanks, ill do that
Oct 17 '08 #9

100+
P: 196
Sorry for the long delay, anyways i tried implmenting that code and i narrowed down where the error is actually coming from -

[PHP]$query1 = "SELECT prices, beds, baths, car, logo, houseimage, propadd, blur, moreinfo, suburb FROM `search_results` WHERE `state` ='".$state."' && `pcode` =".$pcode." && `suburb` ='".$suburb."' LIMIT $offset, $rowsPerPage";
$result1 = mysql_query($query1,$con) or die('Error, query failed'); [/PHP]

The error occurs whenever i try to load the next page and im not sure why the error actually occurs, it just says that the query failed which is a little strange cause it works on the first page perfectally fine.

Any ideas would be greath, thanks
Oct 23 '08 #10

chelvan
P: 90
Sorry for the long delay, anyways i tried implmenting that code and i narrowed down where the error is actually coming from -

[PHP]$query1 = "SELECT prices, beds, baths, car, logo, houseimage, propadd, blur, moreinfo, suburb FROM `search_results` WHERE `state` ='".$state."' && `pcode` =".$pcode." && `suburb` ='".$suburb."' LIMIT $offset, $rowsPerPage";
$result1 = mysql_query($query1,$con) or die('Error, query failed'); [/PHP]

The error occurs whenever i try to load the next page and im not sure why the error actually occurs, it just says that the query failed which is a little strange cause it works on the first page perfectally fine.

Any ideas would be greath, thanks
hi
you need to pass the where condition's variables $state,$pcode,$suburb on the links pre,<num>,next (actullay this links displays your page bottom). to indiacte the conditions to the next page.

try it.
yours may be work.

regards.
chel-1
Oct 23 '08 #11

100+
P: 196
Ok that sounds like it may work but how would set it up to send $state, $postcode, $suburb when someone clicks the next button, to the next page and for that matter how would i recieve it on the next page since it don't exist until it clicked on??

Also which line are you talking about adding it to??

Thanks for the help,
Oct 23 '08 #12

chelvan
P: 90
Ok that sounds like it may work but how would set it up to send $state, $postcode, $suburb when someone clicks the next button, to the next page and for that matter how would i recieve it on the next page since it don't exist until it clicked on??

Also which line are you talking about adding it to??

Thanks for the help,
hi

i think you have to set those variables, where you initialize the hyper links for previous , next , nums.

so...
it should like this
Expand|Select|Wrap|Line Numbers
  1. $prev = " <a href=\"$self?page=$page&somename=$variable\">[Prev]</a>
  2.  
then you need to get the somename using $_get['somename'] initialize to your where clause variable.


try it.

regards
chel-1
Oct 23 '08 #13

P: 3
The thing you want to implement is called pagination. To improve your performance you should implement AJAX. By this you have to fetch all the records from database only at once and you can use it several times. To complete your requirement yo have to do the following:

1. Set the limit that how many records at a time you want to display per page in a config file.

2. Then on the very first time of loading your page you have to maintain some values like startcount, lastcount, previouscount, nextcount. After drawing each page you have to set these discussed variables to fetch more record from the database.

3. Now by using these values you have to start only a loop to grab the part of records from the whole one.
Oct 23 '08 #14

Atli
Expert 5K+
P: 5,058
The thing you want to implement is called pagination. To improve your performance you should implement AJAX.
I would suggest getting the PHP pagination right first before moving on to AJAX, as the AJAX solution will undoubtedly be somewhat more complicated and include a lot more client-side code.

As to the problem you are discussing.

To get this working, you are going to have to pass along ALL the required information via the link. The $_POST array will be cleared once a link is clicked so you need to pass this info along some other way.

Consider this:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // Get the chosen state.
  3. // First look in the $_POST array, if that doesn't work
  4. // look in the $_GET array. If that doesn't work either
  5. // simply set a default state, or whatever.
  6. if(isset($_POST['state'])) {
  7.   $state = $_POST['state'];
  8. }
  9. else if(isset($_GET['state'])) {
  10.   $state = $_GET['state'];
  11. }
  12. else {
  13.   user_error("State was not passed. Using default.", E_USER_NOTICE);
  14.   $state = "Whatever state you default to";
  15. }
  16.  
  17. // Get the page index. Default to 0 if none was passed.
  18. ($index = @$_GET['index']) or $index = 0;
  19.  
  20. /* This is where you would print the current page **/
  21.  
  22. // Print the links
  23. for($i = 0; $i < $totalPages; $i++) {
  24.   echo '<a href="?index='. $i .'&amp;state='. $state .'">'. ($i + 1) .'</a>&nbsp;';
  25. }
  26. ?>
  27.  
This assumes only one additional condition for the WHERE clause, a 'state'.
Notice how I pass it along into the link and then try to fetch it at the top.
Oct 23 '08 #15

100+
P: 196
Ok this sounds great, let me have a little fiddle around and see if i can't get it working thanks everyone for the contributions.
Oct 24 '08 #16

100+
P: 196
Ok now i got one straight forward problem and one thats a little strange,

First up i tried to implement the following code

[PHP]$next = '<a href="?page=$page?suburb=$suburb?state=$state?pcod e=$pcode" />[Next]</a> ';

echo $next;[/PHP]

I know this is not exactly identical to code used by Atli above but i tried a couple variations with this and his and couldn't exactly get it to send properly so i was just wondering where i went wrong with this.

Now for the strange problem even though the information was transfered properly using the defualt statment seemed to work i.e. when i checked what values they were set to the defualt which is good but the problem is that even with the defualt statement i still get a msql error when trying to executed this code -

[PHP]$query1 = "SELECT prices, beds, baths, car, logo, houseimage, propadd, blur, moreinfo, suburb FROM `search_results` WHERE `state` ='".$state."' && `pcode` =".$pcode." && `suburb` ='".$suburb."' LIMIT $offset, $rowsPerPage";
$result1 = mysql_query($query1,$con) or die('Error, query failed'); [/PHP]

All help is much appreciated
Oct 25 '08 #17

Atli
Expert 5K+
P: 5,058
The values should be separated by &, not ?.
Like:
Expand|Select|Wrap|Line Numbers
  1. page.php?var1=val2&var2=val2
And, & being a special char in HTML, it should be replaced by it's HTML entity, &amp;
Making it:
Expand|Select|Wrap|Line Numbers
  1. page.php?var2=val2&amp;var2=val2
Otherwise you will get a warning when validating.
Oct 25 '08 #18

100+
P: 196
Sweet thanks heaps, ill do that then
Oct 25 '08 #19

100+
P: 196
Sweet i found out what was causing the msql problem it was simply that the page number was not be passed correctly and i overlooked it.

Thanks everyone for the help
Oct 25 '08 #20

100+
P: 196
Just a bit of a follow up question, with putting varibles into an a url to pass to another page.

I was wondering if it is possible to send a string containing a url to another completely different website in the url aswell for example -

Expand|Select|Wrap|Line Numbers
  1. $link = "www.google.com" //Note this changes depending on other stuff
  2. $part1 = '<a href="test.php';
  3. $part2 = '&amp;moreinfo=';
  4. $part3 = '" />More Info</a> ';
  5.  
  6. $moreinfo = $part1 . $part2 .$link . $part3;
Thanks,
Nov 5 '08 #21

Post your reply

Sign in to post your reply or Sign up for a free account.