Jump to content

[SOLVED] Limiting records


affordit

Recommended Posts

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>";
}

Link to comment
Share on other sites

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> ";
}
?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

<?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> ";
}
?>

Link to comment
Share on other sites

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
}
?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.