CODEWITHSUNDEEP
phpsqljsonmysqlirow
Victor G
Victor G

Reputation: 11

Select rows from two tables to json array

I am a beginner developer and it is very difficult for me to understand as it to make. Please help me.

catbook 

+----+---------+------------+
| id | cat_num | count_book |
+----+---------+------------+
|  1 |   55555 |         21 |
|  2 |   77777 |         40 |
+----+---------+------------+

userbook

+----+-----------+--------------+------------+
| id | user_name | cat_userbook | read_count |
+----+-----------+--------------+------------+
|  1 | Andy      |        55555 |          3 |
|  2 | Andy      |        77777 |          5 |
|  3 | Tom       |        55555 |          4 |
|  4 | Tom       |        77777 |          8 |
+----+-----------+--------------+------------+

Code

<?php
require_once("config.php");

$user_name = "Andy";

$sql ="SELECT cat_userbook, read_count, count_book FROM userbook, catbook WHERE user_name LIKE '" . $user_name . "'";
$result = mysqli_query($db_connect, $sql);
$response = array();

while ($row = mysqli_fetch_assoc($result)) {

    $response[] = $row;
}
echo json_encode($response);
mysqli_close($db_connect);

result:

[
 {"cat_userbook":"55555","read_count":"3","count_book":"21"},
 {"cat_userbook":"55555","read_count":"3","count_book":"40"},
 {"cat_userbook":"77777","read_count":"5","count_book":"21"},
 {"cat_userbook":"77777","read_count":"5","count_book":"40"}
]

but I want this result as it is necessary for me so:

[
 {"cat_userbook":"55555","read_count":"3","count_book":"21"},
 {"cat_userbook":"77777","read_count":"5","count_book":"40"}
]

Upvotes: 1

Views: 202

Answers (1)

Frivolin
Frivolin

Reputation: 41

If you are joining like this: userbook, catbook u get userbook x catbook

I think what you are looking for is:

SELECT cat_userbook, read_count, count_book FROM userbook LEFT JOIN catbook ON catbook.catNum = userbook.cat_userbook WHERE user_name LIKE '" . $user_name . "'";

Upvotes: 1

Related Questions