Dividing up search results

[chazzy69]

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,

[Atli]

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.

[chazzy69]

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,

[Atli]

Something like this should work on most SQL databases.

SELECT COUNT(*) FROM myTable;
 

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.

[chelvan]

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

chel-1

[chazzy69]

Sweet thanks for info and thanks for the heads up on the previous discussion on this subject aswell.

[chazzy69]

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

[Atli]

Try displaying the query that is failing by adding the mysql error and the query to the error message.
For example:

$query = "SELECT * FROM myTable";
$result = mysql_query($query) 
  or die("Query failed:<pre>". mysql_error() ."\n\n$query</pre>");
 

That should show you why the query is failing.

[chazzy69]

sweet thanks, ill do that

[chazzy69]

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

[chelvan]

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

[chazzy69]

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,

[chelvan]

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

$prev = " <a href=\"$self?page=$page&somename=$variable\">[Prev]</a>
 

then you need to get the somename using $_get['somename'] initialize to your where clause variable.


try it.

regards
chel-1

[akarsh]

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.

[Atli]

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:

<?php
// Get the chosen state.
// First look in the $_POST array, if that doesn't work
// look in the $_GET array. If that doesn't work either
// simply set a default state, or whatever.
if(isset($_POST['state'])) {
  $state = $_POST['state'];
}
else if(isset($_GET['state'])) {
  $state = $_GET['state'];
}
else {
  user_error("State was not passed. Using default.", E_USER_NOTICE);
  $state = "Whatever state you default to";
}
 
// Get the page index. Default to 0 if none was passed.
($index = @$_GET['index']) or $index = 0;
 
/* This is where you would print the current page **/
 
// Print the links
for($i = 0; $i < $totalPages; $i++) {
  echo '<a href="?index='. $i .'&amp;state='. $state .'">'. ($i + 1) .'</a>&nbsp;';
}
?>
 

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.

[chazzy69]

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.

[chazzy69]

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

[Atli]

The values should be separated by &, not ?.
Like:

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:

page.php?var2=val2&var2=val2

Otherwise you will get a warning when validating.

[chazzy69]

Sweet thanks heaps, ill do that then

[chazzy69]

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

[chazzy69]

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 -

$link = "www.google.com" //Note this changes depending on other stuff
$part1 = '<a href="test.php';
$part2 = '&amp;moreinfo=';
$part3 = '" />More Info</a> ';
 
$moreinfo = $part1 . $part2 .$link . $part3;

Thanks,