sql limit help
Moderator: General Moderators
sql limit help
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
- shiznatix
- DevNet Master
- Posts: 2745
- Joined: Tue Dec 28, 2004 5:57 pm
- Location: Tallinn, Estonia
- Contact:
uhhh
that will only give you 5 results where somthing = somthing_else
the PREV and NEXT is somthing a bit different
Code: Select all
SELECT
a_field_name
FROM
a_table
WHERE
somthing = somthing_else
LIMIT 5the PREV and NEXT is somthing a bit different
sql limit help
Well I need that different thing.I need the next results to be in the next page!Please help me.I know how it works,just that it's not working to me!
here is the code that i use for displaying on multiple pages...
it's a bit lengthy... but it works fine.
Put this in the head:
This is your physical form coding:
Just copy and paste this and change your variables...
it's a bit lengthy... but it works fine.
Put this in the head:
Code: 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();
}
?>This is your physical form coding:
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>Just copy and paste this and change your variables...