Jimmie Magnusson Jimmie Magnusson - 10 days ago 8
JSON Question

PHP JSON 2 dimensional array output

I've been stuck here for about 2 hours and I need some help.

What I'm trying to accomplish

I have a MySQL DB which has a table called

movies
and has rows such as
question
,
answer
,
category
. Where the category is important. Since each category should be printed as it's own array, with rows that has the value of that category.

So all rows with category
Two and a Half Men
should display in the
Two and a Half Men
array beneath.

Example:

[
{
"Arrow": [
"question:","this is a question"
"answer","this is an answer"
],
"How I Met Your Mother": [
"question:","this is a question"
"answer","this is an answer"
],
"South Park": [
"question:","this is a question"
"answer","this is an answer"
],
"The Big Bang Theory": [
"question:","this is a question"
"answer","this is an answer"
],
"The Last Man On Earth": [
"question:","this is a question"
"answer","this is an answer"
]
}
]


What I have as JSON output:

[
{
"Arrow": [
"question without the key value(only the string..)"
],
"How I Met Your Mother": [
"question without the key value(only the string..)"
],
"South Park": [
"question without the key value(only the string..)"
],
"The Big Bang Theory": [
"question without the key value(only the string..)"
],
"The Last Man On Earth": [
"question without the key value(only the string..)"
],
"Two and a Half Men": [
""
]
}
]


My code:

<?php

// Create connection
$con=mysqli_connect("localhost","username","password","db");

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "SELECT * FROM movies";


if ($result = mysqli_query($con, $sql))
{
$category = array();

while($thearray = mysqli_fetch_array($result))
{
// this is the part where it gets messy. ->
// more columns should show such as question, while the category is only the array.
$category[$thearray['category']] = [$thearray['question']];

array_push($category);

}

header('Content-Type: application/json');
echo json_encode(array($category));


}

// Close connections
mysqli_close($con);

?>


Sincerely, Jimmie!

Answer

Append a new element to the category array dynamically with []. You should use mysqli_fetch_assoc() to get only the column names as indexes. Also, if you want all of the columns that were selected, then just use all of $thearray:

while($thearray = mysqli_fetch_assoc($result)) {
    $category[$thearray['category']][] = $thearray;
}

Or for specific columns:

$category[$thearray['category']][] = ['question' => $thearray['question'],
                                      'answer' => $thearray['answer']];