0

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!

Improve this question
2
  • what should be displayed if one user has 1000000 appointments and another user has only 1 appointment? Commented Dec 20, 2013 at 21:38
  • Sorry, I should have specified, there is a maximum of three appointments, so I expect that if there is no appointment date set, I will need to display a blank table cell. Commented Dec 20, 2013 at 21:41

5 Answers 5

Reset to default
3

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>

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Ilya, you're a star! I'll need to go through as you say and sort the counts but you've helped me more in the past 10 minutes, than I've managed to achieve in the past 5 hours. Thank you so much, I've ticked the answer to accept it (let me know if there's anything else I need to do). Just in case anyone else uses this answer by the way, the line: $prevName = $row['surname'] needs a semi-colon on the end.
0

If you want a row for each record you must include a <tr> tag this way:

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

1 Comment

I cut down on the code for brevity - the lack of <table>, <thead>, <th>, <tbody> etc... tags aren't the issue unfortunately - it's the way I'm pulling from the data. I didn't want to display the whole page, as it added no value to the question.
0

The point here is that you have multiple appointments for one user and I'm thinking the "inner join" isn't the one you need if you're looking for what I said before. Try using outer join for multiple rows for each user.

This might help http://www.w3schools.com/sql/sql_join_left.asp

Improve this answer

Comments

0 
$result = mysql_query("SELECT * FROM userData ORDER BY table1.surname ASC");
while($row = mysql_fetch_assoc($result)){
    $result2 = mysql_query("SELECT * FROM appointmentData WHERE uid = {$row['id']} LIMIT 3");
    $appointment1 = mysql_fetch_assoc($result2);
    $appointment2 = mysql_fetch_assoc($result2);
    $appointment3 = mysql_fetch_assoc($result2);
    echo "<td>" . (($appointment1==false)?"":$appointment1['appointmentDate']) . "</td>";
}

I'm writing this on my phone, but maybe you got my idea.

Improve this answer

Comments

0 
<?php

  $query="SELECT * FROM userData as table1 INNER JOIN appointmentData as table2 ON table1.id = table2.uid ORDER BY table1.surname ASC";
 $result=mysql_query($query);
 echo "table";
 while($row=mysql_fetch_array($result))
  {
    echo '<tr><td>' . htmlspecialchars($row['surname']) . '</td>';
    echo '<td>' . htmlspecialchars($row['email']) . '</td>';
    echo '<td>' . htmlspecialchars($row['appointment1 ']) . '</td>';
    echo '<td>' . htmlspecialchars($row['date ']) . '</td>';
    echo '</tr>';
  }
 ?>
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.