0

I need to display comments with responds from database because i need asynchronous connection i need to transfer mysql output for json array.

So, i have two tables in my DB, one cold comments and second subcomment. Code i use for display

$sql = "SELECT comments.* , subcomment.comment AS subcom FROM comments left join subcomment on comments.id = subcomment.forid ";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // output data of each row
        $outp = array();
        $outp = $result->fetch_all(MYSQLI_ASSOC);
} else {
    echo "0 results";
}

But that code generate me json with multiply comments, so if one comment have 10 responds my output will have 10 comments with 10 respond, looking like that:

[{
"id":583,
"user_uid":"xxx",
"video_id":"stackoverflow",
"comment":"what did you try?",
"created":"2019-02-19 11:43:15",
"subcom":"nothing special"
},{
"id":583,
"user_uid":"xxx",
"video_id":"stackoverflow",
"comment":"what did you try?",
"created":"2019-02-19 11:43:15",
"subcom":"my sql commands"
},{
"id":583,
"user_uid":"xxx",
"video_id":"stackoverflow",
"comment":"what did you try?",
"created":"2019-02-19 11:43:15",
"subcom":"php? or json"}]

What do i need? Something like that:

 {
"id":583,
"user_uid":"xxx",
"video_id":"stackoverflow",
"comment":"what did you try?",
"created":"2019-02-19 11:43:15",
"subcom":{
    sub1: "nothing special" , 
    sub2: "my sql commands" , 
    sub3: "php? or json"
}}

Or:

 {
"id":583,
"user_uid":"xxx",
"video_id":"stackoverflow",
"comment":"what did you try?",
"created":"2019-02-19 11:43:15",
"subcom":["nothing special" , "my sql commands" , "php? or json"]
}

I will be glad if someone will show me the way to do it :D

1 Answer 1

1

You need to map master-detail relationship ("comments" and "subcomments") onto memory structure.

You method of fetching master-detail records is a bit ineffective, because you load whole master row for each detail row. I would recommend splitting solution in two halves, like querying comments first, and querying only subcomments next (see below).

But, basically, given query that you provide, you need to extract master data only once, and then add detail record to the master record if you already have that master record.

To keep track of what master records were already added to the result array, I would use primary key of master record as array key, because primary key allows to unambiguously identify that record.

// Less effective solution - do everything in one query

$sql = "SELECT comments.* , subcomment.comment AS subcom FROM comments left join subcomment on comments.id = subcomment.forid ";
$result = $conn->query($sql);
$outp = array();
foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) {
    // we will use primary key as $outp array index
    $key = $row['id'];  
    if (!isset($outp[$key])) { 
        // this is first time we encountered the master record with that key

        // we need to convert detail row into an array
        $row['subcom'] = array($row['subcom']);

        // save the master record using primary key as index
        $outp[$key] = $row;
    } else {
        // we already encountered master item with key $key,
        // so add another comment to the master record that already exist

        $outp[$key]['subcom'][] = $row['subcom'];
    }
}

// you may want to strip record keys from output array, 
// otherwise you will receive hash instead of JSON array 
// because keys may be non-sequential and json_encode will interpret 
// array as "associative" instead of "numeric"
$outp = array_values($outp);

But if I would try to make more effective solution, I would solve it this way:

// More effective solution - use two queries

$masterQuery = $conn->query("SELECT comments.* FROM comments");
$masterItems = $conn->query($masterQuery)->fetch_all(MYSQLI_ASSOC);
$byKeys = array();
foreach ($masterItems as $row) {
    $row['subcom'] = array(); // create array for detail items
    $byKeys[$row['id']] = $row;    
}

// note: if your keys come from user input, or are not guaranteed 
// to be numbers, you will need to sanitize and/or escape 
// or quote them on this step

// "forid IN (1, 2, 3)"
$where = 'forid IN ('.implode(', ', array_keys($byKeys)).')'; 

$detailQuery = "SELECT comment, forid FROM subcom WHERE {$where}";
$detailItems = $conn->query($detailQuery)->fetch_all(MYSQLI_ASSOC);
foreach ($detailItems as $row) {
    $masterKey = $row['forid'];
    $byKeys[$masterKey]['subcom'][] = $row['comment'];
}

// strip keys from result
$outp = array_values($byKeys);

Code examples above are to illustrate the principle, so they are not guaranteed to work if you will just copy-and-paste them.

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

1 Comment

i already start splinting like your 2nd opt. but your answer help me to do it faster :D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.