从table1中选择3个值并使用table2中table1中的一个值来获取结果？

Question

Hi Stackoverflow members!

I got this dilemma:

users  ID  username  firstname  middlename lastname
        1  bilbodog    Casper               Thomsen
        2  bilbodog2   Smith      John      Andersen


flirts    flirter_id  flirted_id  date  time
              1          2          X    X

I wanna get all the flirted_id's made by the flirter_id '1' and compare the flirted_id to users and gather the firstname, middlename & lastname from the flirted_id. Plus I wanna gather the date and time from the flirts table. Then I wanna echo the results out in a HTML table.

So for now I am doing this:

<?php
$ID=$_SESSION['ID'];
// Create connection
$conn = new mysqli($mysql_hostname, $mysql_user, $mysql_password, $mysql_database);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT flirted_id, date, time FROM flirts WHERE flirter_id='$ID'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
  ----> ECHO TABLE <----
} else {
  echo 'No flirts found.';
}
$conn->close();
?>

But that does not work.

I am so stuck at this point. I dunno how to gather 3 results from 1 table and use one of the results to compare at table 2, to gather the firstname, middlename and lastname of the person that goes by that ID.

• Thanks in advance!

</div>

Answer 1

If I understand you correctly you want to do a join to grab the information from table1 and also the information about the person in table 2 who is listed as added_id in table1

select t1.date, t1.time, t2.first, t2.middle, t2.last
from table1 t1
join table2 t2 on (t1.added_id = t2.id)
where t1.adder_id = 1;

If you also want the information about the adder_id (assuming the persons are also stored in table2):

select t1.date, t1.time, t2.first, t2.middle. t2.last, t3.first, t3.middle, t3.last
from table1 t1
join table2 t2 on (t1.added_id = t2.id)
join table2 t3 on (t1.added_id = t3.id)
where t1.adder_id = 1;    

In this case you should give the person cols alias to identify them more cleary, e.g. t2.first as added_id_first, t3.first as adder_id_first and so on

Note I assume that there are no NULL values in the adder_id and added_id fields. If they can then use a LEFT JOIN instead of JOIN in the statements.