How to collect data from two related tables

Question

I am trying to get data from two different tables: users and comments. I want to return comment data and user data depending on the commentsenderid .

<?php
$commentpostid = $_POST['commentpostid'];
$sql = "SELECT * FROM comments where commentpostid = '{$commentpostid}'";
$sqll = mysqli_query($db, $sql); // $db->query() is also accepted.
$result = mysqli_fetch_all($sqll, MYSQLI_ASSOC);
echo json_encode($result);

Result:

[
  {
    "commentid": "93",
    "comment": "naber kankam Benin",
    "commentpostid": "1006",
    "commentsenderid": "12"
  },
  {
    "commentid": "95",
    "comment": "kankam selam!",
    "commentpostid": "1006",
    "commentsenderid": "3135"
  }
]

Should be:

[
  {
    "commentid": "93",
    "comment": "naber kankam Benin",
    "commentpostid": "1006",
    "commentsenderid": "12",
    "username": "denemeuser",
    "userbolum": "denemebolum",
    "useryas": "useryasdeneme"
  },
  {
    "commentid": "95",
    "comment": "kankam selam!",
    "commentpostid": "1006",
    "commentsenderid": "3135",
    "username": "denemeuser",
    "userbolum": "denemebolum",
    "useryas": "useryasdeneme"
  }
]

[ phpMyAdmin structure ] [ JSON structure ]

of i understood right, the tables are connected via FK, so why not just use a simple join? — vlad katz, Dec 16 '20 at 08:36
i tried to do it using joins but i couldn't, it didnt return it as a json — Newbie Developer, Dec 16 '20 at 08:41
JOIN will connect you with the data in the database, and return you a complete set of data. Whether you will convert this data to JSON is a completely separate matter. — Gander, Dec 16 '20 at 08:48
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 17 '20 at 19:26

score 1 · Answer 1 · answered Dec 16 '20 at 08:56

1

You select all comments, join them with users whose id matches the id of the authors of comments, you select only those whose post matches the value you are looking for.

SELECT c.*, u.username, u.userbolum, u.useryas
FROM comments c
         JOIN users u ON c.commentsenderid = u.userid
WHERE commentpostid = ?

answered Dec 16 '20 at 08:56

Gander

1,854
1
23
30

THANKS THIS WORKED; SELECT*FROM comments INNER JOIN users ON comments.commentsenderid = users.userid WHERE commentpostid = '".$commentpostid."' – Newbie Developer Dec 16 '20 at 15:05
1

btw: `JOIN` = `INNER JOIN` @NewbieDeveloper If this solution helped you, please click that this is the solution to your question. – Gander Dec 16 '20 at 15:16

How to collect data from two related tables

1 Answers1