akashi akashi - 5 months ago 11
SQL Question

output data based on two tables

I need to output a table based on two other tables as shown below:

table and output

case: there are two table "tbl_schedule" and "tbl_report"

this is my script:

$sql = mysql_query("SELECT*, count(*) as schedule_date FROM mst_schedule WHERE schedule_date LIKE '%$date' GROUP BY schedule_account") or die (mysql_error());
while ($data = mysql_fetch_array($sql)) {
$account = schAccount($data['schedule_account']);
$sql2 = mysql_query("SELECT * FROM trn_reportsch WHERE schedule_id='$data[schedule_id]' GROUP BY schedule_id");
echo "<tr>";
echo "<td>".ucfirst($account['admin_fullname'])."</td>";
while ($data2 = mysql_fetch_array($sql2)) {
echo "<td>".$data2['rating']."</td>";
}
echo "<td>".$data['schedule_date']."</td>";
echo "</tr>";
}


So far I don't get the desired output. How should I change the script?

Answer

Your code is almost correct.

Add following lines:

    $sql = mysql_query("SELECT*, count(*) as schedule_date FROM mst_schedule WHERE schedule_date LIKE '%$date' GROUP BY schedule_account") or die(mysql_error());
while ($data = mysql_fetch_array($sql)) {
    $account = schAccount($data['schedule_account']);
    $sql2 = mysql_query("SELECT * FROM trn_reportsch WHERE schedule_id='$data[schedule_id]' GROUP BY schedule_id");
    echo "<tr>";
    echo "<td>" . ucfirst($account['admin_fullname']) . "</td>";

    $bad = $good = $vGood = 0; // <-- ADD THIS LINE

    while ($data2 = mysql_fetch_array($sql2)) {
        if($data2['rating'] <=2){ // BAD
            $bad++;
        } else if($data2['rating'] <= 3){ // GOOD
            $good++;
        } else if($data2['rating'] > 3){ // VERY GOOD
            $vGood++;
        }
    }
    echo "<td>" . $bad . "</td>"; // Display the final value for bad
    echo "<td>" . $good . "</td>"; // Display the final value for good
    echo "<td>" . $vGood . "</td>"; // Display the final value for very good
    echo "<td>" . $data['schedule_date'] . "</td>";
    echo "</tr>";
}