DaniWeb Forum Index > Web Development > PHP

How to limit page numbers for search results for database website

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2008

Posts: 37

Reputation: 123468743867143 is an unknown quantity at this point

Solved Threads: 0

123468743867143 123468743867143 is offline

Offline

Light Poster

How to limit page numbers for search results for database website

Nov 12th, 2008

Good day everyone from beautiful California,

I have been able to limit the number of listings showing per page ($per_page=10)
But how do I limit the number of pages so I do not have rows after rows of pages.

The way it looks now:
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 >>

The way it should look:
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 >>

The code:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$option_page=$_REQUEST[option_page];
$per_page = 10;
$sqlsearch="select a.*,b.session_id from fsbo_listing a, searchresults b where a.listingid=b.listingid and b.session_id=\"$sessionid\" order by b.searchid";
//echo"<br>Final Query-->".$sqlsearch;
if (!isset($option_page)) {
$option_page = 1;
}
$prev_option_page = $option_page - 1;
$next_option_page = $option_page + 1;
$option_query = mysql_query($sqlsearch);
$option_page_start = ($per_page * $option_page) - $per_page;
$num_rows = mysql_num_rows($option_query);
if ($num_rows <= $per_page) {
$num_pages = 1;
} else if (($num_rows % $per_page) == 0) {
$num_pages = (int)($num_rows / $per_page);
} else {
$num_pages = (int)($num_rows / $per_page) + 1;
}
$sqlsearch = $sqlsearch . "  LIMIT $option_page_start, $per_page";
$option_page=$_REQUEST[option_page];
$per_page = 10;
$sqlsearch="select a.*,b.session_id from fsbo_listing a, searchresults b where a.listingid=b.listingid and b.session_id=\"$sessionid\" order by b.searchid";
//echo"<br>Final Query-->".$sqlsearch;
if (!isset($option_page)) {
$option_page = 1;
}
$prev_option_page = $option_page - 1;
$next_option_page = $option_page + 1;
$option_query = mysql_query($sqlsearch);
$option_page_start = ($per_page * $option_page) - $per_page;
$num_rows = mysql_num_rows($option_query);
if ($num_rows <= $per_page) {
$num_pages = 1;
} else if (($num_rows % $per_page) == 0) {
$num_pages = (int)($num_rows / $per_page);
} else {
$num_pages = (int)($num_rows / $per_page) + 1;
}
$sqlsearch = $sqlsearch . "  LIMIT $option_page_start, $per_page";

THANK YOU A MILLION TIMES.

Natasha

Last edited by peter_budo; Nov 13th, 2008 at 5:43 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Oct 2006

Posts: 232

Reputation: Rhyan is an unknown quantity at this point

Solved Threads: 24

Rhyan

Offline

Posting Whiz in Training

Re: How to limit page numbers for search results for database website

Nov 13th, 2008

Ok, i just have a short question: Your $sqlsearch is used both for displaying the results from the search and making the page numbers in the bottom, or you use it ONLY to create the page numbers on the bottom of your search page.

I would suggest you the following:
You should have 2 querries - one responsible for retrieving the search results, and one dedicated for the page numbers only.
Your display query should look something like:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
if (!isset($current_page))
{
$current_page=1;
}
SELECT a.* b.8 FROM a, b WHERE .... LIMIT ($omit_records, $display_this_number_per_page);
if (!isset($current_page))
{
$current_page=1;
}
SELECT a.* b.8 FROM a, b WHERE .... LIMIT ($omit_records, $display_this_number_per_page);

Where $display_this_number_per_page = 10; ( e.g. you want to show 10 records on your page)
and $omit_records= $display_this_number_per_page * $current_page;

The second query will take care of the page numbers only.
It should look something like

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
SELECT a.* b.8 FROM a, b WHERE (whatever you like);
SELECT a.* b.8 FROM a, b WHERE (whatever you like);

Note that there is no limit clause in the select statement, wich will return always the total number of records. You need to count this number of records in order to create the number of pages. So

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$total_records = count(mysql_num_rows(query));
if (($total_records % $display_this_number_per_page)==0)
 {
 $total_pages = $total_records / $display_this_number_per_page;
}
else
  {
  $total_pages = ($total_records / $display_this_number_per_page)+1;
  }
$total_records = count(mysql_num_rows(query));
if (($total_records % $display_this_number_per_page)==0)
 {
 $total_pages = $total_records / $display_this_number_per_page;
}
else
  {
  $total_pages = ($total_records / $display_this_number_per_page)+1;
  }

Then you have to display your page numbers

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
for($i=0;$i<$total_pages;$i++)
  {
  echo '<a href="?current_page="'.$i.'">'.$i.'</a>';
  }
for($i=0;$i<$total_pages;$i++)
  {
  echo '<a href="?current_page="'.$i.'">'.$i.'</a>';
  }

So, in short this should look like this:

e.g. you have 45 records in your db. You want to show 10 records per page, and you want to show the page numbers for quick selection, the bottom line should look like
1 | 2 | 3 | 4 | 5

" Of all the things I've lost,
I miss my mind the most...."
Mark Twain

•

Join Date: Aug 2008

Posts: 37

Reputation: 123468743867143 is an unknown quantity at this point

Solved Threads: 0

123468743867143 123468743867143 is offline

Offline

Light Poster

Re: How to limit page numbers for search results for database website

Nov 13th, 2008

Thank you Rhyanfor your reply. Unfortunatly I am unable to work your suggestion into my code. It seems that all it does is limit to 10 the number of listings displayed per page, not the number of pages.

I already have it set up for 10 listings per page. Problem arises when they are (let's say) 9000 listings which will show 900 page numbers on numerous rows.

1 | 2 | ETC |900 ... Too many page numbers showing on too many rows.

I tried:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
if ($num_rows > 100) {
$num_pages = 10;
}
if ($num_rows > 100) {
$num_pages = 10;
}

But what it does is only display the first 10 page numbers with no possibility to go to the next 800 pages.

It should be simple … what is wrong with my code?

All I want is to show:

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 >>

If someone clicks on >>, it takes them to the next 10 pages and so forth.

Thank you for your help.

Natasha

Last edited by peter_budo; Nov 13th, 2008 at 5:42 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Apr 2005

Posts: 1,382

Reputation: ShawnCplus is a glorious beacon of light

Solved Threads: 217

ShawnCplus ShawnCplus is offline

Offline

Code Monkey

Re: How to limit page numbers for search results for database website

Nov 13th, 2008

Where is the code for your loop that is actually printing out the links? It's not in any of the code you've posted.

GCS d- s+ a-->? C++(++++) UL+++ P+>+++ L+++ E--- W+++
N+ o K w++(---) O? !M- V PS+>++ PE+ Y+ PGP !t- 5? X- R tv+
b+>++ DI+ D G++>+++ e+ h+>++ r y+
PMs asking for help will not be answered, post on the forums. That's what they're there for.

•

Join Date: Aug 2008

Posts: 37

Reputation: 123468743867143 is an unknown quantity at this point

Solved Threads: 0

123468743867143 123468743867143 is offline

Offline

Light Poster

Re: How to limit page numbers for search results for database website

Nov 13th, 2008

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
echo"<tr><td align='right'>";
if ($prev_option_page)  {
echo "<a href='qualify_search.php?option_page=$prev_option_page&property_type=$_REQUEST[property_type]&region_id=$_REQUEST[region_id]&country_id=$_REQUEST[country_id]&state_id=$_REQUEST[state_id]&minprice=$_REQUEST[minprice]&maxprice=$_REQUEST[maxprice]' class='smalltext'> &lt;&lt; </a> | ";
}
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $option_page) {
echo "<a href='qualify_search.php?option_page=$i&property_type=$_REQUEST[property_type]&region_id=$_REQUEST[region_id]&country_id=$_REQUEST[country_id]&state_id=$_REQUEST[state_id]&minprice=$_REQUEST[minprice]&maxprice=$_REQUEST[maxprice]'  class='smalltext'> $i</a> | ";
} else {
echo '<b><font color=#0C0C7E>' . $i . '</font></b> | ';
}
}
// Next
if ($option_page < $num_pages) {
echo"<a href='qualify_search.php?option_page=$next_option_page&property_type=$_REQUEST[property_type]&region_id=$_REQUEST[region_id]&country_id=$_REQUEST[country_id]&state_id=$_REQUEST[state_id]&minprice=$_REQUEST[minprice]&maxprice=$_REQUEST[maxprice]'  class='smalltext'> &gt;&gt; </a>";
}
echo"</td></tr>";
}else{
$country_name=getcountryname($cntid);
echo"<tr><td align='right'>";
if ($prev_option_page)  {
echo "<a href='qualify_search.php?option_page=$prev_option_page&property_type=$_REQUEST[property_type]&region_id=$_REQUEST[region_id]&country_id=$_REQUEST[country_id]&state_id=$_REQUEST[state_id]&minprice=$_REQUEST[minprice]&maxprice=$_REQUEST[maxprice]' class='smalltext'> &lt;&lt; </a> | ";
}
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $option_page) {
echo "<a href='qualify_search.php?option_page=$i&property_type=$_REQUEST[property_type]&region_id=$_REQUEST[region_id]&country_id=$_REQUEST[country_id]&state_id=$_REQUEST[state_id]&minprice=$_REQUEST[minprice]&maxprice=$_REQUEST[maxprice]'  class='smalltext'> $i</a> | ";
} else {
echo '<b><font color=#0C0C7E>' . $i . '</font></b> | ';
}
}
// Next
if ($option_page < $num_pages) {
echo"<a href='qualify_search.php?option_page=$next_option_page&property_type=$_REQUEST[property_type]&region_id=$_REQUEST[region_id]&country_id=$_REQUEST[country_id]&state_id=$_REQUEST[state_id]&minprice=$_REQUEST[minprice]&maxprice=$_REQUEST[maxprice]'  class='smalltext'> &gt;&gt; </a>";
}
echo"</td></tr>";
}else{
$country_name=getcountryname($cntid);

Last edited by peter_budo; Nov 13th, 2008 at 5:44 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Apr 2005

Posts: 1,382

Reputation: ShawnCplus is a glorious beacon of light

Solved Threads: 217

ShawnCplus ShawnCplus is offline

Offline

Code Monkey

Re: How to limit page numbers for search results for database website

Nov 13th, 2008

Firsly, use code tags. Secondly this is what you need to change

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
for ($i = 1; $i <= $num_pages; $i++) {
  if ($i != $option_page) {
    echo "<a href='qualify_search.php?option_page=$i&property_type=$_REQUEST[property_type]&region_id=$_REQUEST[region_id]&country_id=$_REQUEST[country_id]&state_id=$_REQUEST[state_id]&minprice=$_REQUEST[minprice]&maxprice=$_REQUEST[maxprice]' class='smalltext'> $i</a> | ";
  } else {
    echo '<b><font color=#0C0C7E>' . $i . '</font></b> | ';
  }
}
for ($i = 1; $i <= $num_pages; $i++) {
  if ($i != $option_page) {
    echo "<a href='qualify_search.php?option_page=$i&property_type=$_REQUEST[property_type]&region_id=$_REQUEST[region_id]&country_id=$_REQUEST[country_id]&state_id=$_REQUEST[state_id]&minprice=$_REQUEST[minprice]&maxprice=$_REQUEST[maxprice]' class='smalltext'> $i</a> | ";
  } else {
    echo '<b><font color=#0C0C7E>' . $i . '</font></b> | ';
  }
}

The loop needs to be changed to do something along the lines of

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$start = $option_page - 5;
$end = $option_page + 5;
for( $i = $start; $i < $end; $i++) {
 <your code here>
}
$start = $option_page - 5;
$end = $option_page + 5;
for( $i = $start; $i < $end; $i++) {
 <your code here>
}

That will limit it to 10, however, you should add in your own checks to make sure it doesn't go below 0 or past the final page.

•

Join Date: Oct 2006

Posts: 232

Reputation: Rhyan is an unknown quantity at this point

Solved Threads: 24

Rhyan

Offline

Posting Whiz in Training

Re: How to limit page numbers for search results for database website

Nov 13th, 2008

I went on solving your issue, but I just stumbled upon the following:

Your task is to display pages in groups of 10, so e.g. 4500 records will display on 45 pages, instead of showing page numbers from 1 to 45, you want to display the pages in groups of 10. so you will have numbers 1, 2, 3, 4, 5 only.
But in this way you will display only first 10 records from each page group. I mean - if you click on 1 - you will have there only 10 records, corresponding to firts page only. This means that either you should display 100 records per page, or, you have to revise your page numbering requirement.

I think that your numbering should look something like

<< Previous 10 | 11 |12|.... next 10 >>, instead of 1|2|3....>>

Please advise...

" Of all the things I've lost,
I miss my mind the most...."
Mark Twain

•

Join Date: Aug 2008

Posts: 37

Reputation: 123468743867143 is an unknown quantity at this point

Solved Threads: 0

123468743867143 123468743867143 is offline

Offline

Light Poster

Re: How to limit page numbers for search results for database website

Nov 13th, 2008

Impossible to have 100 listings per page.

But not impossible to display groups of 10 pages, 10 listings per page. That will reduce the number of rows of page numbers by ten folds.

Thank you for the idea.

I will try this as soon as my server clears up. I am on a shared Linux and it keeps jamming every time I do lots of changes. A real pain.

Thanks again!

________________________________________
GB d- s+:- a? C--- U P L E !W? N o K- w(+++++) !O? !M !V PS? PE++ !Y !PGP !t- !5 !X !R tv>$ b++ DI !D? G- e++ h(++) r y(+++)