...

View Full Version : limiting 50 records per page & links to go subsequent pages



cheryl
06-09-2006, 12:44 PM
i have a table which populates the data.

i would like to show 50 records per page, and have buttons or links to click on to view the next 50 records and so on.

how should i go about doing it??

lavinpj1
06-09-2006, 01:12 PM
you'd use the LIMIT of mysql. You would simply have a $_GET variable of the page number, e.g. ?page=3. You would get this out and multiply it by 50 and -50, e.g. $limitStart = $_GET['page'] - 50;

You would then pass this into your SQL query as the start point...

$result = mysql_query("SELECT `field1`, `field2` FROM `table` WHERE `whatever`='whatever' ORDER BY `whatever` DESC LIMIT $limitStart,50");

This will select 50 records of your chosen fields from your table starting from $limitStart.

~Phil~

degsy
06-09-2006, 03:06 PM
http://www.codingforums.com/showthread.php?t=74614

cheryl
06-09-2006, 05:43 PM
you'd use the LIMIT of mysql. You would simply have a $_GET variable of the page number, e.g. ?page=3. You would get this out and multiply it by 50 and -50, e.g. $limitStart = $_GET['page'] - 50;

You would then pass this into your SQL query as the start point...

$result = mysql_query("SELECT `field1`, `field2` FROM `table` WHERE `whatever`='whatever' ORDER BY `whatever` DESC LIMIT $limitStart,50");

This will select 50 records of your chosen fields from your table starting from $limitStart.

~Phil~

attached is the file that i want to display 50 records per page and to have subsquent pages to view the next 50 records and so on.

will someone please assist in guiding me how to dump the codes to set the limit??

thank you

edited

i've tried, but it gives me an syntax error about LOGICAL something something...

what is the problem? the class files should be able to work, right??

lansing
06-10-2006, 12:10 PM
Read this thread...I posted a code & someone made it a little better, but it is Pagination & this is what you need.

http://www.codingforums.com/showthread.php?t=88676

cheryl
06-11-2006, 09:23 AM
Read this thread...I posted a code & someone made it a little better, but it is Pagination & this is what you need.

http://www.codingforums.com/showthread.php?t=88676



er okie... after which line of code should i dump the codes at then??

i shall put the full display page here. please assist in how do i modify and add the pagination in.

thanks



<?php
session_save_path("../servertemp");
session_start();
if(empty($_SESSION[login]))
//check if the user has logged in, if not redirect the user to login page
{
$_SESSION[error]="Access Denied! You need to login first.";
header("Location: ../login.php");
}
else
{
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Participant Listing</title>
<style type="text/css">
<!--
.style1 {
font-family: Tahoma, sans-serif;
font-size: small;
}
-->
</style>
</head>

<body>
<?php $eventName = $_POST["eventName"]; ?>
<form name="listParticipants" method="get" action="<? $PHP_SELF?>">
<table width="469" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="469"><span class="style1">Show:</span> <select name="status" id="status"> View :

<option <?php if($status =="All") {print "selected";} ?> value="All">All</option>
<option <?php if($status =="Approved") {print "selected";} ?> value="Approved">Approved</option>
<option <?php if($status =="Pending") {print "selected";} ?> value="Pending">Pending</option>
<option <?php if($status =="Unapproved") {print "selected";} ?> value="Unapproved">Unapproved</option>
</select>
<span class="style1">Sort by:</span> <select name="sort" id="select2">
<option <?php if($sort =="partName") {print "selected";} ?> value="partName">Participant's Name</option>
<option <?php if($sort =="partUID") {print "selected";} ?> value="partUID">NRIC/Passport No</option>
<option <?php if($sort =="partMemType") {print "selected";} ?> value="partMemType">Membership Type</option>
<option <?php if($sort =="TotalAmtDue") {print "selected";} ?> value="TotalAmtDue">Total Amount Payable</option>
<option <?php if($sort =="paymentMode") {print "selected";} ?> value="paymentMode">Mode Of Payment</option>
<option <?php if($sort =="partRegApprove") {print "selected";} ?> value="partRegApprove">Status</option>

<option <?php if($sort =="partRegTimeStamp") {print "selected";} ?> value="partRegTimeStamp">Date of Registration</option>
</select>
<input type="submit" name="filter" value="Filter"> </td>
</tr>
</table>
<p>
<?php
include("../lib/db_connect.php");
include("common.php");
$link = @mysql_connect("localhost", $user, $pass);

if (!$link)
die ("Couldn't connect to MySQL");
mysql_select_db($db, $link)
or die ("Couldn't open $db".mysql_error());

$sql="SELECT * from event where eventCode='$eventCode'";
$result=mysql_query($sql)
or die ("Couldn't execute query.".mysql_error());
$num=mysql_num_rows($result);
while($row = mysql_fetch_array($result))
{
$eventName = $row["mainTitle"];
}
//sql statement for event listing
$status = $_GET["status"];
$sort = $_GET["sort"];

if(!empty($status))
{
if($status == "All")
{
if(!empty($sort))
{
$selectsql="select * from Temp_Participants ORDER BY '$sort'";
}
else
{
$selectsql="select * from Temp_Participants";
}
}
else
{
if(!empty($sort))
{
$selectsql="select * from Temp_Participants WHERE partRegApprove LIKE '$status' ORDER BY '$sort'";
}
else
{
$selectsql="select * from Temp_Participants WHERE partRegApprove LIKE '$status'";
}
}
}
else
{
$selectsql="select * from Temp_Participants ORDER BY partRegTimeStamp DESC";
}

$selectlist_result=mysql_query($selectsql)
or die ("Couldn't execute query.".mysql_error()."".$selectsql);
$num=mysql_num_rows($selectlist_result);

/*include_once("class/pager.class.php");

$selectsql=str_replace('*', 'COUNT(1)', $selectsql);
$max=mysql_query($selectsql);
$num=50;
$pager = new pager( $max, $num, @$_GET['_p']);

$selectlist_result=mysql_query($selectsql.' LIMIT '.$pager->get_limit());
or die ("Couldn't execute query.".mysql_error()."".$selectsql);
/*if it worked ...*/
//echo $pager->get_title('Page {CURRENT} of {MAX}<br />Results {FROM} to {TO} of {TOTAL}');

print "<font face=Tahoma,sans-serif size=1>";
print "<center><strong><h4>".$eventName."</h4></strong></center>";
print "<center><table width=825 border=1 bordercolor=black cellspacing=0 cellpadding=5>";
print "<tr bgcolor=orange>";
print "<td colspan=1 align=left width=200><b>Participant's Name</b></td>";
print "<td colspan=1 align=left width=60><b>NRIC/Passport no</b></td>";
print "<td colspan=1 align=left width=50><b>Membership</b></td>";
print "<td colspan=1 align=left width=60><b>Total Amount Payable</b></td>";
print "<td colspan=1 align=left width=50><b>Payment Mode</b></td>";
print "<td colspan=1 align=left width=20><b>Registration Approval</b></td>";
print "<td colspan=1 align=left width=20><b>Registration Timestamp</b></td>";
print "<td colspan=3 width=100><b>Action</b></td>";
print "</tr>";

while ($cl_row = mysql_fetch_array($selectlist_result))
{
$_SESSION[eventCode]=$cl_row['eventCode'];
echo "<tr>";
echo "<td colspan=1 align=left width=90>".$cl_row['partName']."</td>";
echo "<td colspan=1 align=left width=200>".$cl_row['partUID']."</td>";
echo "<td colspan=1 align=left width=200>".$cl_row['partMemType']."</td>";
echo "<td colspan=1 align=left width=200>$".$cl_row['TotalAmtDue']."</td>";
echo "<td colspan=1 align=left width=60>".$cl_row['paymentMode']."</td>";
echo "<td colspan=1 align=left width=60>".$cl_row['partRegApprove']."</td>";
$friendlysdate = date('j F Y H:i:s', strtotime($cl_row['partRegTimeStamp']));
echo "<td colspan=1 align=left width=60>".$friendlysdate."</td>";
echo "<td colspan=1 width=30><a href='Partsummary.php?partCode=" .$cl_row['partCode']."'>View</a></td>";
echo "<td colspan=1 width=30><a href='editparticipants.php?partCode=" .$cl_row['partCode']."'>Edit</a></td>";
echo "<td colspan=1 width=40><a href='confirmdeleteParticipant.php?partCode=" .$cl_row['partCode']."'>Delete</a></td>";
echo "</tr>";
}

print "</table></center></font>";

?>
</p>
</form>
</body>
<?php
}
?>
</html>

goughy000
06-11-2006, 11:02 AM
why dont u show us how you tried.. someone will then help u sort out your mistakes AND you will learn why the way you tried is incorect for further coding.

Also.. php code should go in [PHP ] and [/PHP ] tags.. not [CODE ] and [/CODE ] there is a sticky on this forum noting this

cheryl
06-11-2006, 02:33 PM
hi... i shall post part by part of the code.



//this is the statements where the dropdown list filters the records
<?
$status = $_GET["status"];
$sort = $_GET["sort"];

if(!empty($status))
{
if($status == "All")
{
if(!empty($sort))
{
$selectsql="select * from Temp_Participants ORDER BY '$sort'";
}
else
{
$selectsql="select * from Temp_Participants";
}
}
else
{
if(!empty($sort))
{
$selectsql="select * from Temp_Participants WHERE partRegApprove LIKE '$status' ORDER BY '$sort'";
}
else
{
$selectsql="select * from Temp_Participants WHERE partRegApprove LIKE '$status'";
}
}
}
else
{
$selectsql="select * from Temp_Participants ORDER BY partRegTimeStamp DESC";
}

$selectlist_result=mysql_query($selectsql)
or die ("Couldn't execute query.".mysql_error()."".$selectsql);
$num=mysql_num_rows($selectlist_result);




//here is where the records are printed
print "<font face=Tahoma,sans-serif size=1>";
print "<center><strong><h4>".$eventName."</h4></strong></center>";
print "<center><table width=825 border=1 bordercolor=black cellspacing=0 cellpadding=5>";
print "<tr bgcolor=orange>";
print "<td colspan=1 align=left width=200><b>Participant's Name</b></td>";
print "<td colspan=1 align=left width=60><b>NRIC/Passport no</b></td>";
print "<td colspan=1 align=left width=50><b>Membership</b></td>";
print "<td colspan=1 align=left width=60><b>Total Amount Payable</b></td>";
print "<td colspan=1 align=left width=50><b>Payment Mode</b></td>";
print "<td colspan=1 align=left width=20><b>Registration Approval</b></td>";
print "<td colspan=1 align=left width=20><b>Registration Timestamp</b></td>";
print "<td colspan=3 width=100><b>Action</b></td>";
print "</tr>";

while ($cl_row = mysql_fetch_array($selectlist_result))
{
$_SESSION[eventCode]=$cl_row['eventCode'];
echo "<tr>";
echo "<td colspan=1 align=left width=90>".$cl_row['partName']."</td>";
echo "<td colspan=1 align=left width=200>".$cl_row['partUID']."</td>";
echo "<td colspan=1 align=left width=200>".$cl_row['partMemType']."</td>";
echo "<td colspan=1 align=left width=200>$".$cl_row['TotalAmtDue']."</td>";
echo "<td colspan=1 align=left width=60>".$cl_row['paymentMode']."</td>";
echo "<td colspan=1 align=left width=60>".$cl_row['partRegApprove']."</td>";
$friendlysdate = date('j F Y H:i:s', strtotime($cl_row['partRegTimeStamp']));
echo "<td colspan=1 align=left width=60>".$friendlysdate."</td>";
echo "<td colspan=1 width=30><a href='Partsummary.php?partCode=" .$cl_row['partCode']."'>View</a></td>";
echo "<td colspan=1 width=30><a href='editparticipants.php?partCode=" .$cl_row['partCode']."'>Edit</a></td>";
echo "<td colspan=1 width=40><a href='confirmdeleteParticipant.php?partCode=" .$cl_row['partCode']."'>Delete</a></td>";
echo "</tr>";
}

print "</table></center></font>";


should i be putting the pagination codes like this??

i placed the codes before the table is printed.

like this:



if(isset($_GET['page']))
{
$page = $_GET['page'];
$page2 = $page;
$page2--;
$show = 5; // Number of post or records per page
$skip = $page2*$show;
}
else
{
$page = 1;
$page2 = $page;
$page2--;
$show = 5; // Number of post or records per page if the $_GET['page'] isn't set!!
$skip = $page2*$show;
}

$sql = mysql_query("SELECT * FROM table ORDER BY `column_name` ASC LIMIT $skip,$show");
$sql_all = mysql_query("SELECT * FROM table");
$rows = @mysql_num_rows($sql_all);
$maxpage = ceil($rows/$show);
$bgcolor = "#FFFFFF";
while($row = @mysql_fetch_assoc($sql))
{
$colum_1 = $row["colum_1"];
$colum_2 = $row["colum_2"];
$colum_3 = $row['colum_3'];
$colum_4 = $row["colum_4"];
$colum_5 = $row["colum_5"];
if ($bgcolor == "#FFFFFF"){
$bgcolor = "#CACACA";
}else{
$bgcolor = "#FFFFFF";
}
echo'
<tr bgcolor="'.$bgcolor.'">
<td align="center">' . $colum_1 . '</td>
<td align="center">' . $colum_2 . '</td>
<td align="center">' . $colum_3 . '</td>
<td align="center">' . $colum_4 . '</td>
<td align="center">' . $colum_5 . '</td>
</tr>';
}

?>
Page:
<?
while($x <= $maxpage)
{
echo '<a href=" ' . $_SERVER['PHP_SELF'] . 'page=' . $x . '">' . $x . '</a> ';
$x++;
}
}

//here is where the records are printed
print "<font face=Tahoma,sans-serif size=1>";
print "<center><strong><h4>".$eventName."</h4></strong></center>";
print "<center><table width=825 border=1 bordercolor=black cellspacing=0 cellpadding=5>";
print "<tr bgcolor=orange>";
print "<td colspan=1 align=left width=200><b>Participant's Name</b></td>";
print "<td colspan=1 align=left width=60><b>NRIC/Passport no</b></td>";
print "<td colspan=1 align=left width=50><b>Membership</b></td>";
print "<td colspan=1 align=left width=60><b>Total Amount Payable</b></td>";
print "<td colspan=1 align=left width=50><b>Payment Mode</b></td>";
print "<td colspan=1 align=left width=20><b>Registration Approval</b></td>";
print "<td colspan=1 align=left width=20><b>Registration Timestamp</b></td>";
print "<td colspan=3 width=100><b>Action</b></td>";
print "</tr>";

while ($cl_row = mysql_fetch_array($selectlist_result))
{
$_SESSION[eventCode]=$cl_row['eventCode'];
echo "<tr>";
echo "<td colspan=1 align=left width=90>".$cl_row['partName']."</td>";
echo "<td colspan=1 align=left width=200>".$cl_row['partUID']."</td>";
echo "<td colspan=1 align=left width=200>".$cl_row['partMemType']."</td>";
echo "<td colspan=1 align=left width=200>$".$cl_row['TotalAmtDue']."</td>";
echo "<td colspan=1 align=left width=60>".$cl_row['paymentMode']."</td>";
echo "<td colspan=1 align=left width=60>".$cl_row['partRegApprove']."</td>";
$friendlysdate = date('j F Y H:i:s', strtotime($cl_row['partRegTimeStamp']));
echo "<td colspan=1 align=left width=60>".$friendlysdate."</td>";
echo "<td colspan=1 width=30><a href='Partsummary.php?partCode=" .$cl_row['partCode']."'>View</a></td>";
echo "<td colspan=1 width=30><a href='editparticipants.php?partCode=" .$cl_row['partCode']."'>Edit</a></td>";
echo "<td colspan=1 width=40><a href='confirmdeleteParticipant.php?partCode=" .$cl_row['partCode']."'>Delete</a></td>";
echo "</tr>";
}

print "</table></center></font>";
?>



do i have to change the sql statements in the pagination codes??

this is needed rather urgently. i appreciate ur help.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum