Represent data in PHP with MYSQL in JSON

Question

I'm trying to represent data in this format with php and mysql database that I made, the format below is what I need to get:

{
"data": [
    {
        "id": "1",
        "name": "bad boys",
        "genres": [
            "action",
            "comedy"
        ]
    },
    {
        "id": "2",
        "name": "star wars",
        "genres": [
            "action",
            "Sci-Fi"
        ]
    },
    {
        "id": "3",
        "name": "titanic",
        "genres": "drama"
    }
  ]
}

but what I'm getting is this format:

{
"data": [
    {
        "id": "1",
        "name": "bad boys",
        "genres": "action"
    },
    {
        "id": "1",
        "name": "bad boys",
        "genres": "comedy"
    },
    {
        "id": "2",
        "name": "star wars",
        "genres": "action"
    },
    {
        "id": "2",
        "name": "star wars",
        "genres": "sci-fi"
    },
    {
        "id": "3",
        "name": "titanic",
        "genres": "drama"
    }
  ]
}

First I made a query to select movies from database:

public function readMovie() {
    $query = 
    'SELECT
        movie.id,
        movie.name,
        genre.name as genre_name
    FROM movie
    INNER JOIN movie_genre
    ON movie_genre.movie_id = movie.id
    INNER JOIN genre
    ON genre.id = movie_genre.genre_id';

    $stmt = $this->conn->prepare($query);

    $stmt->execute();
    return $stmt;
}

then I write some code so I can convert it to an array:

$database = new Database();
$db = $database->connect();

$movie = new Movie($db);

$result = $movie->readMovie();

$num = $result->rowCount();

if($num > 0){
    $movies_arr = array();
    $movies_arr['data'] = array();

    while ($row = $result->fetch(PDO::FETCH_ASSOC)){
       extract($row);

       $movie_item = array(
           'id' => $id,
           'name' => $name,
           'genres' => $genre_name
       );
      array_push($movies_arr['data'], $movie_item);
}

echo json_encode($movies_arr);

} else {
   echo json_encode(
    array('message' => 'No Movie Found')
  );
}

this is my schema click here

I made this api to using it in flutter app

if anyone can help me please

you can't have that "array inside the object" because in SQL data is represented as a table, so the result set is always a table, and tables don't have nested table in them — Alberto
– Alberto, Commented Jun 15, 2020 at 21:35

GMB · Accepted Answer · 2020-06-15 21:40:08Z

3

MySQL 5.7 added proper JSON support, which makes it possible to generate the resultset that you want directly in the database:

select json_arrayagg(
    json_object('id', m.id, 'name', m.name, 'genres', x.genres)
) res
from movie m
inner join (
    select movie_id, json_arrayagg(name) genres
    from movie_genre mg
    inner join genre g on g.id = mg.genre_id
    group by movie_id
) x on x.movie_id = m.id

This works with two levels of aggregation: the subquery associates an array of genres to each movie, then the outer query builds an object for each movie, and aggregates all objects in an array.

You get a scalar resultset (1 row and 1 column called res), that contains the expected json payload.

answered Jun 15, 2020 at 21:40

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Represent data in PHP with MYSQL in JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related