Table Joining Issue

[Sonic_Rage]

Next problem 🙁

I have two tables. One called donors, the other called donations.

The Donor table looks like this:
CREATE TABLE donors (
donor_id int(9) NOT NULL auto_increment,
primary_first varchar(40) default NULL,
primary_last varchar(40) default NULL,
secondary_first varchar(40) default NULL,
secondary_last varchar(40) default NULL,
date date NOT NULL default '0000-00-00',
company varchar(40) default NULL,
address varchar(40) default NULL,
city varchar(40) default NULL,
state varchar(25) default NULL,
zip varchar(12) default NULL,
phone varchar(15) default NULL,
email text,
newsletter char(3) default NULL,
last_login date NOT NULL default '0000-00-00',
logins int(5) NOT NULL default '0',
PRIMARY KEY (donor_id)
) TYPE=MyISAM;

The Donations Table looks like this:
CREATE TABLE donations (
donation_id int(9) NOT NULL auto_increment,
donor_id int(9) NOT NULL default '0',
donation_date date default NULL,
amount float(6,2) NOT NULL default '0.00',
check int(1) default NULL,
check_num int(6) default NULL,
cash int(1) default NULL,
credit int(1) default NULL,
goods int(1) default NULL,
PRIMARY KEY (donation_id)
) TYPE=MyISAM;

I want to generate a report that will show the total donations made. But where it shows donor_id in the donations table, I want it to pull the primary_first from the donors table that matches the donor_id. SO instead of a number being shown, it's the donor's name.

My query looks like this:

   $conn = db_connect();
   $query = "SELECT *, DATE_FORMAT(donation_date, '%m/%d/%Y') AS thedate FROM donations, donors where donations.donor_id=donors.primary_first";
   $result = @mysql_query($query);
   $num_results = mysql_num_rows($result);
    $i = 0; 
    while($row = mysql_fetch_array($result))

         { 
           $bgcolor = ($i++ & 1) ? '#E9EAED' : '#eeeeff'; 
           echo"<TR bgcolor='$bgcolor'><TD WIDTH=100><P CLASS=small>".$row["thedate"]."</TD><TD><P CLASS=small>".$row["donor_id"]."</TD><TD><P CLASS=small>".$row["amount"]."</TD>";
}

But when I do this, the table goes blank and displays nothing on the page. What am I doing wrong here?

😕

Thanks.

[blobbox]

It looks like your query is just joining on the wrong field, just change the where clause. The query should look like:

SELECT *, DATE_FORMAT(donation_date, '%m/%d/%Y') AS thedate FROM donations, donors where donations.donor_id=donors.donor_id;

and then in the PHP you should use:

$row["primary_first"]

instead of :

$row["donor_id"]

Also if you want totals for the donations you are going to need to add a group by clause to the SQL and a sum on the amount field.

Hope this helps,