sql limit help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

sql limit help

Post by sebs »

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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

uhhh

Code: Select all

SELECT
  a_field_name
FROM
  a_table
WHERE
  somthing = somthing_else
LIMIT 5
that will only give you 5 results where somthing = somthing_else

the PREV and NEXT is somthing a bit different
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

sql limit help

Post by sebs »

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!
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

you want pagintation? search the forum then. otherwise please make your question a little bit clearer
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post by $var »

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:

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">&nbsp;&nbsp;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>&nbsp;</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...
Post Reply