create json array from multiple mysql query

Question

for example i have two tables post table and user table, and from these i want to create a json array to pass bootstrap datatable.

My table structure. post table and user table. i want to generate an output table having post details and user details.but user name should be in drop down box.

<?php
include('config/db_i.php');

$sql_post = mysqli_query($con,"SELECT * FROM `post_tbl`");
$array = array();
$array['data'] = array();

while($res_post = mysqli_fetch_array($sql_post)){

    $sql_user = mysqli_query($con,"select * from user_tbl where user_id='".$res_post['user_id']."'");
    $row_user = mysqli_fetch_array($sql_user);

    //what i'll code here
    $array['data'][] = $res_post; 

}
echo json_encode($array);
?>

I'd have thought you would be able to do a single sql query but join the two tables using the user_id? — Professor Abronsius
– Professor Abronsius, Commented Aug 7, 2016 at 7:43

APIS · Accepted Answer · 2016-08-07 07:50:05Z

1

You can do the concatenation at query level by joining the two tables together as such :

Select ps.post_id, ps.post_title, us.user_id, us.user_name FROM post_tbl AS ps JOIN user_tbl AS us ON ps.user_id = us.user_id

That way each row of the fetched array will have the post info and the user info which you can freely pass to bootstrap datatable.

*You can also use NATURAL JOIN

answered Aug 7, 2016 at 7:50

APIS

1362 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

create json array from multiple mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related