kamanzi abubakar kamanzi abubakar - 4 months ago 6
JSON Question

How can i generate a json script out of these 3 Tables while using a php script

I want to join these 3 tables once in one query , after doing that i use php to come out with a json file in the web browser(localhost)

Below is the decription of the pictures;
1.Is the

Movie Table

2 Is the
Cinemax Table

3. Is the
Showtime Table


This is my Movies Table in the picture below

This is my Cinemax Table

This is my ShowTime Table

Below is the output of JSON script i want with those fields to output from the php script after joining all the 3 tables in Mysql.

[{
"Cinemax":"Ntinda", "Contacts":"0704594180","Movie":[{"MovieName":"Zootopia", "ShowTime":[
{"Monday":"1:30 p.m , 3:40 p.m",
"Tuesday":"1:30 p.m , 3:40 p.m",
"Wednesday":"1:30 p.m , 3:40 p.m",
"Thursday":"1:30 p.m , 3:40 p.m",
"Friday":"1:30 p.m , 3:40 p.m",
"Saturday":"1:30 p.m , 3:40 p.m",
"Sunday":"1:30 p.m , 3:40 p.m"},
{"MovieName":"Ice Age 3", "ShowTime":[
{"Monday":"4:30 p.m , 6:40 p.m",
"Tuesday":"1:30 p.m , 3:40 p.m",
"Wednesday":"1:30 p.m , 3:40 p.m",
"Thursday":"1:30 p.m , 3:40 p.m",
"Friday":"1:30 p.m , 3:40 p.m",
"Saturday":"1:30 p.m , 3:40 p.m",
"Sunday":"1:30 p.m , 3:40 p.m"}

], "Synopsis:"Its really a nice animation movie", "photo":"zootopia.jpg",
"others":"Animation"},

"Cinemax":"Kisementi", "Contacts":"0700304850","Movie":[{"MovieName":"Zootopia","ShowTime":[
{"Monday":"1:30 p.m , 3:40 p.m",
"Tuesday":"1:30 p.m , 3:40 p.m",
"Wednesday":"1:30 p.m , 3:40 p.m",
"Thursday":"1:30 p.m , 3:40 p.m",
"Friday":"1:30 p.m , 3:40 p.m",
"Saturday":"1:30 p.m , 3:40 p.m",
"Sunday":"1:30 p.m , 3:40 p.m"},
{"MovieName":"Ice Age 3", "ShowTime":[
{"Monday":"4:30 p.m , 6:40 p.m",
"Tuesday":"1:30 p.m , 3:40 p.m",
"Wednesday":"1:30 p.m , 3:40 p.m",
"Thursday":"1:30 p.m , 3:40 p.m",
"Friday":"1:30 p.m , 3:40 p.m",
"Saturday":"1:30 p.m , 3:40 p.m",
"Sunday":"1:30 p.m , 3:40 p.m"}

], "Synopsis:"Its really a nice animation movie", "photo":"zootopia.jpg",
"others":"Animation"}

}]


This is my current Php script, so how can i try to get that format of Json

<?php
$username="root";
$password="namungoona";
$hostname = "localhost";
//connection string with database
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
echo "";
// connect with database
$selected = mysql_select_db("UgandaEntertainment",$dbhandle)
or die("Could not select examples");
//query fire
$result = mysql_query("select Movie.MovieName,

Movie.Synopsis,
Movie.photo,
Movie.Others,
Cinemax.CinemaName,
Cinemax.Longitude,
Cinemax.Latitude,
Cinemax.Contact,
Cinemax.CinemaxPhoto,
ShowTime.Monday,
ShowTime.Tuesday,
ShowTime.Wednesday,
ShowTime.Thursday,
ShowTime.Friday,
ShowTime.Saturday,
ShowTime.Sunday,
Actor.ActorsName,
Actor.ActorsPhoto

from Movie
inner join Cinemax
ON Movie.CinemaxId=Cinemax.CinemaxId
inner join ShowTime
ON Movie.MovieId=ShowTime.MovieId

inner join Actor
ON Movie.MovieId=Actor.MovieId

;


");
$json_response = array();
// fetch data in array format
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
// Fetch data of Fname Column and store in array of row_array
$row_array['MovieName'] = $row['MovieName'];
$row_array['Synopsis'] = $row['Synopsis'];
$row_array['Others'] = $row['Others'];
$row_array['CinemaName'] = $row['CinemaName'];
$row_array['photo'] = $row['photo'];
$row_array['CinemaxPhoto'] = $row['CinemaxPhoto'];
$row_array['Longitude'] = $row['Longitude'];
$row_array['Latitude'] = $row['Latitude'];
$row_array['Contact'] = $row['Contact'];
$row_array['Monday'] = $row['Monday'];
$row_array['Tuesday'] = $row['Tuesday'];
$row_array['Wednesday'] = $row['Wednesday'];
$row_array['Thursday'] = $row['Thursday'];
$row_array['Friday'] = $row['Friday'];
$row_array['Saturday'] = $row['Saturday'];
$row_array['Sunday'] = $row['Sunday'];
$row_array['ActorsName'] = $row['ActorsName'];
$row_array['ActorsPhoto'] = $row['ActorsPhoto'];



//push the values in the array
array_push($json_response,$row_array);
}
//
echo json_encode($json_response);
?>


This is my new OutPut For JSON

[{"Cinemax":"Ntinda Cinemax","Contacts":"0704594180","Movie":[{"MovieName":"Ice Age 3","ShowTime":{"Monday":"1:30 p.m , 3:40 p.m","Tuesday":"1:30 pm , 3:40 p.m","Wednesday":"1:30 p.m., 3:40 p.m","Thursday":"1:30 p.m., 3:40 p.m","Friday":"1:30 p.m., 3:40 p.m","Saturday":"1:30 p.m., 3:40 p.m","Sunday":"1:30 p.m., 3:40 p.m"},"Synopsis":"Manny, Diego and sid","photo":"ice_age_3.jpg","others":"cinema and comedy"}]},{"Cinemax":"Ntinda Cinemax","Contacts":"0704594180","Movie":[{"MovieName":"Ice Age 3","ShowTime":{"Monday":"1:30 p.m , 3:40 p.m","Tuesday":"1:30 pm , 3:40 p.m","Wednesday":"1:30 p.m., 3:40 p.m","Thursday":"1:30 p.m., 3:40 p.m","Friday":"1:30 p.m., 3:40 p.m","Saturday":"1:30 p.m., 3:40 p.m","Sunday":"1:30 p.m., 3:40 p.m"},"Synopsis":"Manny, Diego and sid","photo":"ice_age_3.jpg","others":"cinema and comedy"}]},{"Cinemax":"Ntinda Cinemax","Contacts":"0704594180","Movie":[{"MovieName":"Ice Age 3","ShowTime":{"Monday":"1:30 p.m , 3:40 p.m","Tuesday":"1:30 pm , 3:40 p.m","Wednesday":"1:30 p.m., 3:40 p.m","Thursday":"1:30 p.m., 3:40 p.m","Friday":"1:30 p.m., 3:40 p.m","Saturday":"1:30 p.m., 3:40 p.m","Sunday":"1:30 p.m., 3:40 p.m"},"Synopsis":"Manny, Diego and sid","photo":"ice_age_3.jpg","others":"cinema and comedy"}]}]


Me I want something like this , without a repaetition of the Same Cinemax

[{"Cinemax":"Ntinda Cinemax","Contacts":"0704594180",
"Movie":[{"MovieName":"Zootopia","ShowTime":{"Monday":"1:30 p.m , 3:40 p.m","Tuesday":"1:30 pm , 3:40 p.m","Wednesday":"1:30 p.m., 3:40 p.m","Thursday":"1:30 p.m., 3:40 p.m","Friday":"1:30 p.m., 3:40 p.m","Saturday":"1:30 p.m., 3:40 p.m","Sunday":"1:30 p.m., 3:40 p.m"},"Synopsis":"Manny, Diego and sid","photo":"ice_age_3.jpg","others":"cinema and comedy"}]},{"Cinemax":"Ntinda Cinemax","Contacts":"0704594180","Movie":[{"MovieName":"Ice Age 3","ShowTime":{"Monday":"1:30 p.m , 3:40 p.m","Tuesday":"1:30 pm , 3:40 p.m","Wednesday":"1:30 p.m., 3:40 p.m","Thursday":"1:30 p.m., 3:40 p.m","Friday":"1:30 p.m., 3:40 p.m","Saturday":"1:30 p.m., 3:40 p.m","Sunday":"1:30 p.m., 3:40 p.m"},"Synopsis":"Manny, Diego and sid","photo":"ice_age_3.jpg","others":"cinema and comedy"}]},{"Cinemax":"Ntinda Cinemax","Contacts":"0704594180","Movie":[{"MovieName":"Ice Age 3","ShowTime":{"Monday":"1:30 p.m , 3:40 p.m","Tuesday":"1:30 pm , 3:40 p.m","Wednesday":"1:30 p.m., 3:40 p.m","Thursday":"1:30 p.m., 3:40 p.m","Friday":"1:30 p.m., 3:40 p.m","Saturday":"1:30 p.m., 3:40 p.m","Sunday":"1:30 p.m., 3:40 p.m"},
"Synopsis":"Manny, Diego and sid","photo":"ice_age_3.jpg","others":"cinema and comedy"},{"MovieName":"Ice Age 3","ShowTime":{"Monday":"1:30 p.m , 3:40 p.m","Tuesday":"1:30 pm , 3:40 p.m","Wednesday":"1:30 p.m., 3:40 p.m","Thursday":"1:30 p.m., 3:40 p.m","Friday":"1:30 p.m., 3:40 p.m","Saturday":"1:30 p.m., 3:40 p.m","Sunday":"1:30 p.m., 3:40 p.m"},"Synopsis":"Manny, Diego and sid","photo":"ice_age_3.jpg","others":"cinema and comedy"}]},{"Cinemax":"Ntinda Cinemax","Contacts":"0704594180","Movie":[{"MovieName":"Ice Age 3","ShowTime":{"Monday":"1:30 p.m , 3:40 p.m","Tuesday":"1:30 pm , 3:40 p.m","Wednesday":"1:30 p.m., 3:40 p.m","Thursday":"1:30 p.m., 3:40 p.m","Friday":"1:30 p.m., 3:40 p.m","Saturday":"1:30 p.m., 3:40 p.m","Sunday":"1:30 p.m., 3:40 p.m"},"Synopsis":"Manny, Diego and sid","photo":"ice_age_3.jpg","others":"cinema and comedy"}]},{"Cinemax":"Ntinda Cinemax","Contacts":"0704594180","Movie":[{"MovieName":"Ice Age 3","ShowTime":{"Monday":"1:30 p.m , 3:40 p.m","Tuesday":"1:30 pm , 3:40 p.m","Wednesday":"1:30 p.m., 3:40 p.m","Thursday":"1:30 p.m., 3:40 p.m","Friday":"1:30 p.m., 3:40 p.m","Saturday":"1:30 p.m., 3:40 p.m","Sunday":"1:30 p.m., 3:40 p.m"},
"Synopsis":"Manny, Diego and sid","photo":"ice_age_3.jpg","others":"cinema and comedy"}]}]

Answer

You can try this:

$json_response = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $movieArray = array(
        "MovieName" => $row['MovieName'],
        "ShowTime" => array(
            "Monday" => $row['Monday'],
            "Tuesday" => $row['Tuesday'],
            "Wednesday" => $row['Wednesday'],
            "Thursday" => $row['Thursday'],
            "Friday" => $row['Friday'],
            "Saturday" => $row['Saturday'],
            "Sunday" => $row['Sunday']
        ),
        "Synopsis" => $row['Synopsis'], 
        "photo" => $row['photo'], 
        "others" => $row['Others']
    );
    $cinema = array(
        "Cinemax" => $row['CinemaName'],
        "Contacts" => $row['Contact'],
        "Movie" => array($movieArray)
    );
    $key = array_search($row['CinemaName'], array_column($json_response, 'Cinemax'));
    if($key !== false)
    {
        $json_response[$key]["Movie"][] = $movieArray;
    } else {
        $json_response[]= $cinema;
    }
}
var_dump(json_encode($json_response));

And returns something like this (you can add other fields):

[{"Cinemax":"CinemaRX","Contacts":"0731565131","Movie":[{"MovieName":"Suicide Squad0","ShowTime":{"Monday":"10:000","Tuesday":"11:000","Wednesday":"12:000","Thursday":"13:000","Friday":"14:000","Saturday":"15:000","Sunday":"16:000"},"Synopsis":"Bla bla tests0","photo":"test.jpg0","others":"others0"},{"MovieName":"Suicide Squad1","ShowTime":{"Monday":"10:001","Tuesday":"11:001","Wednesday":"12:001","Thursday":"13:001","Friday":"14:001","Saturday":"15:001","Sunday":"16:001"},"Synopsis":"Bla bla tests1","photo":"test.jpg1","others":"others1"}]}]

Comments