problem with Inner Join and order by

[str8_doughboy]

Hi All, 

Tearing my hair out over what seems like it should be a simple thing to do.

One of my databases has two tables:

Donors
Donations

The Donors table contains a donor id, First Name, Last Name, Address, etc about the donors.

The Donations table contains a donation id, donor id, donation amount, month, day, year, and other misc information about the individual donation.

I have a php script that downloads and inner join of the two tables into an excel spreadsheet.

Here's the section I'm having trouble with:
[code]

$select = "SELECT Donors.DID, Donors.DFirstName, Donors.DLastName,
Donations.DonMonth, Donations.DonDay, Donations.DonYear,
Donations.DonAmount FROM Donations INNER JOIN Donors ON
Donations.DonId = Donors.DID ORDER By Donors.DLastName";

[/code]

This works fine as it is... however in addition to being able to order by Donors.DLastName, i'd also like the results sorted by date (most recent to furthest back).

I tried altering the above code as below.

[code]

$select = "SELECT Donors.DID, Donors.DFirstName,
Donors.DLastName, Donations.DonMonth, Donations.DonDay,
Donations.DonYear, Donations.DonAmount FROM Donations
INNER JOIN Donors ON Donations.DonId = Donors.DID ORDER By
Donors.DLastName (ASC), Donations.DonYear (DESC),
Donations.DonMonth (DESC), Donations.DonDay (DESC)";

[/code]
I now get 0 results.  I then tried the following:

[code]

$select = "SELECT Donors.DID, Donors.DFirstName, Donors.DLastName,
Donations.DonMonth, Donations.DonDay, Donations.DonYear, Donations.DonAmount
FROM (SELECT * FROM Donations ORDER By DonId (ASC), DonYear(DESC),DonMonth(DESC),
DonDay(DESC)) INNER JOIN Donors ON Donations.DonId = Donors.DID ORDER By Donors.DLastName";

[/code]

Again, 0 results.

Any suggestions?

Thanks!

Dave

PS.  PHP version 4.3.11
      MySql version 4.0.18

[jcarouth]

So this [code]SELECT
  Donors.DID
  , Donors.DFirstName
  , Donors.DLastName
  , Donations.DonMonth
  , Donations.DonDay
  , Donations.DonYear
  , Donations.DonAmount
FROM
  Donations
INNER
  JOIN
    Donors
      ON Donations.DonId = Donors.DID
ORDER
  BY
    Donors.DLastName ASC
    , Donations.DonYear DESC
    , Donations.DonMonth DESC
    , Donations.DonDay DESC[/code] won't give you results?

In the future, you can consolidate those date fields into one column of type date or datetime and extract the specific part you want when necessary.

[str8_doughboy]

That fixed it, apparantly it was choking on the ( ) around the ASC and DESC!!!

Thanks much for your help!

Dave ;D