sql for finite number of records

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rkalexander
Forum Newbie
Posts: 10
Joined: Tue May 30, 2006 9:30 am

sql for finite number of records

Post by rkalexander »

I currently have a table loop populated by a query from my database. I want to be able to determine the number of records to query and then stop. As it is now it queries and loops through the whole database table.

What would I do, if I wanted to just pull the the first 20 records or the last 20 records from the table?

This is what I have now:

Code: Select all

<?php 
$sql="SELECT * FROM records ORDER BY matchDate ASC";
$result=mysql_query($sql,$DB);
?>


<table width="480" border="0" align="center" class="hostileText">
  <tr bgcolor="#000000"> 
    <td width="20%"><div align="center"><u><font color="#FFFFFF" >Date</font></u></div></td>
    <td width="20%"><div align="center"><u><font color="#FFFFFF" >Map</font></u></div></td>
    <td width="20%"><div align="center"><u><font color="#FFFFFF" >Opponent</font></u></div></td>
	<td width="20%"><div align="center"><u><font color="#FFFFFF" >Score</font></u></div></td>
	<td width="20%"><div align="center"><u><font color="#FFFFFF" >Stats</font></u></div></td>
  </tr>
  <?php
  $i=0;
while($row=mysql_fetch_assoc($result)){ 
$bgcolor=($i%2==0)?'#333333':'#666633';
$i++;

?>
  <tr> 
    <td bgcolor="<?php echo $bgcolor; ?>"><div align="center"><font color="#FFFFFF"><?php echo $row['matchDate']; ?></font></div></td>
    <td bgcolor="<?php echo $bgcolor; ?>"><div align="center"><font color="#FFFFFF"><?php echo $row['matchMap']; ?></font></div></td>
    <td bgcolor="<?php echo $bgcolor; ?>"><div align="center"><font color="#FFFFFF"><?php echo $row['matchOpp']; ?></font></div></td>
	<td bgcolor="<?php echo $bgcolor; ?>"><div align="center"><font color="#FFFFFF"><?php echo $row['matchScore']; ?></font></div></td>
	<td bgcolor="<?php echo $bgcolor; ?>"><a href="../<?php echo $row['matchStats']; ?>" target="_blank"> 
      <div align="center">view stats</div></td>
	  
  </tr>
  <?php }
?>
</table>
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Take a look at the LIMIT keyword. Something like this:

Code: Select all

$number_of_rows = 20;
$starting_at_row = 0;
$sql="SELECT * FROM records ORDER BY matchDate ASC LIMIT $number_of_rows OFFSET $starting_at_row";
(#10850)
Post Reply