And although it blows out your single-query theory further out of the water,
you could query your listing for a count of each starting letter of the last name, getting something like this: [syntax will be wrong (closer to Oracle syntax), but I don't have a mysql installation at work to get it right] select substr(upper(ln),0,1), count(1) from directory group by substr(upper(lname),0,1) (I don't think MySQL needs the group by clause (it implies any non-specified non-group columns), but it's good practice for others to be able to maintain your code) A 2 B 15 C 4 E 2 (note skipping D, as there may be some letters that don't appear...) you could use your front/middle-end to go through this list first, grouping out your letters for the letter-specific queries (supplied by Brent below). I would reccomend caching out results of this query, as it won't change often enough to skew the results (likely) if you want to limit to 10 per page, you are going to need further pagination for entries with more than 10 entries per letter... > -----Original Message----- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 09, 2003 3:38 PM > To: Dave Dash > Cc: [EMAIL PROTECTED] > Subject: Re: Query pages by Alphabet > > > I'm not quite following what you are asking. If you want to limit the > result to only 10 items per page, you're going to need to > come up with > some paginating code (it actually isn't that hard). You could easily > get more than 10 names starting with a single letter. > > If you want to create specific links that show only name beginning > with a letter or set of letters, then you need to do a search: > SELECT fn, ln FROM directory WHERE ln like "A%" ORDER BY > ln,fn LIMIT 10 > or for a group of letters > SELECT fn, ln FROM directory WHERE ln between "BAAA" AND "CZZZ" ORDER > BY ln,fn LIMIT 10 > > That's actually kind a fudge on the search for a group of letters. > Technically you should search for "between A and D" to get all names > beginning with B-C, but I think it reads better this way from > a coding > readability standpoint. > > On Tuesday, September 9, 2003, at 02:55 PM, Dave Dash wrote: > > > I have a page that is a directory of names ordered by lastname, > > firstname (e.g. SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT > > 0,10). > > > > I have it paginated so that there are 10 results per page. > What I'd > > like to do instead of having page numbers (which can be > unhelpful when > > trying to page through people's names) is be more like a phone book > > and let people click on links that are the first letters of > their last > > names > > > > For example, let's say my result set for > > > > SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 30, 10 > > > > is > > > > Jackson > > Johnson > > Knutson > > Kraig > > Liver > > Lombard > > Marx > > Maxx > > Milton > > Nixon > > > > The page link would be > > > > J-N > > > > I know how to get the letters for one page (well I think I do at > > least), but I want to get them for all pages > > > > So basically I'd have something like this for my page list: > > > > A B-C D E-G F-H I J-N O-Z > > > > and clicking on each page would result in entries only from that > > letter. The trick is I don't want more than 10 entries a page. Is > > there an easy way to do this? Possibly in a single query? > > > > Thanks > > > > -dd > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
