Jump to content

Pagination


PKV

Recommended Posts

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>
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.