Re: Alphabetical pagination

On Wed, Jul 15, 2009 at 11:30 AM, Ashley Sheridan<ash@ashleysheridan.co.uk> wrote: > On Wednesday 15 July 2009 16:21:22 tedd wrote: >> At 12:38 PM -0700 7/14/09, Miller, Terion wrote: >> >I am trying to make a page that displays a-z like a b c d e etc as links >> >then when you click open one it reloads itself and shows only the query >> >results that go with that letter...i'm not getting it....I get a page that >> >says ARRAY over and over... >> > >> >What I have so far: >> >> -snip- >> >> Why not have MySQL sort the data instead of using php? >> >> For example (from memory -- use with caution) >> >> SELECT name FROM restaurant ORDER BY name DESC LIMIT $offset, 1 >> >> Then just change the offset to go up and down the list. >> >> Cheers, >> >> tedd >> -- >> ------- >> http://sperling.com >>  http://ancientstones.com >>  http://earthstones.com > > You could do what Tedd suggested, but use MySQL to actually limit the results > it returns you by using a like clause, i.e. WHERE somefield > LIKE 'a%'. > > *ducks to avoid people throwing things at him. I know it's slow!* > > -- > Thanks, > Ash > http://www.ashleysheridan.co.uk > Why would that be slow? Using LIKE isn't always a bad thing. In this case, the LIKE condition begins with a constant rather than a wildcard, so it should perform well. It can even benefit from an index on somefield if one exists. I only see a couple issues with tedd's query: 1) As written, it only returns one row. To get it to return a list, you'd have to call it repeatedly inside a for...loop where $offset increments begins at some value and increments/decrements to an ending value. But then he did say "from memory -- use with caution". The general idea is correct. 2) It implements numeric pagination, which is usually based on a fixed number of rows per page. The OP wanted alphabetical pagination (like an address book) with each page containing all entries that begin with the selected letter. Andrew