Re: Alphabetical pagination

On Wednesday 15 July 2009 16:46:53 Andrew Ballard wrote: > 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 I just had a query doing the same thing one time, and that did take it's time (about 2-3 seconds) but it did have a few million records to look at, so I can understand why it was slow! And it was on MSSQL, with no indexes set up :( I nearly cried when I saw what I was dealing with! -- Thanks, Ash http://www.ashleysheridan.co.uk