How to limit records/page?

[Dan06]

I'd like to limit the number of records displayed per page. With the following code I'm able to get all the records.

 

<?php
$query1 = "SELECT FirstName from registration";
$query2 = "SELECT LastName from registration";
$firstName = mysql_query($query1, $conn);
$lastName = mysql_query($query2, $conn);
$row_firstName = mysql_fetch_assoc($firstName);
$row_lastName = mysql_fetch_assoc($lastName);
<table border="1" bordercolor="#00CC33">
?>
            <?php do { ?>
            <tr><td>
            <?php echo $row_firstName['FirstName']; ?>
            <?php echo $row_lastName['LastName']; ?>
            </td></tr>
         <?php } while ($row_firstName = mysql_fetch_assoc($firstName)); ?> 
            </table>                 

 

Can someone tell me how I can limit the amount of displayed records, say maybe 10 per page? Thanks.

[fri3ndly]

$query1 = "SELECT FirstName from registration LIMIT 1";

$query2 = "SELECT LastName from registration LIMIT 10";

 

 

Just change the end limit to the amount you require

[flyhoney]

To get a specific range of records:

 

SELECT * FROM table LIMIT 0, 25
SELECT * FROM table LIMIT 100, 110
etc...

[fri3ndly]

By the way I just noticed, why are you doing two queries?

 

Just do it like this:

 

<?php
$query = mysql_query("SELECT FirstName,LastName from registration LIMIT 5", $conn);

$row = mysql_fetch_array($query);

$firstname = $row['FirstName']; 
$lastname = $row['LastName']; 
?>

[The Little Guy]

To get a specific range of records:

 

SELECT * FROM table LIMIT 0, 25
SELECT * FROM table LIMIT 100, 110
etc...

 

First number is the record start number, second number is quantity of rows.

[Dan06]

Thank you all for the help. I failed to make clear one aspect of the limiting/displaying in my original question/post. Not only do I want to limit the records to a particular number but I want to have the option to cycle through the record set. For example, let's say I have 100 records. I'd like to display 10 of them at a time and then using "Previous" or "Next" move forward or back to the next 10 appropriate set of records. How can I do that?

 

Oh and as for the reason for two queries - query1 & query2, I did that because I was fiddling around with the code and trying a few different things. There was no purpose to it.

[premiso]

You want to look into paging. There should be a ton of examples on the forum and a googling of php mysql paging  will pull up tutorials and examples.

[flyhoney]

Yeah, try googling 'paging' or 'pagination'.  It's a very common PHP task and there are loads of examples on the tubez.

[Dan06]

After doing some research on paging/pagination, I've put together some code. Unfortunately, the code doesn't work as intended. The code correctly shows "First," "Previous," "Next," "Last" - but instead of showing the assigned limit, shows all the records... By looking at my code can anyone let me know where I went wrong? Thanks.

 

<?php
$query = "SELECT FirstName, LastName FROM registration " . $limit;
$result = mysql_query($query, $conn);
$name = mysql_fetch_assoc($result);

if (isset($_GET['pageNum'])) {
   $pageNum = $_GET['pageNum'];
} else {
   $pageNum = 1;
} 

$resultRows = mysql_num_rows($result);
$rowsPerPage = 2;
$lastPage = ceil($resultRows/$rowsPerPage);

if ($pageNum > $lastPage) { $pageNum = $lastPage; } else if ($pageNum < 1) { $pageNum = 1; }

$limit = "LIMIT " . ($pageNum - 1) * $rowsPerPage . ", " . $rowsPerPage;

?>
<?php do { ?>
            <br />
               <?php echo $name['FirstName'] . " " . $name['LastName']; ?>
            <div class="borderLine"><p/></div>
            <?php } while ($name = mysql_fetch_assoc($result)); ?>
            
            <?php
            
if ($pageNum > 1) {
   $prevPage = $pageNum-1; 
   echo " <a href='{$_SERVER['PHP_SELF']}?pageNum=1'>FIRST</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageNum=$prevPage'>PREV</a> ";
}

echo " ( Page $pageNum of $lastPage ) ";

if ($pageNum < $lastPage) {
   $nextPage = $pageNum+1; 
   echo " <a href='{$_SERVER['PHP_SELF']}?pageNum=$nextPage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageNum=$lastPage'>LAST</a> ";
}
?> 

[premiso]

Order of operations:

 

<?php
if (isset($_GET['pageNum'])) {
   $pageNum = $_GET['pageNum'];
} else {
   $pageNum = 1;
} 

$resultRows = mysql_num_rows($result);
$rowsPerPage = 2;
$lastPage = ceil($resultRows/$rowsPerPage);

if ($pageNum > $lastPage) { $pageNum = $lastPage; } else if ($pageNum < 1) { $pageNum = 1; }

$limit = "LIMIT " . ($pageNum - 1) * $rowsPerPage . ", " . $rowsPerPage;

$query = "SELECT FirstName, LastName FROM registration " . $limit;
$result = mysql_query($query, $conn);
$name = mysql_fetch_assoc($result);


?>
<?php do { ?>
            <br />
               <?php echo $name['FirstName'] . " " . $name['LastName']; ?>
            <div class="borderLine"><p/></div>
            <?php } while ($name = mysql_fetch_assoc($result)); ?>
            
            <?php
            
if ($pageNum > 1) {
   $prevPage = $pageNum-1; 
   echo " <a href='{$_SERVER['PHP_SELF']}?pageNum=1'>FIRST</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageNum=$prevPage'>PREV</a> ";
}

echo " ( Page $pageNum of $lastPage ) ";

if ($pageNum < $lastPage) {
   $nextPage = $pageNum+1; 
   echo " <a href='{$_SERVER['PHP_SELF']}?pageNum=$nextPage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageNum=$lastPage'>LAST</a> ";
}
?> 

 

You cannot use limit if it has not been defined yet.

[Dan06]

Wow, do I feel silly... after correcting for order of operations, the code works fine. I can't believe I missed something that basic/obvious. Thanks for the help.