php mysql查询加入两个表

Question

I've spent a long time today looking through SO and have yet to find an answer that I've implemented that works as I need and as this is the first time I've tried to join tables rather than create inefficient monsters, I thought I'd ask for help!

I have two tables:

1. userData (id, surname, email)
2. appointmentData (id, uid, appointmentNum, appointmentDate)

The uid in the second table corresponds with the id of the first table.

What I would like to do is display a table:

surname | email | appointment1 | appointment1 date | appointment2 | appointment2 date etc...

I tried:

mysql_query("SELECT * FROM userData as table1 INNER JOIN appointmentData as table2 ON table1.id = table2.uid ORDER BY table1.surname ASC");

But this will only display the first appointment for that particular user, if I use a while loop (I think I'm trying to display the data horizontally in the table when it's vertically in the array, if that makes sense):

while($row = mysql_fetch_array($result)) {
echo"<td>".$row['surname']."</td>";
echo"<td>".$row['email']."</td>";
echo"<td>".$row['apptDate']."</td>";
...
}

I'd really appreciate if someone could help point me in the right direction!

Answer 1

you can start with:

$prevId = '';
while($row = mysql_fetch_array($result)) {
    if ($row['id'] != $prevId) {
        if ($prevId != '') echo '</tr>'; // close previous row
        echo '<tr><td>' . htmlspecialchars($row['surname']) . '</td>';
        echo '<td>' . htmlspecialchars($row['email']) . '</td>';
        $prevId = $row['id'];
    }
    echo '<td>' . $row['apptDate'] . '</td>';
}
if ($prevId != '') echo '</tr>'; // close last row

this will display table you needed, but table layout will not be very correct (browser will display properly, but actual html code will not be right)

to fix this - you need to count how many columns were displayed and add appropriate number of empty <td></td> before closing </tr>