PHP JSON 2 dimensional array output

Question

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!

AbraCadaver · Accepted Answer · 2016-11-28 19:51:20Z

1

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']];

edited Nov 28, 2016 at 19:51

answered Nov 28, 2016 at 19:42

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

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

4 Comments

onceuponaduck Over a year ago

Alright, your second one worked. How it looks now: gyazo.com/6612f52889a9e0ff958250bdae67c041 But how can I add the key value? I believe something like [$thearray['Question'=>'question']], but that didnt work :/

onceuponaduck Over a year ago

Thanks btw, your small change of my piece of code was greatly functional. Many thanks!

AbraCadaver Over a year ago

That should cover the bases.

onceuponaduck Over a year ago

It worked! Thank you very much for the help! Happy coding mate :')

Collectives™ on Stack Overflow

PHP JSON 2 dimensional array output

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related