"The approach you are trying to use (LIMIT in the SQL) won't do what you want (the database will return the first N items everytime)."
It was so frustrating to find out the approach I was using for two weeks wasnt even the correct approach, im not entirely sure this is correct though. can you guys help - I have been working on this navigation function to navigate my records 10 records at a time below is the related code. Thank you for your patience.
Code: Select all
<?php
/* Connecting, selecting database */
$link = mysql_connect("****", "***", "******")
or die("Could not connect : " . mysql_error());
echo "";
mysql_select_db("contact_management_system") or die("Could not select database");
$limit = 10;
$query_count = "SELECT count(*) FROM people";
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);
$page = (!empty($_REQUEST['page'])) ? $_REQUEST['page'] : 1;
$limitvalue = $page * $limit - ($limit);
$query = "SELECT * FROM people ORDER BY firstname LIMIT $limitvalue,$limit";
$result = mysql_query($query) or die("Error: " . mysql_error());
if(mysql_num_rows($result) == 0){
echo("Nothing to Display!");
}
// Begin your table outside of the array
echo "<table width="50%" border="0" cellpadding="2" cellspacing="2">
<tr>
<td width="110"><b><small>ID</small></b></td>
<td><b></b></td>
<td><b><small>First Name</small></b></td>
<td><b><small>Surname</small></b></td>
<td><b><small>Organisation</small></b></td>
<td><b><center><small>Role</small></center></b></td>
<td><b><small>Address(1)</small></b></td>
<td><b><small>Address(2)</small></b></td>
<td><b><small>City</small></b></td>
<td><b><small>Post Code</small></b></td>
<td><b><small>Telephone</small></b></td>
<td><b><small>Mobile</small></b></td>
<td><b><small>Fax</small></b></td>
<td><b><small>Last Contact</small></b></td>
<td><b><small>Contact Again</small></b></td>
<td><b><small>Notes</small></b></td>
</tr>";
// Define your colors for the alternating rows
$color1 = "#ADD8E6";
$color2 = "#E0FFFF";
$row_count = 0;
/* Printing results in HTML */
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$person_id = $line["person_id"];
$salutation = $line["salutation"];
$firstname = $line["firstname"];
$surname = $line["surname"];
$organisation = $line["organisation"];
$role = $line["role"];
$address1 = $line["address1"];
$address2 = $line["address2"];
$city = $line["city"];
$postcode = $line["postcode"];
$telephone = $line["telephone"];
$mobile = $line["mobile"];
$fax = $line["fax"];
$dateoflastcontact = $line["dateoflastcontact"];
$datecontactagain = $line["datecontactagain"];
$notes = $line["notes"];
$email = $line["email"];
/* Now we do this small line which is basically going to tell
PHP to alternate the colors between the two colors we defined above. */
$row_color = ($row_count % 2) ? $color1 : $color2;
// Echo your table row and table data that you want to be looped over and over here.
echo "<tr>
<td width="20" bgcolor="$row_color" nowrap><a href ="Upeople.html">$person_id
</td>
<td bgcolor="$row_color">$salutation </td>
<td width="110" bgcolor="$row_color" nowrap><a href="mailto: $email">$firstname
</td>
<td width="100" bgcolor="$row_color" nowrap><a href="mailto: $email">$surname
</td>
<td width="100" bgcolor="$row_color" nowrap>$organisation
</td>
<td bgcolor="$row_color">$role</td>
<td width="100" bgcolor="$row_color" nowrap>$address1
</td>
<td width="100" bgcolor="$row_color" nowrap>$address2
</td>
<td bgcolor="$row_color">$city</td>
<td width="110" bgcolor="$row_color" nowrap>$postcode
</td>
<td bgcolor="$row_color">$telephone</td>
<td bgcolor="$row_color">$mobile</td>
<td bgcolor="$row_color">$fax</td>
<td width="100" bgcolor="$row_color" nowrap>$dateoflastcontact
</td>
<td width="100" bgcolor="$row_color" nowrap>$datecontactagain
</td>
<td width="300" bgcolor="$row_color" nowrap>$notes
</td>
</tr>";
// Add 1 to the row count
$row_count++;
}
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href="$PHP_SELF?s=$prevs"><<
Prev 10</a>  ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href="$PHP_SELF?s=$news">Next 10 >></a>";
}
$a = $s + ($limit);
if ($a > $numrows) { $a = $numrows; }
$b = $s + 1;
echo "<p>Showing results $b to $a of $numrows</p>";
mysql_free_result($result);
/* Free resultset */
mysql_free_result($result);
/* Closing connection */
mysql_close($link);
?>
</table>
?>