Fetching constraints in SQL database to JSON in PHP

Question

Let's assume I have the following database structure of car manufacturers and the corresponding cars:

manufacturers:

----------------------------------
| manufacturer | founded_in | id |
|--------------|------------|----|
| Daimler AG   | 1927       | 1  |
| Volkswagen AG| 1937       | 2  |
----------------------------------

cars:

-------------------------------------
| car     | built_in | manufacturer |
|---------|----------|---------------
| C Class | 1993     | 1            |
| E Class | 1993     | 1            |
| Golf    | 1974     | 2            |
-------------------------------------

The id column of manufacturers is the primary key and the manufacturer column of cars has the corresponding foreign key constraint.

I'd like to produce the following JSON output using PHPs json_encode:

{
    "manufacturers": {
        "Daimler AG": {
            "founded in": "1927",
            "cars": [
                "C Class",
                "E Class"
            ]
        },
        "Volkswagen AG": {
            "founded in": "1937",
            "cars": [
                "Golf"
            ]
        }
    }
}

To get the manufacturers and their founded_in I'd just perform:

SELECT manufacturer, founded_in FROM manufacturers

And fetch them to an array. How do I assign the cars correctly though, after making a second query?

How? I'd need to assign multiple cars to one manufacturer in one join. — j.Doe
– j.Doe, Commented Mar 16, 2019 at 22:41

GMB · Accepted Answer · 2019-03-16 23:05:03Z

If you are using MySQL 5.7 or higher, you can use the JSON aggregate functions to generate the output you expect directly from a SQL query. I expect that will be more efficient than using php in between.

To start with, consider the following aggregates query, that JOINs both tables and creates a JSON object for each manufacturer, with the list of associated car names in a subarray:

SELECT JSON_OBJECT('founded in', m.founded_in, 'cars', JSON_ARRAYAGG(c.car)) js
FROM manufacturers m
INNER JOIN cars c ON c.manufacturer = m.id
GROUP BY m.id, m.founded_in;

This returns:

| js                                                   |
| ---------------------------------------------------- |
| {"cars": ["C Class", "E Class"], "founded in": 1927} |
| {"cars": ["Golf"], "founded in": 1937}               |

To produce the expected output, we can turn this to a subquery and add another level of aggregation:

SELECT JSON_OBJECT('manufacturers', JSON_OBJECTAGG(manufacturer, js)) myjson
FROM (
    SELECT 
        m.id, 
        m.manufacturer, 
        JSON_OBJECT('founded in', m.founded_in, 'cars', JSON_ARRAYAGG(c.car)) js
    FROM manufacturers m
    INNER JOIN cars c ON c.manufacturer = m.id
    GROUP BY m.id, m.manufacturer, m.founded_in
) x

This yields:

| myjson                                                                                                                                           |
| ------------------------------------------------------------------------------------------------------------------------------------------------ |
| {"manufacturers": {"Daimler AG": {"cars": ["C Class", "E Class"], "founded in": 1927}, "Volkswagen AG": {"cars": ["Golf"], "founded in": 1937}}} |

Demo on DB Fiddle.

jgoday · Accepted Answer · 2019-03-16 22:44:50Z

2

You can query all the data first

SELECT m.manufacturer, m.founded_in
  FROM manufacturers AS m
  JOIN cars AS c ON c.manufacturer_id = m.id

and then convert it to your structure in php

$manufacturers = [];
foreach ($rows as $row) {
    if (!array_key_exists($row['manufacturer'], $manufacturers)) {
        $manufacturers[$row['manufacturer']] = array(
           'founded_in' => $row['founded_in'],
           'cars' => array());
    }

    array_push($manufactureres[$row['manufacturer']]['cars'], $row['car']);
}

There is, for sure, a more functional way of doing this in php but you this should work ...

answered Mar 16, 2019 at 22:44

jgoday

2,8461 gold badge19 silver badges18 bronze badges

3 Comments

j.Doe Over a year ago

So are you suggesting that I should join both tables which have all rows in the manufacturer column filled, even though I only have two? Is that considered good practice?

jgoday Over a year ago

Well, it depends on how much data do you have. You can have two separate queries too, and fill the same structure.

paskl Over a year ago

As a practice you could say, let the database handle it all, since its made for stuff like that. If it costs too much, you could fill bogus values into a test database and run a benchmark.

nickl- · Accepted Answer · 2019-03-17 00:43:19Z

2

You can also use an object relational mapper like Respect/Relational

You would be able to simply call:

use Respect\Relational\Mapper;

$mapper  = new Mapper(new PDO($db_string, $db_user, $db_pass));
$results = $mapper->manufacturers->cars->fetchAll();
$json    = json_encode($results);

Leave the heavy lifting to the API.

answered Mar 17, 2019 at 0:43

nickl-

8,8004 gold badges44 silver badges58 bronze badges

Collectives™ on Stack Overflow

Fetching constraints in SQL database to JSON in PHP

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related