Extract data from two tables

[danoli]

Hi

I have two tables I wish to extract data from.

Table1 & Table2  (table names simplified as real names are longish)

Table1 requires data from all fields whereas I only need data from a field called description IF table1.name = a session variable called $USERNAME

Table2 is a static table, it only contains 27 descriptions of student grades. A student will be given a grade (table1) and if they are given a specific grade, e.g. Grade1, then I want to pull the description for Grade1 from Table 2. They only get a grade for every 3rd pass they get so I want to list every grade from Table1 and list them in a table, the code checks to see if the grade is 3 (or 6, 9 etc)
and if a grade is given the relevant description is pulled from Table2. I haveall the required code working but not the ability to pull the descripion of the grade from Table2.

The code I am using for the query is

[code]$query = "select Table1.*, Table2.Description ".
    "FROM Table1, Table2 ".
          "WHERE Table1.name = '$USERNAME' ".
              "ORDER by Table1.MDate";[/code]

but this returns weird results. Basically, the code repeats every grade 27 times (the number of grades in table2) before showing the next grade, 27 times again... repeating through every grade.

Can anyone spot the error(s) in my query?


The full code I am using is as follows (if this helps)

[code]<?PHP


// $query = "select * from UDT_MERIT_LOG where NAME = '$USERNAME'order by MERIT_DATE";

$query = "select UDT_MERIT_LOG.*, UDT_MERIT_BADGE_DESCRIPTION.BADGE_DESCRIPTION ".
    "FROM UDT_MERIT_LOG, UDT_MERIT_BADGE_DESCRIPTION ".
          "WHERE UDT_MERIT_LOG.NAME = '$USERNAME' ".
              "ORDER by UDT_MERIT_LOG.MERIT_DATE";



$result = mysql_query($query) or die(mysql_error());
echo mysql_num_rows($result),' grading(s) found in database for ',$OWNER_NAME,"<BR><BR><BR>\n";


echo "<TABLE style=\"MARGIN-LEFT: auto; WIDTH: 90%; MARGIN-RIGHT: auto; TEXT-ALIGN: left\" borderColor=#333333 cellSpacing=0 cellPadding=0 align=center

border=2>\n";
echo "<TBODY>\n";
echo "<TR borderColor=#FF0000>\n";
echo "<TD style=\"WIDTH: 100px; TEXT-ALIGN: center\"vAlign=top align=left><B>Merrit Badge</B></TD>\n";
echo "<TD style=\"WIDTH: 90px; TEXT-ALIGN: center\" vAlign=top align=left><B>Date</B></TD>\n";
echo "<TD style=\"WIDTH: 90px; TEXT-ALIGN: center\" vAlign=top align=left><B>Dojo</B></TD>\n";
echo "<TD style=\"TEXT-ALIGN: center\"><B>Merit Badge Criteria & Testing Sensei's Comments</B></TD></TR>\n";
echo "<TR borderColor=#000000>\n";


while ($row = mysql_fetch_assoc($result)) {
    echo "<TR borderColor=#FF0000>\n";
    echo "<TD rowspan=2 style=\"TEXT-ALIGN: center\">  <IMG style=\"WIDTH: 100px; HEIGHT: 152px\" src=\"images/",$row[AUTO_IMAGE],"\" border=0></TD>";
    echo "<TD rowspan=2 style=\"WIDTH: 90px; TEXT-ALIGN: center\" vAlign=top align=left>".$row[MERIT_DATE]."</TD>\n";
    echo "<TD rowspan=2 style=\"WIDTH: 90px; TEXT-ALIGN: center\" vAlign=top align=left>".$row[DOJO]."</TD>\n";
    echo "<TD height=20 style=\"TEXT-ALIGN: left\" vAlign=top align=left padding-left=10px><B>Criteria: </B>".$row[MERIT_CRITERIA]."</TD></TR>";
    echo "</TR>\n";
    echo "<TR borderColor=#FF0000>\n";
    echo "<TD style=\"TEXT-ALIGN: left\" vAlign=top align=left padding-left=10px><B>Sensei's Comments: </B>".$row[MERIT_COMMENTS]."</TD></TR>";
    echo "</TR>";
//
// Now check if a star is to be awarded. Star awards given after every 3rd belt so check if last belt displayed is 3 6 9 12 15 18 21 24 27.
//
    $starawarded = '';
    if ($row[AUTO_IMAGE] == 'merit-03.gif') { $starawarded = 'belt-star-01.gif'; $backcolour = 'FFFF00';}
    elseif ($row[AUTO_IMAGE] == 'merit-06.gif') { $starawarded = 'belt-star-02.gif'; $backcolour = 'FF9900';}
    elseif ($row[AUTO_IMAGE] == 'merit-09.gif') { $starawarded = 'belt-star-03.gif'; $backcolour = '33CC00';}
    elseif ($row[AUTO_IMAGE] == 'merit-12.gif') { $starawarded = 'belt-star-04.gif'; $backcolour = '0000FF';}
    elseif ($row[AUTO_IMAGE] == 'merit-15.gif') { $starawarded = 'belt-star-05.gif'; $backcolour = 'FF0000';}
    elseif ($row[AUTO_IMAGE] == 'merit-18.gif') { $starawarded = 'belt-star-06.gif'; $backcolour = '990066';}
    elseif ($row[AUTO_IMAGE] == 'merit-21.gif') { $starawarded = 'belt-star-07.gif'; $backcolour = 'BBBBBB';}
    elseif ($row[AUTO_IMAGE] == 'merit-24.gif') { $starawarded = 'belt-star-08.gif'; $backcolour = 'CC9900';}
    elseif ($row[AUTO_IMAGE] == 'merit-27.gif') { $starawarded = 'belts-08-star.gif'; $backcolour = 'FFFFFF';}
    if ($starawarded <> '') {
        echo "<TR borderColor=#000000 bgcolor=$backcolour>\n";
        echo "<TD colspan=4> <div align=center> <IMG style=\"HEIGHT: 22px\" src=\"images/",$starawarded,"\" border=0> </div> </TD>";
        echo "</TR>";
    }
}

echo "</TBODY>\n";
echo "</TABLE>\n<BR><BR><BR>";
?> [/code]

[printf]

Whats the name of the column in ta that holds the grade => [b]Grade1[/b], and what is the name of the column in tb that contains the matching [b]Grade1[/b]! You need a relationship, which you don't have in the current query!

printf

[danoli]

Sorry, a mistake on my part. I was working on two issues at the same time, however the problem I have is still based on the same code.

Every grade the student gets has a standard comment, not every third (this is an additional awarded badge). So, when the student is given grade 1, the details of grade 1 are taken from the table2. So everyone getting grade1 gets the same comment.

It is all based on the grade badge (gif file) awarded, so I think the answer to your question is the AUTO_IMAGE column in both tables.


[b]TableA[/b]
UDT_MERIT_LOG.NAME
UDT_MERIT_LOG.MERIT_DATE
UDT_MERIT_LOG.DOJO
UDT_MERIT_LOG.MERIT_CRITERIA
UDT_MERIT_LOG.MERIT_COMMENTS
UDT_MERIT_LOG.[b]AUTO_IMAGE[/b]

[b]TableB[/b]
UDT_MERIT_BADGE_DESCRIPTION.BADGE
UDT_MERIT_BADGE_DESCRIPTION.BADGE_TITLE
UDT_MERIT_BADGE_DESCRIPTION.BADGE_DESCRIPTION
UDT_MERIT_BADGE_DESCRIPTION.[b]AUTO_IMAGE[/b]

[printf]

If they have a relationship (ta.AUTO_IMAGE = tb.AUTO_IMAGE) then this will work.

[code]$sql = "SELECT ta.*, tb.BADGE_DESCRIPTION AS BADGE_DESCRIPTION
FROM UDT_MERIT_LOG AS ta
LEFT JOIN UDT_MERIT_BADGE_DESCRIPTION AS tb
ON(ta.AUTO_IMAGE = tb.AUTO_IMAGE)
WHERE ta.name = '" . $USERNAME . "' ORDER BY ta.MERIT_DATE ASC";[/code]


printf

[danoli]

That worked a treat, just need to figure out what it all means... :)