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 XI 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.
</div>
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.
Try it
select concat (table2.first," ",table2.middle ) as adder_id_Name,AddedName.* from table2,(SELECT concat (tb2.first," ",tb2.middle ) as added_id_Name, tb1.adder_id from table2 tb2,table1 tb1 where tb1.added_id = tb2.ID) as AddedName where table2.ID = AddedName.adder_id
I found out by myself. I strangled my brain and got this as the answer:
<?php
// 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 from_id, message, date, time FROM messages WHERE to_id='$ID'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$from_id=$row["from_id"];
$message=$row["message"];
$date=$row["date"];
$time=$row["time"];
$sql10 = "SELECT firstname, middlename, lastname FROM users WHERE ID='$from_id'";
$sql11 = mysql_query($sql10);
$sql12 = mysql_fetch_row($sql11);
$firstname=$sql12[0];
$middlename=$sql12[1];
$lastname=$sql12[2];
?>
<a href="#" class="list-group-item">
<span class="label label-default"><?php echo $date, ' ', $time; ?></span>
<span class="fa fa-star"></span>
<span class="name" style="min-width: 120px; display: inline-block;">Casper Thomsen</span>
<span class="text-muted" style="font-size: 11px;">- <?php echo $message; ?></span>
<span class="pull-right">
<button type="submit" id="view_message" name="view_message" class="btn btn-default btn-xs pull-right">Læs Besked <i class="fa fa-angle-right"></i></button>
</span>
</a>
<?php
}
} else {
echo '
<div class="block block-team_member_block span12 first cf">
<div class="member">
<div class="member-social">
</div>
<h3>No members or could not connect to database..</h3>
<div class="bline"></div>
</div>
</div>';
}
$conn->close();
?>