Take the 2-minute tour ×

So, I am trying to pull a first name, last name and donortype from 2 tables. Basically, a person has a primary "donortype". Meaning if they are two different donortypes then in the Donor table you put the one with the highest rank. In the DonorandType table you put all the remaining. So if they are a board member, alumni and parent you would see board member in the donor table and then alumni and parent in the other table.

I end up getting all the people in the donorandtype table(I only put 4 people in there for testing each with a different number of types) showing up continually. However, if I choose 2011(last years fiscal year) they don't repeat. However, in 2012(this fiscal year) they do. If you would prefer I show all my code and not just the piece about the sql statement and while loop I can. However, I don't think it is an issue with my date constraint.

I also have some date constraints in my sql statement but I don't think that should make a difference. It basically just checks the fiscal year and if they haven't donated that year it doesn't show them. I have tried it with and without that part of the SQL statement and it doesn't seem to make a difference. So, I believe that part can be ignored.

Donor Table:

  • DonorID (PK)
  • FirstName
  • LastName
  • Email
  • Phone
  • DonorType (FK)

DonorType Table:

  • DonorTypeID (PK) -- This is a 2 letter abbreviation of the donortype ex: AL for Alumni
  • DonorType -- These are the names of each donor, 8 in total
  • Rank -- Basic rank 1,2,3 and so on of importance

DonorandType Table:

  • DonorID (FK)
  • DonorType(FK)

Donation Table:

  • DonationID (PK)
  • DonationAmount
  • Restriction
  • Description
  • DateReceived
  • DonorID(FK)

Ok, so I want to pull their first name & last name from donor table. Then pull all their donortype's from the DonorandType table.

// SQL query to interact with info from our database
$sql = mysql_query("
    SELECT donor.FirstName, donor.LastName, donorandtype.DonorType
    FROM donor, donortype, donorandtype, donations
    WHERE donor.DonorID = donorandtype.DonorID
      and donortype.DonorType = donorandtype.DonorType
      and donations.Date_Received BETWEEN '" . $startdate . "' and '" . $enddate . "'
") or die (mysql_error()); 

// Establish the output variable
    <th id='name'>Donor Name</th>
    <th id'dontype'>Donor Type</th>
while($row = mysql_fetch_array($sql)){ 

    $first_name = $row["FirstName"];
    $last_name = $row["LastName"];
    $donor_type = $row["DonorType"];

    echo "
    <td> $first_name $last_name </td>
    <td>$donor_type </td>

echo '</table>';

This part actually displays in a table...does that need to be said? I think it is clear from the code above but whatever.

Donor Name  Donor Type
Henry Hunt  Alumni
Henry Hunt  Board member
Paul Gates  Past Parent
Winifred Gardner    Parent
share|improve this question
do paste your output too. –  Joddy Dec 7 '12 at 4:57
@cHao mysql_query works just as well as anything else. Also, hate it when people mock w3schools. It's a great way to learn for beginners. –  christopher Dec 7 '12 at 5:03
You haven't given us the details of the donations table. –  Lucanos Dec 7 '12 at 5:08
w3schools is awesome, almost as awesome as SO but anyway. I will edit my post with the output. I am assuming it is ok to just display it using the {} because it is pretty long and I don't want people to have to scroll through a mile of text. –  Joel Dec 7 '12 at 5:10
Do you need the donations table? The query doesn't involve it. It is simply there because I have a java box created that is used to choose a particular fiscal year and then it only displays results based off if a donor has donated in that fiscal year. For example, if I donate in 2011 then I show up under the 2011 fiscal year and not under 2012. If I donate in 2012 then the reverse is true and if I donate in both then I show up in both. I will add it though into my post though. In this query I really don't need any info about the donation except the date and don't display it. –  Joel Dec 7 '12 at 5:16

1 Answer 1

up vote 1 down vote accepted
// SQL query to interact with info from our database
$sqlTpl = 'SELECT `d`.`FirstName`, `d`.`LastName`, `t`.`DonorType`
             `donorandtype` AS `j`
             LEFT JOIN `donor` AS `d` ON ( `j`.`DonorID` = `d`.`DonorID` )
             LEFT JOIN `donortype` AS `t` ON ( `j`.`DonorType` = `t`.`DonorTypeID` )
             LEFT JOIN `donations ` AS `m` ON ( `d`.`DonorID` = `m`.`DonorID` )
             `m`.`Date_Received` BETWEEN "%s" AND "%s"
           ORDER BY
             `d`.`DonorID` , `t`.`Rank`';
$sqlStr = sprintf( $sqlTpl ,
            date( 'Y-m-d h:i:s' , strtotime( $startdate ) ) ,
            date( 'Y-m-d h:i:s' , strtotime( $enddate ) ) );
$sqlRes = mysql_query( $sqlStr );

if( !$sqlRes ){
  echo 'SQL Error Occurred:';
  echo 'But I am not going to show the error messages publicly.';
      <th id="name">Donor Name</th>
      <th id="type">Donor Type</th>
      <th colspan="2"><?php echo mysql_num_rows( $sqlRes ); ?> Donors Found</th>
  if( mysql_num_rows( $sqlRes ) ){
    while( $r = mysql_fetch_array( $sqlRes ) ){
      <td><?php echo $r['FirstName'].' '.$r['LastName']; ?></td>
      <td><?php echo $r['DonorType']; ?></td>
      <td colspan="2">No Records Returned</td>
share|improve this answer
Thanks Lucanos, this worked. –  Joel Dec 7 '12 at 5:37

Your Answer


