problems in the use of limit in sql statement

[misslois74]

im not sure if im posting on the right forum section but since im working on php i decided to place my question here im working on applying pagination on one of my page and the data that im supposed to display comes from 3 different tables now my problem is i dont know where im supposed to put the limit since im joining the tables here is my codes:

include "db_settings_config.php";
					
					$records_per_page=10;
				
					(!$_GET['start']) ? $start=0 : $start=$_GET['start'];
					
					$query = "Select count(*) from tbl_user_stamp";
					$result2 = mysql_query($query) or die(mysql_error());
					
					$rows = mysql_fetch_row($result2);
					$total_records = $row[0];
					
					if(($total_records > 0) && ($start < $total_records))
					{
						
						$result = mysql_query("Select date_format(user_stamp_datetime, '%D %M %Y %h:%i %p') as trans_date, table_users.lname, table_users.fname, table_users.mi,
					                              table_users.empid, table_users.user_level, tbl_transaction_type.transaction_type 
					             	          from tbl_user_stamp, table_users, tbl_transaction_type
											  where table_users.empid=tbl_user_stamp.user_id
										   	  and tbl_transaction_type.transaction_id=tbl_user_stamp.trans_id
											  limit $start, $records_per_page
										   	  order by tbl_user_stamp.user_stamp_datetime DESC") or die(mysql_error());
						$result1 = mysql_num_rows($result);
						if($result1 == 0)
						{  ?>
								<table><tr><td>NO RECORDS TO DISPLAY.</td></tr></table>
		      		<?php } 
						else
						{  ?>
							  <center><table border="1" cellspacing="0" cellpadding="2" width="610">
							  <th>Employee ID</th>
							  <th>Username</th>
							  <th>User Level</th>
							  <th>Transaction Type</th>
							  <th>Date/Time Stamp</th>
							 							  		
					   <?php while($row = mysql_fetch_array($result))
							 {  ?>
								
									<tr><td><?php echo $row[empid] ?></td>
									<td><?php echo $row[lname]. ', ' . $row[fname] . ' ' . $row[mi] ?></td>
									<td><?php echo $row[user_level] ?></td>
									<td><?php echo $row[transaction_type] ?></td>
									<td><?php echo $row[trans_date] ?></td>
						<?php } ?>
						   </table></center>
				   <?php }	?>
				   
			   <?php if($start >= $records_per_page)
			         { 
					 	echo "<a href=" .$_SERVER['PHP_SELF']."?start=".($start-$records_per_page).">Previous Page</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
					  }
					  
					  if($start + $records_per_page < $total_records && $start >=0)
					  {
					    echo "<a href=" .$_SERVER['PHP_SELF']."?start=".($start+$records_per_page).">Next Page</a>";
					   }
				} ?>

when im using the above code its not displaying the data on the table
thanks!

 

[jpadie]

that's a mysql question. but the answer is that you are limiting the whole result set, so it goes at the end. typically after the ORDER.

 

[misslois74]

so that means i just have to put my LIMIT clause after the ORDER clause am i right?

 

[jpadie]

yes