CODEWITHSUNDEEP
phpmysqlsql-serverjsonmysqli
David Addoteye
David Addoteye

Reputation: 1641

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

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.

Upvotes: 0

Views: 1190

Answers (1)

O. Jones
O. Jones

Reputation: 108651

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;

Upvotes: 1

Related Questions