How do I return 10 results per page using SELECT statement?

Hello,

I'm wasn't sure whether to post this under the PHP or under the MySQL section of the forums. Since most people (I'm assuming) who write PHP script, also use MySQL, I thought I'd stick with PHP. I'm rather new to both PHP and MySQL and I'd be very grateful to anyone who can help me solve a problem I'm having at the moment. First, I'll give you an introduction to my little project...

I'm developing a database in MySQL that is used to store the song titles of all the MP3s I have on my PC. I've written a basic search feature that works well. The user has 2 options: Either he can type a special "!ALL" "parameter", that I made up, to return all the songs in the database or the user may enter a few characters in the textbox and select "song title" or "artist" etc. from a dropdown to search for a specific song title or artist etc.. Hope you are following so far? Say for example the user presses submit after typing "!ALL" to return all the song titles - there could be hundreds of results. What I want to do now is display 10 results on a page, then on the next page another 10, etc. (like a real search engine!). I've tried doing it with the LIMIT statement, but like I said I'm new to this and discovered that that is not the right way of going about it. My question is this: how would I go about returning 10 matches per page? Do I use hidden form fields? What would the SELECT statement look like? What would the PHP scripting look like to setup the "back" and "forward" navigation between the result for the search?

If you need any more info, let me know. Thanks in advance for your help!

Hi.

Test using
SELECT * FROM your_datbase LIMIT 0, 30

This will return 30 hits.

Sincerely,
Christoffer
SWEDEN

Hi again

Thanks for that, but it's not quite right. I wanted something that would return all the rows of the table, but split the results up onto seperate pages. Say for example the SELECT statement returns 100 results, then I want 10 pages each with 10 results on it (continuing on from where the previous page left off) - just like a web search engine does. My real problem lies with what goes into the select statement and what is the PHP code I must use to control the navigation between the seperate pages of results. Also, what are the variables that I will need to keep all this together.

Thanks again,
Warren

OK, I am going to try and explain this as clear as I can, this is solution I have thought up in the last few seconds, this is not to say that it is the most ideal way.

With that small disclaimer out of the way here is how you could do it. As I don't know mysql as yet I'll write it in some psuedo type code.

Select all the results from the database. Put all the results from the database into a array.

Query the size of the array, if that is larger than 10 then display the first ten results. Also echo out images depending on what page you are on (dictated by a post value).

Example:

max_val = post_value x 10
loop = 0;
while loop < sizeof(array) && loop != max_val
echo out array[loop]
end loop

if post_value != "" | | post_value != 1
//echo out prev image
echo <link?pagenum=post_value-1><image>
end if

if NOT(more results to work out)
// echo out next iamge
echo <link?pagenum=post_value+1><image>
end if

A bit rough and ready, but not bad for making it up on the spot. I'm sorry its not more detailed but I hope it helps.

Falcon

Ok thanks falcon, I'm going to give that a try and let you know how it works out. Anyone else may still offer their help as I want to see how many different ways this can be done.

Thanks to everyone,
Warren

I've written an article at www.phpbuilder.com that deals with just this matter. It's a bit rough written on a Sunday morning before coffee (what do you expect, I did it for free..), but it seemed to be an area where many questions have arisen.

The sample code is NOT intended for production but more as an algorithm to describe HOW to build a multi-page result script including PREV 1 2 3 4 NEXT type indexing.

HTH

You are requested to visit http://www.bharatyellowpages.com and view a category that has the maximum no of entries. Multiple display page has been implemented there. If you find it useful please let me know.i can give u the code which implements the same.

Rgds



------------------
Anish Modi
Infotech World
India

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
I've written an article at www.phpbuilder.com that deals with just this matter. It's a bit rough written on a Sunday morning before coffee (what do you expect, I did it for free..), but it seemed to be an area where many questions have arisen.

The sample code is NOT intended for production but more as an algorithm to describe HOW to build a multi-page result script including PREV 1 2 3 4 NEXT type indexing.

HTH[/quote]

Rod,

I noticed you have a PHPBuilder address. How are you involved there? Would love to work with you on other tutorials (not necessarily for free). We don't have backing like Internet.com, but we're trying

Randy

Thanks everyone for all your help. Rod K, I saw your column at PHPBuilder last night coincedentally. How weird is that?

Amodi, I would love to see the code that you used to implement the search pages at http://www.bharatyellowpages.com...Thank you for your help.

Ok you guys, take care

Randy,

I'm not really affiliated at all. Tim Purdue, founder of PHPBuilder, offered a couple of the regular contributors there the address. I took it sort of as a vanity thing

I would like to write some more tutorials (I've got some ideas for "obscure" mysql functions) but until I clear up some things on my plate...