Pagination

PKV · April 15, 2006

I've recently tried to modify my results page (for DB queries) to limit the number of results on each page.
The final scrip is a combination of a script found here:
[a href=\"http://www.phpfreaks.com/tutorials/73/1.php\" target=\"_blank\"]Paging Script[/a]
and one of my own.

I need a results page that states the number of "hits" and limits the amount of returns on a page.

The results page show nothing until I remove the expression "LIMIT $from, $max_results" at the end of the select statement.
The queries are then posted all on one page, but pagination fails (ie. the page number links don't show.)

My SELECT statement is syntexed differently from the origional paging script...... maybe thats where the problem lies...

Origional SELECT statement:
$sql = mysql_query("SELECT * FROM pages LIMIT $from, $max_results");

This may be a simple fix... any insight would be greatly appreciated

[b]Here's the code:[/b]

<html>
<body>
<div align="center">

<?php

$connection=mysql_connect("someserver.com","user","password");
if (!$connection) {
echo "Could not connect to MySql server!";
exit;
}

// Lets select our database

$db=mysql_select_db("somedatabase",$connection);
if (!$db) {
echo "Could not select database";
exit;
}

// If current page number, use it
// if not, set one!
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}

// Define the number of results per page
$max_results = 10;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);
// Lets select our record fields from our database table and check for results

$sql="SELECT prod_type, prod_id, prod_name, prod_qty, prod_cost, acc1, acc2, acc3, acc4, acc5, acc6, Name, phone_num, Your_Province, call_area, Street, City, Province, Postal, Comments, Your_Name, PHORM_FROM, IO_Cost_Center, Company, proceed, order_date, OrderNum, voice, scompany, saddress1, saddress2, saddress3, sattn, sref FROM DEGT WHERE $criteria like \"%$q%\ LIMIT $from, $max_results
order by OrderNum";

$mysql_result=mysql_query($sql,$connection);
$num_rows=mysql_num_rows($mysql_result);

// We have no results

if ($num_rows == 0) {
echo "<table border=0 cellpadding=0 cellspacing=0 width=755 id=table5>
<tr>
<td width=755 align=left><font color=#003366><b> Sorry, we have no records that match your query...</b></font></td>";
exit;
} else {
echo "<table border=0 cellpadding=0 cellspacing=0 width=755 id=table5>
<tr>
<td width=755 align=left><font color=#003366><b> $num_rows record(s) returned...\n</b></font></td></tr></table>";

while ($row=mysql_fetch_array($mysql_result))
{
$Name=$row["Name"];
$phone_num=$row["phone_num"];
$Your_Province=$row["Your_Province"];
$call_area=$row["call_area"];
$Street=$row["Street"];
$City=$row["City"];
$Province=$row["Province"];
$Postal=$row["Postal"];
$Comments=$row["Comments"];
$Your_Name=$row["Your_Name"];
$PHORM_FROM=$row["PHORM_FROM"];
$IO_Cost_Center=$row["IO_Cost_Center"];
$Company=$row["Company"];
$proceed=$row["proceed"];
$order_date=$row["order_date"];
$OrderNum=$row["OrderNum"];
$voice=$row["voice"];
$scompany=$row["scompany"];
$saddress1=$row["saddress1"];
$saddress2=$row["saddress2"];
$saddress3=$row["saddress3"];
$sattn=$row["sattn"];
$sref=$row["sref"];
$prod_type=$row["prod_type"];
$prod_id=$row["prod_id"];
$prod_name=$row["prod_name"];
$prod_qty=$row["prod_qty"];
$prod_cost=$row["prod_cost"];
$acc1=$row["acc1"];
$acc2=$row["acc2"];
$acc3=$row["acc3"];
$acc4=$row["acc4"];
$acc5=$row["acc5"];
$acc6=$row["acc6"];

// We display the results under the correct headings

echo "<table >
<td width=755 height=200 align=center colspan=2>
<table border=1 cellpadding=0 cellspacing=0 width=755 id=table4 height=200 bordercolor=#496378>
<tr>
<td height=19 bgcolor=#E2E5E8 width=755>
<strong><font color=#003366 size=2> Reference#: $OrderNum.....

<.......... alot of table data here. Too long to post..........>

........</tr>
</table></table>
<BR>";

}

}// End else while
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM pages"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

// Build Previous Link
if($page > 1){
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i ";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
}
}

// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";
}

?>

</div>
</body>
</html>

Sign In

Pagination

Recommended Posts

PKV

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information