Select rows from two tables to json array

Question

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"}
]

You need to tell DB server to return only lines where cat_num = cat_userbook. Learn something about JOINS — Marek Vitek
– Marek Vitek, Commented Jun 16, 2017 at 9:47

Frivolin · Accepted Answer · 2017-06-16 09:43:21Z

1

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 . "'";

answered Jun 16, 2017 at 9:43

Frivolin

415 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Victor G Over a year ago

You so quickly repelled. This is a working solution for me. Thank you very much

david valentino Over a year ago

where is the to json array from 2 joining table?

Collectives™ on Stack Overflow

Select rows from two tables to json array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related