mysqli query echo data from two tabels

Question

I have two tables:

bb_users ( user_id, user_name, user_password .... )
bb_topics ( id, time, topic, sender, reciever)

sender and reciever are related to user_id in the bb_users table. For example, sender 450 = user_id 450 = user_name "demouser".

When I export the data from the tables, I want to replace receiver and sender with their user_name.

<?php


$con=mysqli_connect("$DB_HOST","$DB_USER","$DB_PASS","$DB_NAME");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,' SELECT * FROM bb_topics LIMIT 3000');

echo "<table border='1'>
<tr>
<th>id</th>
<th>time</th>
<th>topic</th>
<th>sender</th>
<th>receiver</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>"  . $row['time'] . "</td>";
echo "<td>" . $row['topic'] . "</td>";
echo "<td>" . $row['sender'] . "</td>";
echo "<td>" . $row['receiver'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

The export looks like:

id   | time        | topic      |  sender    | receiver
1    | 1580471226  | demo topic |    320     |  150

user_id of sender 320, his user_name in bb_users = "demotest", receiver = "demo2".

I used LIMIT 3000 because there is a lot of data to be exported. Sometimes I got a PHP fatal error.

How can I convert sender and receiver to their real username, and how to convert time from timestamp to date?

Welcome to Stack Overflow. I would advise a more complex SQL Query that makes use of JOIN. You could also perform a 2nd query to resolve the Names to the IDs. Send the Time back as is, and create a new Date object in JavaScript/jQuery that can display the time local to the browser. You can do it in PHP too if you choose. I would also move away from outputting an HTML Table and consider moving to JSON data. In this way you can use the PHP Script like an API and get raw data from it via AJAX. — Twisty, Feb 18 '20 at 01:18
You must enable mysqli exceptions. See https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param — Dharman, Feb 18 '20 at 09:57

score 0 · Accepted Answer · answered Feb 18 '20 at 03:01

I would advise a more complex SQL Query.

Example: http://sqlfiddle.com/#!9/df5e0e8/14/0

SELECT t.id, FROM_UNIXTIME(t.time, "%Y-%m-%d %H:%i:%s") AS time, t.topic, u1.user_name AS sender, u2.user_name AS receiver 
FROM bb_topics AS t 
LEFT JOIN bb_users AS u1 ON u1.user_id = t.sender 
LEFT JOIN bb_users AS u2 ON u2.user_id = t.receiver;

The output would be:

| id |                time |      topic |   sender | receiver |
|----|---------------------|------------|----------|----------|
|  1 | 2020-01-31 11:47:06 | demo topic | hsimpson | msimpson |

You can also send the Epoch Time to JavaScript if you want.

mysqli query echo data from two tabels

1 Answers1