Returning only a set amount of rows per page

[jaZon]

How would I go about having a query return only, say 10, rows per page? If I have a huge item list I don't want them all displayed at the same time, so splitting them up into pages is the only option.

[jburfield]

Add a LIMIT clause to your sql query.

For instance, something like this:

$query = "SELECT * from YOURTABLE limit 10";

That will give you the first 10 results.

You can also do: LIMIT 10,10 to get the second set of 10, etc, etc.

-- Jason

[jaZon]

so how would you allow the user to view the next pages? sorry, but i'm pretty ne

[jburfield]

One way to do it is through form fields...you could have a hidden field that contains the start for the limit on the next page.

<input type="hidden" name="start" value="10">

could be on your first page of results, showing 10 items.

In the query on the next page you could do something like:

$query = "SELECT * from tablename ... LIMIT " . $start . ", 10";

Which would give you a limit clause of: limit 10, 10

There have been a few posts about stuff like this on here in the past...do a search for 'previous button' or similar and you should be able to find some more info.

-- Jason

[jaZon]

i'm gong to search, but is there anyway to do that w/o having a seperate page?? Can you have a button or something that will tell the page you're already on to display the next 10 results and then reload it??

[jburfield]

Actually, that is the way it should be done.

Quick and simple is to pass the variable back to the same page on POST.

-- Jason

[br__ken]

i just battled this problem a few days ago, this is how i did it:
http://zbdesign.biz/code/user.code.php

(check the 'view source' button on the footer)

[jaZon]

broken, I like your setup, but i'm not quite sure i follow the entire thing

[br__ken]

anything you need me to explain just PM me or paste it. it basically runs a dummy query (select * from userfiles) and then counts the num_rows to that to get the $total rows.

i then use that $total var at the bottom to setup the pages- i take $total / 5 (i want 5 per page) and thats how many pages i make, then if you look at the line with $from=(isset($fr)?$fr:0; line its basically turning the variable 'fr' into the LIMIT $from,5 part. so each page number links to php_self with fr=where to start the list from

it was my first attempt at this sort of thing- so it might be somewhat sloppy, but it works 😃

edit: ignore the first part of the code-> this just puts all files in the directory into an array and checks it against the db list. so when i delete files it will remove them from the db also since the file list is from my 'userfiles' table and not reading a directory.

[jmcneese]

    $nrows = count($dbresult);

if($nrows <= $offset+$limit) {
    $to = $nrows-1;
} else {
    $to = $offset+$limit-1;
}

$from = $offset;

foreach (range($from, $to) as $rownum) {
    $row = $dbresult[$rownum];
    if(!empty($row)) {
        // do row display stuff here
    }
}

$numpages=ceil($nrows/$limit);

if($numpages > 1) {
    $pagebits = "Page: ";
    for ($i=0;$i<$numpages;$i++) { 
        $newoffset=$limit*($i);
        $pg = $i + 1;
        if($page == $pg) {
            $pagebits .= "<b>$pg</b>&nbsp;";
        } else {
            $pagebits .= "<a href=\"$PHP_SELF?offset=$newoffset&limit=$limit&page=$pg\">$pg</a>&nbsp;";
        }
    } 
    if($offset>1) { 
        $prevoffset = $offset - $limit;
        $prevpage = $page - 1;
        $previous = "<a href=\"$PHP_SELF?offset=$prevoffset&limit=$limit&page=$prevpage\">Previous $limit results</a> "; 
    } else {
        $previous = "&nbsp;";
    }
    if($nrows>($offset+$limit)) { 
        $nextoffset = $offset + $limit;
        $nextpage = $page + 1;
        $next = "<a href=\"$PHP_SELF?offset=$nextoffset&limit=$limit&page=$nextpage\">Next $limit results</a>"; 
    } else {
        $next = "&nbsp;";
    } 
}

now, all you have to do is decide how you want to display $next,$previous and $pagebits (maybe in a table?)...

can we make this a FAQ or something? it seems that someone is asking how to do this every day. 🙁

[jaZon]

Ok, using brokens method I get this:

http://jazon.tqhosting.com/prodlist/search2.php

By clicking on either of the selections on the left menu you get a page which is limited to 10 rows......BUT notice the bottom and try clicking.

The source is:

<?
// Connect to the database
mysql_connect ('localhost', 'login', 'pwd');
mysql_select_db ('jazon_tqhosting_com');







echo("<center>");
echo("<table border=1 cellspacing=0 cellpadding=3 bordercolor=000000 width=60%>");

echo("<tr bgcolor=#061690>");

echo("<td>");
echo("<font color=FFFFFF><center><b>SKU</b></center></font>");
echo("</td>");

echo("<td>");
echo("<font color=FFFFFF><center><b>Product</b><center></font>");
echo("</td>");

echo("<td>");
echo("<font color=FFFFFF><center><b>Price</b><center></font>");
echo("</td>");

echo("</tr>");

// setup total rows for pages //
$from=(isset($fr)?$fr:"0");
$q="SELECT * FROM circular_product_list";
$total=mysql_query($q);


switch($query) {
case 2:

$q="SELECT * FROM circular_product_list WHERE department = 02 ORDER BY product ASC LIMIT " . $from . ",10";

break;
case 3:

$q="SELECT * FROM circular_product_list WHERE department = 03 ORDER BY product ASC LIMIT " . $from . ",10";

break;
case 5;

$q="SELECT * FROM circular_product_list WHERE department = 05 ORDER BY product ASC LIMIT " . $from . ",10";

break;
case 8;

$q="SELECT * FROM circular_product_list WHERE department = 08 ORDER BY product ASC LIMIT " . $from . ",10";

break;
case 11;

$q="SELECT * FROM circular_product_list WHERE department = 11 ORDER BY product ASC LIMIT " . $from . ",10";

break;
case 18;

$q="SELECT * FROM circular_product_list WHERE department = 18 ORDER BY product ASC LIMIT " . $from . ",10";

break;
case 20;

$q="SELECT * FROM circular_product_list WHERE department = 20 ORDER BY product ASC LIMIT " . $from . ",10";

break;
}







$result = mysql_query ("$q");


//this block is php code, do not echo it
$bgcolor[0] = "#f3f3f3";
$bgcolor[1] = "#e1e1e1";
$x = 0;
$color=$bgcolor[$x];
//end php code block

while ($row = mysql_fetch_array($result)) {

echo("<tr bgcolor=$color>"); //output a row in color
//then the next two lines change the color
$x = ($x + 1) % count($bgcolor); //php code, do not echo this
$color=$bgcolor[$x]; //php code, do not echo this


echo("<td>");
echo("$row[sku]");
echo("</td>");

echo("<td>");
echo("$row[product]");
echo("</td>");

echo("<td>");
echo("$row[price]");
echo("</td>");
echo("</center>");

}



// print page #s out //
if(mysql_num_rows($total)>10)
{
    $pages=floor(mysql_num_rows($total)/10);
    // start page table & init row //
    ?>
    <table class=10 border=0 width="90%" align="center" cellspacing=2 cellpadding=2>
    <tr>
        <td width="5%">Page:</td>
        <td>[<a href="?fr=0">1</a>]&nbsp;
    <?
    for($i=1;$i<($pages+1);$i++)
        echo "[<a href=\"?fr=" . ($i*10) . "\">" . ($i+1) . "</a>]&nbsp;";

echo "</td></tr></table>";
}





?>

[br__ken]

u got the same problem. the deal with the pages is- in code it works BUT thers a small problem..

ok, when u hit the page and pass fr=5,10,etc. youre just passing that. and it works. (granted the limit isnt working for the base query)

heres the problem: youre running a switch on $query, and when you hit the page it ONLY transfers the fr var, and not the query var- SOO its bypassing the switch, only it wont error because $q is already set (froim the $total count) therefore, when it bypasses the switch $q = the query from the total, and you end up with a massive list. (which also explains why it isnt limiting)

solution-> gimme a min to retype the code

[br__ken]

ok man, here goes my attempt 🙂 this -should- work fine for u, if not u know the drill haha

<?
mysql_connect ('localhost', 'login', 'pwd');
mysql_select_db ('jazon_tqhosting_com');







echo("<center>");
echo("<table border=1 cellspacing=0 cellpadding=3 bordercolor=000000 width=60%>");

echo("<tr bgcolor=#061690>");

echo("<td>");
echo("<font color=FFFFFF><center><b>SKU</b></center></font>");
echo("</td>");

echo("<td>");
echo("<font color=FFFFFF><center><b>Product</b><center></font>");
echo("</td>");

echo("<td>");
echo("<font color=FFFFFF><center><b>Price</b><center></font>");
echo("</td>");

echo("</tr>");

// setup total rows for pages //
$from=(isset($fr)?$fr:"0");
$tquery="SELECT * FROM circular_product_list";
$total=mysql_query($tquery);


switch($query) {
case 2:
$q="SELECT * FROM circular_product_list WHERE department = 02 ORDER BY product ASC LIMIT " . $from . ",10";
// used to pass along with the 'fr' var in page numbers
$passq=2;

break;
case 3:

$q="SELECT * FROM circular_product_list WHERE department = 03 ORDER BY product ASC LIMIT " . $from . ",10";
$passq=3;

break;
case 5;

$q="SELECT * FROM circular_product_list WHERE department = 05 ORDER BY product ASC LIMIT " . $from . ",10";
$passq=5;


break;
case 8;

$q="SELECT * FROM circular_product_list WHERE department = 08 ORDER BY product ASC LIMIT " . $from . ",10";
$passq=8;


break;
case 11;

$q="SELECT * FROM circular_product_list WHERE department = 11 ORDER BY product ASC LIMIT " . $from . ",10";
$passq=11;


break;
case 18;

$q="SELECT * FROM circular_product_list WHERE department = 18 ORDER BY product ASC LIMIT " . $from . ",10";
$passq=18;


break;
case 20;

$q="SELECT * FROM circular_product_list WHERE department = 20 ORDER BY product ASC LIMIT " . $from . ",10";
$passq=20;


break;
default:
	// heres where it falls through - this should stop it
	// default passq as 2 just so we get hits next time around . . .
	$passq=2;
	echo "No query was found. Please fix me!";
	exit;
	// drop php with exit so $result doesnt come back as null
}



$result = mysql_query ($q);


//this block is php code, do not echo it
$bgcolor[0] = "#f3f3f3";
$bgcolor[1] = "#e1e1e1";
$x = 0;
$color=$bgcolor[$x];
//end php code block

if($r && mysql_num_rows($r)>0):

while ($row = mysql_fetch_array($result)) {

echo("<tr bgcolor=$color>"); //output a row in color
//then the next two lines change the color
$x = ($x + 1) % count($bgcolor); //php code, do not echo this
$color=$bgcolor[$x]; //php code, do not echo this


echo("<td>");
echo("$row[sku]");
echo("</td>");

echo("<td>");
echo("$row[product]");
echo("</td>");

echo("<td>");
echo("$row[price]");
echo("</td>");
echo("</center>");

}



// print page #s out //
if(mysql_num_rows($total)>10)
{
    $pages=floor(mysql_num_rows($total)/10);
    // start page table & init row //
    ?>
    <table class=10 border=0 width="90%" align="center" cellspacing=2 cellpadding=2>
    <tr>
        <td width="5%">Page:</td>
        <td>[<a href="?fr=0&query=<?=$passq?>">1</a>]&nbsp;
    <?
    for($i=1;$i<($pages+1);$i++)
		// add in the query=$passq to make sure it hits the switch
        echo "[<a href=\"?fr=" . ($i*10) . "&query=$passq\">" . ($i+1) . "</a>]&nbsp;";

echo "</td></tr></table>";
}


// endif from the (if $r . . . )
endif;


?>

[br__ken]

on further note: that switch is ugly as fuck 🙂 (sorry)

this should save a LOT of space and help clean things up a bit..

$cases=array("2","3","5","8","11","18","20");
// default it to avoid errors
$from=(isset($fr)?$from:0);
// default query to 2, it avoids errors
$query=(isset($query)?$query:2);
if(!in_array($query,$cases)):
	// query # wasnt in the array $cases
	echo "Invalid Query number!!";
else:
	// keep it without the 0 for easy matching
	$passq=$query;
	// add leading 0
	$dep=(($query<10)?"0" . $query:$query);
	$q="SELECT * FROM circular_product_list WHERE department = $dep ORDER BY product ASC LIMIT " . $from . ",10";
endif;

echo $q; // just so you can see how the query looks

[jaZon]

ok, I've got no errors.......but no page numbers at the bottom, either =(

[jewps]

why dont you try this, just replcae my query class with yours..

	($_GET['displimit']) ? $displimit = $_GET['displimit'] : $displimit = 9;
	(!$_GET['page']) ? $page = 1 : $page = $_GET['displimit'];

if ($page == 1) $pageoffset = 1;
else $pageoffset = ($page - 1) * $displimit;

$query .= " limit $pageoffset, $displimit"; //Executes current Offset

$conndb2 = new connDB; $conndb2->selectdb("", 2); //--start-- getting row count
$conndb2->query("select count(*) from imggal where galid = '$gid' and active = 'y'");
$count = mysql_fetch_row($conndb2->results); //--end-- count[array], one var.

$pagecount = $count[0] / $displimit; // This is to round over the pagecount --start--
$pagecount = explode(".", $pagecount);
$pagecount = $pagecount[0];
if (($count[0] % $displimit) > 0) $pagecount += 1; //--end--

$pagenext = $page + 1; //This is to check page count for errors --start--
if ($pagenext > $pagecount) $pagenext = $pagecount;
$pageprev = $page - 1;
if ($pageprev == 0) $pageprev = 1; //--end--

if ($page == 1) echo ("&lt");
else echo ("<a class=link href=\"?page=$pageprev\"><u><b> &lt </u></b></a>");
$i = 1; 
while ($i <= $pagecount)
{
	if ($i == $page) echo (" [$i] ");
	else echo ("<a class=link href=\"?page=$i\"> $i </a>");
	$i++;
}
echo ($page != $pagecount) ? "<a class=link href=\"?page=$pagenext\"><u><b> &gt </u></b></a>" : "&gt";
echo ("Page $page/$pagecount
	");

[br__ken]

ok, here is how i would do it - i didnt (couldnt) test it bc of our different dbs , so you may have to debug a little (maybe a parse error or 2) but i have it set to print errors for the main parts.

try this, if it isnt working ill try and figure something out

<?

// connection
mysql_connect ('localhost', 'login', 'pwd');
mysql_select_db ('jazon_tqhosting_com');

?>
<!-- table headings -->
<center>
<table border=1 cellspacing=0 cellpadding=3 bordercolor=000000 width=60%>
<tr bgcolor=#061690>
<td><font color=FFFFFF><center><b>SKU</b></center></font></td>
<td><font color=FFFFFF><center><b>Product</b><center></font></td>
<td><font color=FFFFFF><center><b>Price</b><center></font></td>
</tr>
<?

// setup defaults
$from=(isset($fr)?$fr:"0");
$query=(isset($query)?$query:"2";
// for pages at bottom
$tquery="SELECT * FROM circular_product_list";
$total=mysql_query($tquery);
// possible cases for query to =
$cases=array("2","3","5","8","11","18","20");

// main part //
if(!in_array($query,$cases)):
    echo "Invalid Query number!!";
else:
    // keep it without the 0 for easy matching
    // add leading 0
    $dep=(($query<10)?"0" . $query:$query);
    $q="SELECT * FROM circular_product_list WHERE department = $dep ORDER BY product ASC LIMIT " . $from . ",10";
    // used to re-pass along with page numbers
	$passq=$query;
endif;

if($q)
{
	// $q is set so query mysql
	$r = mysql_query ($q);

//this block is php code, do not echo it
$bgcolor[0] = "#f3f3f3";
$bgcolor[1] = "#e1e1e1";
$x = 0;
$color=$bgcolor[$x];
//end php code block

if($r && mysql_num_rows($r)>0):
	// theres a result so print the data out
	while ($row = mysql_fetch_array($result))
	{
		//then the next two lines change the color
		$x = ($x + 1) % count($bgcolor); //php code, do not echo this
		$color=$bgcolor[$x]; //php code, do not echo this

		// print out data 
		?>
		<tr bgcolor="<?=$color?>">
		<td><?=$row['sku']?></td>
		<td><?=$row['product']?></td>
		<td><?=$row['price']?></td>
		</tr>
		<?
	}


	// print page #s out //
	if(mysql_num_rows($total)>10)
	{
// edit: this shoudl work better -r
       $pages=floor((mysql_num_rows($total)-1)/10);

				// start page table & init row //
		?>
		<table class=10 border=0 width="90%" align="center" cellspacing=2 cellpadding=2>
		<tr>
			<td width="5%">Page:</td>
			<td>[<a href="?fr=0&query=<?=$passq?>">1</a>]&nbsp;
		<?
		for($i=1;$i<($pages+1);$i++)
			// add in the query=$passq to make sure it hits the switch
			echo "[<a href=\"?fr=" . ($i*10) . "&query=$passq\">" . ($i+1) . "</a>]&nbsp;";

		echo "</td></tr></table>";
	}

else:
	// no results present so print error
	echo "No Results were found.";
endif;
}else
	// query is NOT set, so print error
	echo "No query was found. Please alert the webmaster!";

?>

-r

[jaZon]

broken, this if else statement won't display ANYTHING for some reason....

// edit: this shoudl work better -r
       $pages=floor((mysql_num_rows($total)-1)/10);

                // start page table & init row //
        ?>
        <table class=10 border=0 width="90%" align="center" cellspacing=2 cellpadding=2>
        <tr>
            <td width="5%">Page:</td>
            <td>[<a href="?fr=0&query=<?=$passq?>">1</a>]&nbsp;
        <?
        for($i=1;$i<($pages+1);$i++)
            // add in the query=$passq to make sure it hits the switch
            echo "[<a href=\"?fr=" . ($i*10) . "&query=$passq\">" . ($i+1) . "</a>]&nbsp;";

        echo "</td></tr></table>";
    }

[br__ken]

throw in some echos, like echo $pages, $query, $q, $r.. etc.. etc until you find something thats not right

or if u want you can let me test it on my localhost , and u just change your db pw or some shit- thats last resort if you want