Topic awaiting preservation: php/mysql: quesry results on multiple pages . . (Page 1 of 1)

http://www.planterspeakers.com/forma3.html

I am proud to present to you the fruit of my labor I showed my client yesterday. He liked it. For the purposes of showing him how it works- I put some sample companies in for the state of ?New York? and zip code of ?11124?. In the next few months he will provide me with actual companies I can begin inserting into mysql dbase. Check it out . . . simple but I am proud of myself

Now the next project I want to tackle is developing it so the results come up a maximum of 5 companies to a page. For example, if I choose ?New York?, and say 12 companies get returned in my query- I only want 5 companies to appear on the first and second pages, then the remaining 2 companies on the third page.

Is there a term to describe this- once I find out I can check php.net and go from there. Also, if this works out- he will contract me to re-design his entire site . .

Thanks.
Chris



[This message has been edited by CRO8 (edited 12-23-2002).]

I did a similar thing with a photo gallery I set up a while ago. I was pulling nfo from an array rather than a DB, but perhaps it will help.

here are some tidbits of code...hopefully they make sense out of context

//set some numbers	

$picsnum = sizeof($cur_array);

$picspage = 10;

$numpages = ceil ($picsnum / $picspage);



//if $page is not specified, start at page 1

if (empty($page))

	{

	$page = 1;

	}

//if $page is incorrectly set higher than possible, go to the last page	

if ($page > $numpages)

	{

	$page = $numpages;

	}

	

//setup for later navigation 

$prevpage = $page - 1;

$nextpage = $page + 1;



$start = ($page - 1) * $picspage;

$stop = $start + $picspage;

To set up the basic variables...

And then -

or ($pic = $start; $pic < $stop  && $pic < $picsnum; $pic++)

		{

                                }

a loop for the data..stops at the predetermined number...


and then -

//generate the HTML for the next/prev buttons (to insert below)

$prevbutton = "<a href=\"thumbs.php?sec=$sec&page=$prevpage\"><img src=\"graphics/prev.gif\" width=\"39\" height=\"13\" alt=\"Previous Page\" border=\"0\" /></a>";

$nextbutton = "<a href=\"thumbs.php?sec=$sec&page=$nextpage\"><img src=\"graphics/next.gif\" width=\"39\" height=\"13\" alt=\"Next Page\" border=\"0\" /></a>";



if ($page < $numpages  && $start == 0) //1st of multiple pages - add a "next" button

	{

	$nav = "<div id=\"nav\"><div id=\"prev\"> </div><div id=\"next\">$nextbutton</div></div>";

	}



elseif	($page < $numpages && $start > 0) //middle of multiple pages - add a "prev" and a "next" button

	{

	$nav = "<div id=\"nav\"><div id=\"prev\">$prevbutton</div><div id=\"next\">$nextbutton</div></div>";

	}	



elseif	($page == $numpages && $start > 0) //last of multiple pages - add a "prev" button

	{

	$nav = "<div id=\"nav\"><div id=\"prev\">$prevbutton</div><div id=\"next\"> </div></div>";

	}	



else  //only 1 page - no nav needed

	{}

To set up the page navigation.

Hope that is helpful in some way...



[This message has been edited by DL-44 (edited 12-23-2002).]

Congratulations on your job well done!

There are a few different ways to limit results on returns from a database but I think the easiest to accomplish would be to use the LIMIT clause in your SELECT statement. It would go something like this

SELECT col_1, col_2 FROM your_table where state = 'new york' LIMIT 5;

This will return only the first 5 rows of the result. To continue along for the next page load where you would want to get the next 5 results all you would need to do is change your LIMIT statement slightly to read like this:

SELECT col_1, col_2 FROM your_table where state = 'new york' LIMIT 5, 5;

All your doing here is giving LIMIT a starting point of the first record you want returned and telling it to return 5 rows from that point.

I know this is a bit of a simplistic example and that it will take a little extra code to do the math and keep track of everything for your subsequent page loads, but that's the basic idea. You could also load all of the results into an array and the just keep turning back and forth in the array as need be. It takes a bit more code to do it this way, but causes you to hit the database a little less.

I hope this made sense and helped you out a bit.


-Butcher-


[This message has been edited by butcher (edited 12-23-2002).]