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
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.