I need a double SELECT sql query from 2 different tables with names visits & items 1.: SELECT visitid, visitdate, visitreason FROM visits WHERE personid = 10 2.: SELECT itemid, itemname, itemtime FROM items WHERE itemvisitid= visitid I think I need to do a JOIN but don’t know exactly how.
Table examples:
Table: visits
visitid | personid | visitdate | visitreason
1 | 10 | 05/07/2014 | no reason
2 | 10 | 06/07/2014 | some reason
3 | 12 | 06/07/2014 | no reason
4 | 10 | 12/07/2014 | some other reason
Table: items
itemid | personid | itemvisitid | itemname | itemtime
1 | 10 | 2 | box | 23
2 | 10 | 2 | clock | 70
3 | 10 | null | water | 50
4 | 10 | null | paper | 40
5 | 12 | 3 | box | 26
What I have now is this:
$query = "SELECT visitid, visitdate, visitreason FROM visits WHERE personid = '10' ORDER BY visitdate DESC";
// 2nd select: "SELECT itemid, itemname, itemtime FROM items WHERE itemvisitid= visitid";
$db->setQuery($query);
$results = $db->query();
while($row = mysqli_fetch_array($results)){
echo "<tr>
<td>".$row['visitid'].", ".$row['visitdate']."</td>
<td>".$row['visitreason']."</td>
<td>".$row['itemid'].",".$row['itemname'].", ".$row['itemtime']."</td>
</tr>";
}
I need results to be something like this:
<tr>
<td>1, 05/07/2014</td><td>no reason</td><td></td>
<td>2, 06/07/2014</td><td>some reason</td><td>1, box, 23<br />2, clock, 70</td>
<td>4, 12/07/2014</td><td>some other reason</td><td></td>
</tr>