SET @letter := 'B'; SELECT name FROM table WHERE SUBSTRING(name, 1) == @letter; SELECT name FROM table WHERE name like concat(@letter, '%'); I don't know a faster way to do it On Wed, Jul 15, 2009 at 12:57 PM, Ashley Sheridan<ash@xxxxxxxxxxxxxxxxxxxx> wrote: > On Wednesday 15 July 2009 16:46:53 Andrew Ballard wrote: >> On Wed, Jul 15, 2009 at 11:30 AM, Ashley >> >> Sheridan<ash@xxxxxxxxxxxxxxxxxxxx> 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 > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Martin Scotta -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
