[SOLVED] Limiting records

affordit · February 3, 2008

Using the code below is there a way to limit the number of records displayed on a page and then start from where I left of and use a link to display the next say 10 so that I am only displaying 10 per page.

$query_content= "SELECT `item`, `heading`, `picture`, `description` FROM `content` order by item";
$result=mysql_query($query_content) or die(mysql_error());
print "<table width='0' align='right'><tr>";
while ($info = mysql_fetch_array($result)) {
print "<td width='200'>";
echo $info['heading'];
echo "<BR>";
echo $info['picture'];
echo "<BR>";
echo $info['description'];
echo"<BR></td>";
}

budimir · February 3, 2008

You could use limit option in your query.

But the thing you are asking for is pagination! So try to lookup at google, or through the forums. There was a lot of discussions about it.

affordit · February 3, 2008

Ok this is what I got but I am getting a blank page can someone see something I am not?

<?php
include("sharons_dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
mysql_select_db(mydatabase) or die( "Unable to select database");

//Count rows
$query = "SELECT count(*) FROM content";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

//Get number of the last page
$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);


//Verify page number is within range
$pageno = (int)$pageno;
if ($pageno < 1) {
   $pageno = 1;
} elseif ($pageno > $lastpage) {
   $pageno = $lastpage;

//Query the DB
$query = "SELECT * FROM content $limit";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);


// process contents of $result
$info = mysql_fetch_array($result)
print "<td width='200'>";
echo $info['heading'];
echo "<BR>";
echo $info['picture'];
echo "<BR>";
echo $info['description'];
echo"<BR></td>";



//Let them know what page of how many pages
echo " ( Page $pageno of $lastpage ) ";

//Provide the links for any following pages
if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
}
?>

budimir · February 3, 2008

OK,

It looks good for now, but I would recomend to put PREVIOUS so you could go back.

affordit · February 3, 2008

OK I forgot to end the elseif statment just before I processed the results but now it will only show 1 record and it shows the link for the next page but when I click on it it shows the same record?

Aureole · February 3, 2008

Replace...

$pageno = (int)$pageno;

if ($pageno < 1) {

$pageno = 1;

} elseif ($pageno > $lastpage) {

$pageno = $lastpage;

...with...

$pageno = ( is_numeric( $_GET['page'] ) ) ? $_GET['pageno'] : 1;

if( $pageno < 1 )

{

$pageno = 1;

}

elseif( $pageno > $lastpage )

{

$pageno = $lastpage;

}

... I think.

EDIT: The code parser messed up, so I had to remove the CODE tags.

affordit · February 3, 2008

ooops forgot the while loop. Now it shows all the records at the same time but still say it has 2 pages? ???

Aureole · February 3, 2008

I made a typo, replace the thing I said with

$pageno = ( is_numeric( $_GET['pageno'] ) ) ? $_GET['pageno'] : 1;
if( $pageno < 1 )
{
    $pageno = 1;
}
elseif( $pageno > $lastpage )
{
    $pageno = $lastpage;
}

If it still doesn't work after that, then I'm not sure.

affordit · February 3, 2008

The part you gave me is working OK the problem is it is displaying 5 records and then displaying the last record 5 more times. It is doing the count right I know because there are 10 records and I have set the $limit to 5.

affordit · February 3, 2008

OK fixed the repeating last record problem but still displaying all records any ideas :-\

john010117 · February 3, 2008

Can you please post the updated code? From what I can tell about the code you've posted, you don't have $limit defined.

affordit · February 3, 2008

<?php

include("sharons_dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
mysql_select_db(mydatabase) or die( "Unable to select database");

//Count rows
$query = "SELECT count(*) FROM content";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

//Get number of the last page
$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);


//Verify page number is within range
$pageno = ( is_numeric( $_GET['page'] ) ) ? $_GET['pageno'] : 1;
if( $pageno < 1 )
{
    $pageno = 1;
}
elseif( $pageno > $lastpage )
{
    $pageno = $lastpage;
}

//Query the DB
$query = "SELECT * FROM content $limit";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);


// process contents of $result
print "<table width='0' align='center'><tr>";
while ($info = mysql_fetch_array($result)) {
print "<td width='200'>";
echo $info['heading'];
echo "<BR>";
echo $info['picture'];
echo "<BR>";
echo $info['description'];
echo"<BR></td></tr>";
}


//Let them know what page of how many pages
echo " ( Page $pageno of $lastpage ) ";

//Provide the links for any following pages
if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
}
?>

affordit · February 3, 2008

Your right how do I go about setting $limit?

Aureole · February 3, 2008

$limit = 'LIMIT ' . ( $pageno - 1 ) * $rows_per_page . ',' . $rows_per_page;

affordit · February 3, 2008

OK I used this but it skips the first 5 records?

if ($rows_per_page > 0) {
   $limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
} else {
   $limit = '';
}

Aureole · February 3, 2008

This probably isn't the type of pagination you're after... but does this help?

<?php

include("sharons_dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
mysql_select_db(mydatabase) or die( "Unable to select database");

//Count rows
$query = "SELECT count(*) FROM content";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$page = ( isset( $_GET['page'] ) && is_numeric( $_GET['page'] ) ) ? $_GET['page'] : 1;

$query = "SELECT count(*) FROM `content`";
$result = mysql_query( $query );
$fetch = mysql_fetch_row( $result );
$numRows = $fetch[0];

$rowsPerPage = ( isset( $_GET['show'] ) && is_numeric( $_GET['show'] && $_GET['show'] >= 1 ) && $_GET['show'] <= 40 ) ? $_GET['show'] : 20;
$lastPage = ceil( $numRows / $rowsPerPage );

$page = (int)$page;
if( $page < 1 )
{
   $page = 1;
}
elseif( $page > $lastPage )
{
   $page = $lastPage;
}

if( $numRows > 0 ) $limit = 'LIMIT ' . ( $page - 1 ) * $rowsPerPage . ',' . $rowsPerPage;
else $limit = '';

//Query the DB
$query = "SELECT * FROM content $limit";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);


// process contents of $result
print "<table width='0' align='center'><tr>";
while ($info = mysql_fetch_array($result)) {
print "<td width='200'>";
echo $info['heading'];
echo "<BR>";
echo $info['picture'];
echo "<BR>";
echo $info['description'];
echo"<BR></td></tr>";
}

$lastPage = (int)$lastPage;

if ( $page == 1 )
{
echo( 'FIRST PREVIOUS' );
}
else
{
$prevPage = $page - 1;
echo( '<a href="?page=1" class="first">FIRST</a><a href="?page=' . $prevPage . '">PREV</a>' );    
}

$totalPages = $lastPage;
$curentPage = $page;
if ( $totalPages < 8 )
{
$start = 1;
$end = $totalPages;
}
else
{
if ( $curentPage > 3 )
{
	if ( $totalPages - $curentPage < 3 ) $end = $totalPages;
	else $end = $curentPage + 3;
	$start = $end - 6;
}
else
{
	$start = 1;
	$end = 7;
}
}

echo( '<strong>Page:</strong> ' );

if ( $start > 1 ) echo ' ... ';
for ( $i = $start; $i <= $end; $i++ )
{
if ( $i == $curentPage ) echo( '<span style="font-weight:bold;">' . $i . '</span>' ); else echo ' <a href="?id=' . $_GET['id'] . '&page=' . $i . '" style="font-size:1.00em;">' . $i . '</a> ';
}
if ( $end < $totalPages ) echo ' ... ';
echo( "<strong> of {$totalPages}</strong>" );

if ($page == $lastPage)
{
echo( 'NEXT LAST');
}
else
{
$nextPage = $page + 1;
?>
    <a href="?page=<?php echo $nextPage; ?>">NEXT</a>
<a href="?page=<?php echo $lastPage; ?>">LAST</a>
<?php
}
?>

affordit · February 3, 2008

Yes that works right thanks.

Can I ask one more question how would I format the output so that it goes accross the top of the page instead down the center?

Aureole · February 3, 2008

Well it's just simple HTML... you could try something like this.

<?php

include("sharons_dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
mysql_select_db(mydatabase) or die( "Unable to select database");

//Count rows
$query = "SELECT count(*) FROM content";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$page = ( isset( $_GET['page'] ) && is_numeric( $_GET['page'] ) ) ? $_GET['page'] : 1;

$query = "SELECT count(*) FROM `content`";
$result = mysql_query( $query );
$fetch = mysql_fetch_row( $result );
$numRows = $fetch[0];

$rowsPerPage = ( isset( $_GET['show'] ) && is_numeric( $_GET['show'] && $_GET['show'] >= 1 ) && $_GET['show'] <= 40 ) ? $_GET['show'] : 20;
$lastPage = ceil( $numRows / $rowsPerPage );

$page = (int)$page;
if( $page < 1 )
{
   $page = 1;
}
elseif( $page > $lastPage )
{
   $page = $lastPage;
}

if( $numRows > 0 ) $limit = 'LIMIT ' . ( $page - 1 ) * $rowsPerPage . ',' . $rowsPerPage;
else $limit = '';

//Query the DB
$query = "SELECT * FROM content $limit";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);


// process contents of $result
print "<table width='0' align='center'><tr>";
while ($info = mysql_fetch_array($result)) {
print "<td width='200'>";
echo $info['heading'];
echo "<BR>";
echo $info['picture'];
echo "<BR>";
echo $info['description'];
echo"<BR></td></tr>";
}

$lastPage = (int)$lastPage;
?>
<table width="100%" cellspacing="0">
    <tr>
<?php
if ( $page == 1 )
{
?>
	<td width="15%" style="padding:0;">
		<a href="#">First</a> | <a href="#">Previous</a>
	</td>
<?php
}
else
{
$prevPage = $page - 1;
?>
	<td width="15%" style="padding:0;">
		<a href="?page=1">First</a> | <a href="?page=<?php echo( $prevPage ); ?>">Previous</a>
	</td>
<?php 
}

$totalPages = $lastPage;
$curentPage = $page;
if ( $totalPages < 8 )
{
$start = 1;
$end = $totalPages;
}
else
{
if ( $curentPage > 3 )
{
	if ( $totalPages - $curentPage < 3 ) $end = $totalPages;
	else $end = $curentPage + 3;
	$start = $end - 6;
}
else
{
	$start = 1;
	$end = 7;
}
}
?>
	<td width="70%" class="black_td" style="padding:0;text-align:center;">
<?php
echo( '<strong>Page:</strong> ' );

if ( $start > 1 ) echo ' ... ';
for ( $i = $start; $i <= $end; $i++ )
{
if ( $i == $curentPage ) echo( '<span style="font-weight:bold;">' . $i . '</span>' ); else echo ' <a href="?id=' . $_GET['id'] . '&page=' . $i . '" style="font-size:1.00em;">' . $i . '</a> ';
}
if ( $end < $totalPages ) echo ' ... ';
echo( "<strong> of {$totalPages}</strong>" );
?>
	</td>
<?php
if ($page == $lastPage)
{
?>
	<td width="15%" style="padding:0;">
		Next | Last
	</td>
<?php
}
else
{
$nextPage = $page + 1;
?>
	<td width="15%" style="padding:0;">
		<a href="?page=<?php echo( $nextPage ); ?>">Next</a> | <a href="?page=<?php echo( $lastPage ); ?>">Last</a>
	</td>
<?php
}
?>
</tr>
</table>

Though tables are for tabular data and this isn't tabular data. You should do some HTML and CSS tutorials.

affordit · February 3, 2008

Thanks for all your help. I fixed the prob. by moving the </tr> out of the loop

Sign In

[SOLVED] Limiting records

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information