I need to connect 2 columns to 1 column, but I can't figure it out how to connect those columns. This is how it looks like:
Table(appointment)
-------------------------------------------
| id | home | salesman | buyer | date |
| 1 | 3 | 2 | 4 | 12-6-2016|
| 2 | 1 | 1 | 3 | 15-6-2016|
| 3 | 2 | 5 | 6 | 20-6-2016|
-------------------------------------------
Table(person)
---------------------------------------------------------
| id | name | email | phonenumber | permission |
| 1 | John | John@gmail.com | 12345678 | 1 |
| 2 | Jack | Jack@gmail.com | 12345678 | 1 |
| 3 | Henk | Henk@gmail.com | 12345678 | 0 |
| 4 | Mike | Mike@gmail.com | 12345678 | 0 |
| 5 | Tom | Tom@gmail.com | 12345678 | 1 |
| 6 | Ben | Ben@gmail.com | 12345678 | 0 |
---------------------------------------------------------
Table(home)
--------------------------------------
| id | salesman | price | city |
| 1 | 2 | 123000 | London |
| 2 | 1 | 123000 | New York |
| 3 | 5 | 123000 | Paris |
--------------------------------------
This is how I want to see it on my php page:
-------------------------------------------------
| home | salesman | buyer | date |
| Home in Paris | Jack | Mike | 12-6-2016|
| Home in London | John | Henk | 15-6-2016|
| Home in New York| Tom | Ben | 20-6-2016|
-------------------------------------------------
People with permission 1 are salesman, permission 0 are buyers.
So my question is, how can I change the numbers to the correct value? Without change the number in table appointment to values in the database...
I don't know much about SQL, so could someone please come with a solution that isn't an example and works with my tables and columns?
Edit:
Table(afspraak)
-------------------------------------------
| id | huis | verkoper | koper | datum |
| 1 | 3 | 2 | 4 | 12-6-2016|
| 2 | 1 | 1 | 3 | 15-6-2016|
| 3 | 2 | 5 | 6 | 20-6-2016|
-------------------------------------------
Table(persoon)
---------------------------------------------------------
| id | naam | email | phonenumber | rechten |
| 1 | John | John@gmail.com | 12345678 | 1 |
| 2 | Jack | Jack@gmail.com | 12345678 | 1 |
| 3 | Henk | Henk@gmail.com | 12345678 | 0 |
| 4 | Mike | Mike@gmail.com | 12345678 | 0 |
| 5 | Tom | Tom@gmail.com | 12345678 | 1 |
| 6 | Ben | Ben@gmail.com | 12345678 | 0 |
---------------------------------------------------------
Table(huis)
--------------------------------------
| id | verkoper | prijs | stad |
| 1 | 2 | 123000 | London |
| 2 | 1 | 123000 | New York |
| 3 | 5 | 123000 | Paris |
--------------------------------------
My php code:
include('config.php');
$getAfspraak = "SELECT CONCAT('Home in ', HO.stad) AS Home,
SA.`naam` AS verkoper,
BU.`naam` AS koper,
AP.`datum`
FROM afspraak AP
INNER JOIN huis HO ON HO.id = AP.huis
INNER JOIN persoon SA ON SA.id = AP.verkoper AND SA.rechten = 1
INNER JOIN persoon BU ON BU.id = AP.koper AND BU.rechten = 0";
//Inner join for huis SELECT afspraak.huis, huis.stad FROM afspraak INNER JOIN huis ON afspraak.huis = huis.id; This query should show "huis in [stad]" it means, a home in London.
$dataAfspraak = mysqli_query($con, $getAfspraak) or die(mysqli_error($con));
<table cellspacing=1 border=0 width=100%>
<tr>
<th>Koper</th>
<th>Verkoper</th>
<th>Huis</th>
<th>Datum</th>
</tr>
<?php while($resAfspraak = mysqli_fetch_assoc($dataAfspraak)): ?>
<tr>
<td><?php echo $resAfspraak['koper']?></td> <!-- value is naam if the row has 0 at rechten -->
<td><?php echo $resAfspraak['verkoper']?></td> <!-- value is naam if the row has 1 at rechten -->
<td><?php echo $resAfspraak['huis']?></td>
<td><?php echo $resAfspraak['datum']?></td>
</tr>
<?php endwhile;?>