Returning x results per page [MySQL & php]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Returning x results per page [MySQL & php]

Post by mikebr »

I have a script that returns all the results of a search to a page but would rather return 5 results to a page and then have a "Previous" and "Next" links or even better "[1] [2] [3] [4] NEXT" type links on the page but after finding a couple of samples of how to do this I find I am even more confused (one example was fairly complicated and the other was partly in another language and seemed to be missing the bits where they get the results out of the query and into the link)
Does anyone know where there are any examples or tutorials on creating these types of links? "I found a couple after seaching way back on the pages here but the web sites they point to don't appear to have these any more" I can get all the results for the search as shown below the problem I have is making the connection between the results and linking that to pages that return them five at a time.

Code: Select all

<?php

$sql=$query; // 2 TABLE JOIN QUERY 

$mysql_rental_result=mysql_query($sql,$connection);
$rent_num_rows=@mysql_num_rows($mysql_rental_result);

if ( $rent_num_rows == 0 ) {  // ----------------------------- // open 11

echo "<ALIGN=CENTER><B><FONT FACE="Verdana,Arial"><SPAN CLASS="style30">There are no results for your search.</SPAN></FONT></B>";

} else {  // ----------------------------------------------------- // else 11

echo "<ALIGN=CENTER><B><FONT FACE="Verdana,Arial"><SPAN CLASS="style30">Your search has returned ї $rent_num_rows ] $result_text.</B>$order_str</SPAN></FONT>";

$num = 0;

# - LOOP THE RESULTS

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

$num == $num++;

# - GET THE USER INFORMATION FROM THE PROPERTY TABLE
$sr_ID = $rowї"ID"];
// - LOAD THE REST OF THE PROPERTY VERIABLES

# - GET THE USER INFORMATION FROM THE USER TABLE

$sc_phone = $rowї"phone"];
// - LOAD THE REST OF THE USER VARIABLES

include "search_rental_table.php"; // LOAD THE TABLE 

}
}  // ----------------------------------------------- // close 11
?>
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

http://www.mysql.com/doc/en/SELECT.html[quote]SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]][/quote]now you may set a variable to the number of records per page and transmit the page number in each link, e.g.

Code: Select all

$nRowsPerPage = 5;
$nStartRow = (int)$_POSTї'page'] * $nRowsPerPage;
$query = 'SELECT ... JOIN ... WHERE ... LIMIT ' . $nStartRow . ',' . ($nStartRow + $nRowsPerPage);
also note http://www.mysql.com/doc/en/Counting_rows.html
Post Reply