CODEWITHSUNDEEP
phpmysqlarraysmultidimensional-arraypdo
sensation
sensation

Reputation: 437

Multidimensional array without repeating

I have two MySQL tables, one is called "version" and the other is called "lines" inside one DB called "project".

The "version" table consist of: 

id (PRIMARY / AI) = int
version           = string

Rows:

# | version
------------
0 | 100
1 | 200
3 | 400

The "lines" table consist of:

id (PRIMARY / AI) = int
lines             = string
version_id        = string (ID from table version)

Rows:

# | line     | version_id
--------------------------
0 | line #1  | 0
1 | line #2  | 0
2 | line #3  | 1
3 | line #4  | 0
4 | line #5  | 1

How can I create multidimensional array to output an example JSON (pseudo) 

"full" =>
    "version" => "100"
        "id"    => "0", (version id table)
        "line"  =>
            "row_0" => "line #1", (from lines table)
            "row_1" => "line #2",
            "row_2" => "line #4",
    "version" => "200"
        "id" => "1",
        "line" =>
            "row_0" => "line #3",
            "row_1" => "line #5",
    "version" => "300"
        "id" => "3",
        "line" => "EMPTY" (no lines for this version)
]

I rewrote the code a couple of times but I can't make it work. Either I stuck or I finish in infinite loop of errors. This is what I got for now: 

function returnJson() {
    $db = DB::query("SELECT * FROM version");

    foreach ($db as $row) {
        $i++;
        $lines = DB::Query("SELECT * FROM lines WHERE version_id=%i", $row['id']);

        // approach to nested array?
    }
}

I'm using MeekroDB so any approach to MySQL is offset. You can write an example in PDO if you are more familiar with it.

Upvotes: 2

Views: 81

Answers (2)

user5051310
user5051310

Reputation:

I assume that the array you want would look like this in php:

"full" =>
    "100" => array (
        "versionId"    => "0", (version id table)
        "line"  =>
            "row_0" => "line #1", (
            "row_1" => "line #2",
            "row_2" => "line #4"
    )
    , "200" => array (
        "versionId" => "1",
        "line" => array (
            "row_0" => "line #3",
            "row_1" => "line #5" )
    )
    , "300" => array (
        "versionId" => "3",
        "line" => array()
    )
]

Use a JOIN

SELECT v.id AS versionId, v.version l.id as linesId, l.lines
FROM version v
INNER JOIN lines l ON v.id = l.version_id

And then a loop with some if statement to build the array

$versions = array();

foreach($db as $row) {
  if (!isset($versions[$db["version"]]))
    $versions[$db["version"]] = array (
      "versionId" =>  $db["versionId"],
      "line" => array()
    );
  if (!empty($db["lines"]))
    $versions[$db["version"]][lines"][] = $db["lines"];
}

Upvotes: 1

vmachan
vmachan

Reputation: 1682

Try the accepted answer in this SO post which also deals with nested JSON data.

Also you may want to reduce your SQL to below and just use one loop instead of 2 nested loops as in the SO post above.

SELECT *
  FROM version
 INNER 
  JOIN lines
    ON version.id = lines.version_id

Hope this helps.

Upvotes: 0

Related Questions