Professional Web Applications Themes

ORDER BY does not sort - MySQL

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 * ...

  1. #1

    Default 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.
    BlueBrooke Guest

  2. #2

    Default Re: ORDER BY does not sort

    On Jun 2, 5:13 pm, BlueBrooke wrote: 


    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

    strawberry Guest

  3. #3

    Default Re: ORDER BY does not sort

    On Sat, 02 Jun 2007 18:10:17 -0000, strawberry <com>
    wrote:
     
    >
    >
    >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[/ref]

    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.
    BlueBrooke Guest

Similar Threads

  1. PHP Sort order definitions
    By Csaba Gabor in forum PHP Development
    Replies: 0
    Last Post: May 3rd, 11:57 AM
  2. strange sort order
    By Dan Laflamme in forum PERL Beginners
    Replies: 10
    Last Post: January 15th, 05:12 PM
  3. no sort order for clients?
    By Lorenz in forum FileMaker
    Replies: 1
    Last Post: July 28th, 02:08 PM
  4. Sort order
    By hobbit in forum IBM DB2
    Replies: 4
    Last Post: July 11th, 12:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139