ORDER BY does not sort

Hi --

I have three tables: one for authors, one for books, and a link table
linking the author_id with the book_id.

I want to list first each author and then their books, excluding
authors that don't yet have books entered.

I did this:

// Only want to list authors that actually have books in the library
$query1 = "
SELECT DISTINCT author_id
FROM source_author";
$author_ids = mysql_query($query1) or die ("Error in query: $query1 .
" . mysql_error());
if (mysql_num_rows($author_ids) > 0) {
while(list($a_id) = mysql_fetch_row($author_ids)) {

// Get list of authors' names and dates
$query2 = "
SELECT *
FROM author
WHERE author_id = $a_id
ORDER BY birth, last";
$author_list = mysql_query($query2) or die ("Error in query: $query2 .
" . mysql_error());
if (mysql_num_rows($author_list) > 0) {

This worked -- my list includes only the authors who actually have
books entered (and the subsequent code listing the books works, too),
but my "ORDER BY" won't work no matter what I put into it ("birth" is
birth year and "last" is last name in the "author" table).

If I comment out my first query ($query1), the ORDER BY works, but
then I have not only authors with no books associated with them (in
the link table), I also have them listed many times.

I thought I'd figured out the hard part (distinct authors), but I
don't understand why my ORDER BY doesn't work? Is there a more
elegant way (that works <g>) to accomplish my goal?

Thank you for any help.

On Jun 2, 5:13 pm, BlueBrooke wrote:
> Hi --
>
> I have three tables: one for authors, one for books, and a link table
> linking the author_id with the book_id.
>
> I want to list first each author and then their books, excluding
> authors that don't yet have books entered.
>
> I did this:
>
> // Only want to list authors that actually have books in the library
> $query1 = "
> SELECT DISTINCT author_id
> FROM source_author";
> $author_ids = mysql_query($query1) or die ("Error in query: $query1 .
> " . mysql_error());
> if (mysql_num_rows($author_ids) > 0) {
> while(list($a_id) = mysql_fetch_row($author_ids)) {
>
> // Get list of authors' names and dates
> $query2 = "
> SELECT *
> FROM author
> WHERE author_id = $a_id
> ORDER BY birth, last";
> $author_list = mysql_query($query2) or die ("Error in query: $query2 .
> " . mysql_error());
> if (mysql_num_rows($author_list) > 0) {
>
> This worked -- my list includes only the authors who actually have
> books entered (and the subsequent code listing the books works, too),
> but my "ORDER BY" won't work no matter what I put into it ("birth" is
> birth year and "last" is last name in the "author" table).
>
> If I comment out my first query ($query1), the ORDER BY works, but
> then I have not only authors with no books associated with them (in
> the link table), I also have them listed many times.
>
> I thought I'd figured out the hard part (distinct authors), but I
> don't understand why my ORDER BY doesn't work? Is there a more
> elegant way (that works <g>) to accomplish my goal?
>
> Thank you for any help.


Why are there two queries?

tables:
author(author_id*,f_name,l_name,dob) - where dob uses the DATE
datatype

book(book_id*,book)

author_book(author_id*,book_id*)

SELECT DISTINCT (
a.author_id
), f_name, l_name, dob
FROM author a
JOIN author_book ab ON a.author_id = ab.author_id
ORDER BY dob, l_name

On Sat, 02 Jun 2007 18:10:17 -0000, strawberry <zac.carey@gmail.com>
wrote:

>On Jun 2, 5:13 pm, BlueBrooke wrote:
>> Hi --
>>
>> I have three tables: one for authors, one for books, and a link table
>> linking the author_id with the book_id.
>>
>> I want to list first each author and then their books, excluding
>> authors that don't yet have books entered.
>>
>> I did this:
>>
>> // Only want to list authors that actually have books in the library
>> $query1 = "
>> SELECT DISTINCT author_id
>> FROM source_author";
>> $author_ids = mysql_query($query1) or die ("Error in query: $query1 .
>> " . mysql_error());
>> if (mysql_num_rows($author_ids) > 0) {
>> while(list($a_id) = mysql_fetch_row($author_ids)) {
>>
>> // Get list of authors' names and dates
>> $query2 = "
>> SELECT *
>> FROM author
>> WHERE author_id = $a_id
>> ORDER BY birth, last";
>> $author_list = mysql_query($query2) or die ("Error in query: $query2 .
>> " . mysql_error());
>> if (mysql_num_rows($author_list) > 0) {
>>
>> This worked -- my list includes only the authors who actually have
>> books entered (and the subsequent code listing the books works, too),
>> but my "ORDER BY" won't work no matter what I put into it ("birth" is
>> birth year and "last" is last name in the "author" table).
>>
>> If I comment out my first query ($query1), the ORDER BY works, but
>> then I have not only authors with no books associated with them (in
>> the link table), I also have them listed many times.
>>
>> I thought I'd figured out the hard part (distinct authors), but I
>> don't understand why my ORDER BY doesn't work? Is there a more
>> elegant way (that works <g>) to accomplish my goal?
>>
>> Thank you for any help.
>
>
>Why are there two queries?
>
>tables:
>author(author_id*,f_name,l_name,dob) - where dob uses the DATE
>datatype
>
>book(book_id*,book)
>
>author_book(author_id*,book_id*)
>
>SELECT DISTINCT (
>a.author_id
>), f_name, l_name, dob
>FROM author a
>JOIN author_book ab ON a.author_id = ab.author_id
>ORDER BY dob, l_name

I could have sworn I tried that last night -- I spent most of
yesterday and this morning trying to accomplish this with one query,
but couldn't get it to work.

It works beautifully -- thank you so much.