How to display MySQL query result in multipage? 3

[samansk]

and how to make choices for user to select for display 10 15 or 20 rows per page like in this forum.?

 

[bluetac]

this code will give you an idea. its a bit rough and ready

##########################################################
#setup some variables
##########################################################
$cfgHost = "localhost";
$cfgUser = "someone";
$cfgPassword = "somepass";
$cfgDb = "databasename";

##########################################################
#setup database connection functions
##########################################################
function db_connect() {
global $cfgHost; $cfgUser, $cfgDb, $cfgPassword;
$db = mysql_connect($cfgHost, $cfgUser, $cfgPassword);
mysql_select_db($cfgDb, $db);
return $db;
}
##########################################################
#setup sql query functions
##########################################################

function query_database($querystring = "&quot {

global $db;

$db = db_connect();
$result = mysql_query($querystring) or die("FAILED:".mysql_error()."for".$querystring."\n"

return $result;

}

##########################################################
#start the page
##########################################################

#number of stories per page
$stories_per_page=10;

#how many stories to get at a time
$limit = $stories_per_page;

#set the offset to 0 if not set yet
#otherwise this should be the page number multiplied by
#stories_per_page minus the stories actually on the page

if (!$page):
$offset = 0;
else :
$offset = (($stories_per_page * $page)-$stories_per_page);
endif;

#set up the mysql strings
#$sqlstr retrieves all the rows in the database, using the
#offset and limit functionality to retrieve only certain
#rows. $sqlstr_wo_limits will be used to count the number
#total rows later on, to work out page numbers

$sqlstr = "SELECT column1, column2 FROM yourtable";
$sqlstr_wo_limits = $sqlstr;
$sqlstr.= " LIMIT ".$offset.", ".$limit;

#print the records
$result = query_database($sqlstr);

do {

echo $myrow[column1];
echo $myrow[column2];

} while ($myrow = mysql_fetch_array($result));


#calculate the total number of records
$total_records = query_database($sqlstr_wo_limits);
$count = mysql_num_rows($total_records);

#if no page number variable is present, set it to page 1
if(!$page) {$page=1;}

#work out the offset needed, based on page number
$offset = ($page-1)*$stories_per_page;

#work out how many pages we need to link to
$page_count = ($count-($count%$stories_per_page)) / $stories_per_page + 1;

#set the next page link
$nextpage=$page+1;

#print links to each page
$i = 1;
$output_string.="Page : ";
while ($i <= $page_count) {

if($i != $page)

{

$output_string.=&quot;<a href=\&quot;$PHP_SELF?page=$i\&quot;>$i</a>\n&quot;;

} else {$output_string.=&quot;<b>$i</b>&quot;;}

$i++;
}

# print the &quot;next page&quot; button

if ($page < $page_count) {
$output_string.=&quot;<a href=\&quot;$PHP_SELF?page=$nextpage\&quot;>&quot;;
$output_string.=&quot;Next $stories_per_page record(s)...</a>&quot;;
} else {
$output_string.=&quot;No more records to display!&quot;;
}

Andres Jugnarain
Wireless Editor

http://www.thetuckshop.com

Andres Jugnarain
Wireless Editor

http://www.thetuckshop.com

 

[hmerrill]

Very nice! This topic comes up pretty often - could you make a FAQ for this Andres?
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com

 

[samansk]

Thanks bluetac.