To explain my question, I have 3 tables of information pertaining to beverage manufacturers. They are similar to as follows:
t_manufacturers
___________________________________
| ID | name | phone |
| 1 | Coke | 123456 |
| 2 | Pepsi | 654321 |
___________________________________
t_drinks
___________________________
| ID | name |
| 2 | cherry Pepsi |
| 1 | cherry coke |
___________________________
t_brandlogos
__________________________
| ID | imagename |
| 1 | original_coke |
| 2 | original_pepsi |
| 2 | cherry_pepsi |
| 1 | cherry_coke |
__________________________
And I would like to query this data so I can get a json file containing each manufacturer with all their brand logo/images and drinks that are available, matching the IDs between tables.
I would imagine and hope the ‘pretty’ json would read similar to this if I queried for just the coke information:
[
{
“ID”:1,
“name”:”coke”,
“drinks”:
[{“name”:”cherry coke”},
{“name”:”coke”}],
“images”:
[{“imagename”:”original_coke”},
{“imagename”:”cherry_coke”}]
]
I am using PHP to run the query from my website and return a json. The problem I am having is that I’m not sure how to write the MySQL query logic to get the results into the php so that the php can then return the data in a json format.
To be more clear, I can make it do something, but I can’t seem to get the query/php to structure the outgoing json correctly with all the data containing no duplicate information.
Any guidance would be appreciated.
EDIT ***
I am thinking that the way to do this would be running 3 query’s in the PHP and end up with an array of the beverage manufacturers with arrays of their drinks/images (as is suggested by my desired json output).
To do this I imagine I’d query the beverage manufacturers for their ID/name and then for each manufacture query their drink/logo arrays separately.
Is php capable of doing this though... it would be simple to do in like c++.
END EDIT***
NEW EDITE ***
So I did manage to get the output to arrays of arrays, however the issue I am having now is that instead of the output having the array of drinks/images like:
“drinks”:
[{“name”:”cherry coke”},
{“name”:”coke”}],
“images”:
[{“imagename”:”original_coke”},
{“imagename”:”cherry_coke”}]
]
Instead the "drinks" and "images" is replaced with a "0" and a "1".
Thus far how I am accomplishing this is to query for all the manufacturers and then for each manufacturer I query for all the drinks using the ID. I take the new array of the drinks and then attach it to the end of the current manufacture array, same thing for the images, then add the current manufacturer to the manufacturers array. I accomplish these loops with the idea in this smie-sudocode:
for_each_manufacturer{
for_each_drink{
array_push($drinkArray, $tempDrinkArray);
}
array_push($currentManufacturerArray, $drinkArray);
for_each_image{
array_push($imageArray, $tempImageArray);
}
array_push($currentManufacturerArray, $imagesArray);
array_push($manufacturerArray, $currentManufacturerArray);
}
The issue I want to resolve now(since I am getting the proper output format) is to change that "0" and "1" to "drinks" and "images" respectively.
Thanks for everyone who has been helping so far!
END NEW EDIT ***
I was able to resolve that by just adding to the array a copy of the old data, but with a different key and then dropping the data under the old key.
$currentManufacturerArray["images"] = $currentManufacturerArray["1"];
unset($currentManufacturerArray["1"]);
Hi Charles and welcome to Stack Overflow.
I would recommend to split it into three distinct queries, first to fill in the companies, second to add the drinks and a third to finally add the images.
As you are new to PHP, here's a bit of guidance how you may structure your result:
// define the response array
$responseArray = [];
// Step 1: fill in the companies, using the company ID as array key ("1" in the demo)
// replace this with your first mysqli_query();
// we will need the empty arrays later on.
$responseArray[1] = [
'ID' => 1,
'name' => 'Coca Cola',
'drinks' => [],
'images' => []
];
// Step 2: push in the drinks into the "drinks" sub-array, using the company ID and 'drinks' as array keys ("1" in the demo)
// this would happen in your second mysqli_query();
$responseArray[1]['drinks'][] = ['name' => 'Cherry Cola'];
$responseArray[1]['drinks'][] = ['name' => 'Coca cola Coke'];
// Step 2: push in the images into the "drinks" sub-array, using the company ID and 'images' as array keys ("1" in the demo)
// this would happen in your third mysqli_query();
$responseArray[1]['images'][] = ['imagename' => 'original_coke'];
$responseArray[1]['images'][] = ['imagename' => 'cherry_coke'];
// output the result as JSON. The array_values(...) call makes sure we won't have the company ID as array key in our result
header('Content-Type: application/json');
print json_encode(array_values($responseArray));
Which would give you the following result:
[
{
"ID": 1,
"name": "Coca Cola",
"drinks": [
{
"name": "Cherry Cola"
},
{
"name": "Coca cola Coke"
}
],
"images": [
{
"imagename": "original_coke"
},
{
"imagename": "cherry_coke"
}
]
}
]
To loop over the companies table and fill in the values, you would use code similar to this:
// no error handling as this is just a functionality sample ...
$responseArray = [];
$connection = mysqli_connect('HOST', 'USER', 'PASS', 'DATABASE');
// add the companies ...
$query = '
SELECT
ID, name
FROM
t_manufacturers';
$result = mysqli_query($connection, $query);
while ($row = mysqli_fetch_assoc($result)) {
$responseArray[$row['ID']] = [
'id' => $row['ID'],
'company' => $row['name'],
'drinks' => [],
'images' => []
];
}
This would give you a result containing all companies. If you want to output just one company, use WHERE clauses as part of the three queries. In this simple demo you could also pick one company from the response array using:
print json_encode($responseArray[1]); // would give you the Coca section
Let me know if this helps you.