On 31 Mar 2009 at 18:15, PJ wrote:
<snip>
> >> But I see that I may be trying to do too much - I thought of showing how
> >> many books were listed under each letter of the alphabet but I don't see
> >> how it can be done in any simiple way as it would mean that I would have
> >> to do the select once with the ORDER BY and a second time without it
> >> just to get the number of listing. If there are a lot of books, like
> >> thousands, it might slow down things.
> >> I suppose I could live with ORDER BY "title" as that does not require
> >> another effort.
> >> Any thoughts or suggestions?
Hi,
Sounds like you need to use the GROUP BY functions of MySQL
This SQL is probably wrong because I don't remember seeing your schema (and am
too
busy here to go looking!)
SELECT
LEFT(last_name, 1 ) as Letter, Count(bookID) as NumberOfBooks
FROM
books INNER JOIN <tables that join them...>
GROUP BY Letter
ORDER BY Letter ASC
You will have to play around with that to get the right results. But it should
give you
something like:
Letter,NumberOfBooks
A,47
B,21
C,8
...
The MySQL manual has more info:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions-and-modifiers.html
Regards
Ian
--
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
