CODEWITHSUNDEEP
phpmysql
Emma Kalson
Emma Kalson

Reputation: 3

php mysql query to join two tables

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!

Upvotes: 0

Views: 179

Answers (5)

Maulik patel
Maulik patel

Reputation: 2442

<?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>';
  }
 ?>

Upvotes: 0

Iłya Bursov
Iłya Bursov

Reputation: 24146

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>

Upvotes: 3

Jompper
Jompper

Reputation: 1402

$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.

Upvotes: 0

user2103237
user2103237

Reputation: 69

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

Upvotes: 0

MillaresRoo
MillaresRoo

Reputation: 3848

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>";
}

Upvotes: 0

Related Questions