Page 1 of 1

sql limit help

Posted: Mon Sep 26, 2005 7:22 am
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

Posted: Mon Sep 26, 2005 7:39 am
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

sql limit help

Posted: Mon Sep 26, 2005 7:47 am
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!

Posted: Mon Sep 26, 2005 7:51 am
by shiznatix
you want pagintation? search the forum then. otherwise please make your question a little bit clearer

Posted: Mon Sep 26, 2005 9:24 am
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...