sql limit help
Posted: Mon Sep 26, 2005 7:22 am
Does anyone have an example of how to use the sql statement limit?I need one with PREV and NEXT links that work.Thank you
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
Code: Select all
SELECT
a_field_name
FROM
a_table
WHERE
somthing = somthing_else
LIMIT 5Code: Select all
<?
$sql = "SELECT COUNT(*) FROM city";
$numrecord = mysql_query($sql);
$numrecords = mysql_fetch_array($numrecord);
//this value determines how many threads display on a page.
$intRecsPerPage=20;
if($_POST["intpage"]=="")
{
$intpage=1;
}
if($_POST["nav"]=="first")
{
$intpage = 1;
}
if($_POST["nav"]=="prev")
{
$intpage = intval($_POST["intpage"])-1;
if(intpage < 1)
{
$intpage = 1;
}
}
if($_POST["nav"]=="next")
{
$intpage = intval($_POST["intpage"])+1;
if($intpage * $intRecsPerPage > $numrecords[0])
{
$intpage=intval($numrecords[0]/$intRecsPerPage);
if(intval($numrecords[0]/$intRecsPerPage) != ($numrecords[0]/$intRecsPerPage))
{
$intpage = $intpage + 1;
}
}
}
if($_POST["nav"]=="last")
{
$intpage=intval($numrecords[0]/$intRecsPerPage);
if(intval($numrecords[0]/$intRecsPerPage) != ($numrecords[0]/$intRecsPerPage))
{
$intpage = $intpage + 1;
}
}
$sql = "SELECT * FROM city ORDER BY City_Name ASC LIMIT ".(($intpage-1)*$intRecsPerPage).", ".$intRecsPerPage;
if(!$result = mysql_query($sql))
{
echo mysql_error();
}
?>Code: Select all
<form name="nav" method="post">
<table width="100%"" border="0" align="left" cellpadding="3" cellspacing="1" class"txtblack>
<tr>
<td width="36%">
<tr>
<td height="59" valign="bottom"><input name="add" type="image" class="field" onclick="showRemote('add_city.php','AddPress',640,480);" src="../../../images/icons/cities_add.jpg" alt="Add" width="38" height="38" value="Add">
<br>
<a href="add_city.php" class="rollover2"> Add</a></td>
<td colspan="3" align="right"> <input name="first" type="button" class="field" onClick="document.nav.nav.value='first'; document.nav.submit();" value="<<">
<input name="prev" type="button" class="field" onClick="document.nav.nav.value='prev'; document.nav.submit();" value="<">
<input name="next" type="button" class="field" onClick="document.nav.nav.value='next'; document.nav.submit();" value=">">
<input name="last" type="button" class="field" onClick="document.nav.nav.value='last'; document.nav.submit();" value=">>">
</td>
</tr>
<input type="hidden" name="nav" value="">
<input type="hidden" name="intpage" value="<? echo $intpage; ?>">
<input type="hidden" name="bpress" value="">
<?
$totalpages = intval(($numrecords[0])/$intRecsPerPage);
if(intval($numrecords[0]/$intRecsPerPage) != ($numrecords[0]/$intRecsPerPage))
{
$totalpages = $totalpages + 1;
}
?>
<tr align="left" class="text">
<td colspan="4"> <span class="style5"><span class="style6">Total Cities: <strong><? echo $numrecords[0]; ?></strong></span> </span><br> <hr color="#FFCC00" width="420" size="1">
</td>
</tr>
<tr valign="top" bgcolor="#CCCCCC" class="text">
<td height="16"><span class="style4">CITY </span></td>
<td align="left" valign="top"><span class="style4">VENUE </span></td>
<td valign="top"><span class="style4">EDIT</span></td>
<td valign="top"><span class="style4">VIEW</span></td>
</tr>
// the repetition comes between this and the close bracket after the <tr>
<?
for($x = (($intpage-1) * $intRecsPerPage); $x < (($intpage-1) * $intRecsPerPage) + $intRecsPerPage; $x++)
{
if($x >= $numrecords[0])
{
break;
}
$cityresults = mysql_fetch_array($result);
?>
<tr valign="top" bgcolor="#9FC3D9" class="text" onMouseOver="this.bgColor='#cccccc'" onMouseOut="this.bgColor='#9FC3D9'">
<td height="27"><strong><? echo $cityresults["City_Name"]; ?></strong> , <strong><? echo $cityresults["City_Region"]; ?></strong> , <strong><? echo $cityresults["City_Country"]; ?></strong><br></td>
<td width="6%" valign="top"><a href='edit_city.php?ID=<? echo $cityresults["City_ID"]; ?>'><img src="../../../images/icon_edit.jpg" alt="Edit Details" border="0"></a> </td>
<td width="5%" valign="top"><a href='detail_city.php?ID=<? echo $cityresults["City_ID"]; ?>'><img src="../../../images/icon_view.jpg" alt="View Details" width="22" height="21" border="0"></a></td>
</tr>
<?
}
?>
</table>
</form>