PHP/MYSQLi SQL SELECT on multiple table and return the result as json object

Question

I am building user status app where other can comment. I fetch the json result through ajax and then display it in my ap. however my problem is when i run the query it return only one run for each replies even though there are multiple replies for each status. Honestly i dont know whether the problem is from the PHP loop or it is from the SQL queries it self. I have battling with this since two days now and have search other Stackoverflow question for help but still. I will be glad if anyone can help me. Thank you.

User status are saved in the Status table and all replies on each status is saved in the replies table.

These are my tables

Users Table = gcm_users

   id
   name
   date

Status Table = gcm_status

   id
   userID
   status

Replies Table = gcm_status_replies

   id
   statusID
   userID
   replies

This is my sql statement

$sqlSelect = ( 'SELECT gcm_status.status,
    gcm_status.id,
    gcm_status.userID,
    gcm_status_replies.id,
    gcm_status_replies.statusID,
    gcm_status_replies.userID,
    gcm_status_replies.message,
    gcm_users.id,
    gcm_users.name

    FROM gcm_users INNER JOIN gcm_status
    ON gcm_users.id = gcm_status.userID
    INNER JOIN gcm_status_replies
    ON gcm_status.id = gcm_status.id'
    array(''), $conn);

PHP Loops is

       foreach ($sqlSelect as $row) {
             $respohnds = json_encode($row);
             echo $respohnds;
           }

I really need to help.

@RandomSeed PHP, please kindly check the update question. I have corrected some of the table columns. I am using PDO to connect to the database — David Addoteye
– David Addoteye, Commented Dec 29, 2014 at 14:13
Show the code please. As it stands the loop iterates over a hardcoded array of 3 items, not a query result. — RandomSeed
– RandomSeed, Commented Dec 29, 2014 at 14:19
Run the query in a console or any other client to confirm the SQL is fine. — RandomSeed
– RandomSeed, Commented Dec 29, 2014 at 14:22
@RandomSeed yeah, the sql is returning results when i console.log it with javascript but as i said. i only get only one object from replies table on each status column — David Addoteye
– David Addoteye, Commented Dec 29, 2014 at 14:26

O. Jones · Accepted Answer · 2014-12-29 22:27:11Z

1

You're generating a separate json object for each row, and returning them one by one.

Maybe you should do this instead to return a single json object encoding your whole result set as an array.

$respondhs = array();
foreach ($sqlSelect as $row) {
     $respohnds[] = json_encode($row);
}
echo $respohnds;

answered Dec 29, 2014 at 22:27

O. Jones

110k17 gold badges134 silver badges187 bronze badges

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

1 Comment

David Addoteye Over a year ago

Thank you @Ollie it works, I only move the json_encode outside the loop

Collectives™ on Stack Overflow

PHP/MYSQLi SQL SELECT on multiple table and return the result as json object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related