Creating Multi-Dimensional Array Using SQL data

Question

I am using a plugin that wants to be fed an array like this in javascript:

var data = [
    {
        "id": 1,
        "name": "University1",
        "list": [
            {"id": 1, "name": "Dorms", "list": 
                [
                    {"id": 1, "name": "Dorm1"},
                    {"id": 2, "name": "Dorm2"}
                ]
            },
            {"id": 2, "name": "Off-Campus", "list":
                [
                    {"id": 1, "name": "North Campus"},
                    {"id": 2, "name": "East Campus"}
                ]
            }
        ]
    },
    {
        "id": 2,
        "name": "University2",
        "list": [
            {"id": 1, "name": "Dorms", "list": 
                [
                    {"id": 1, "name": "Dorm1"},
                    {"id": 2, "name": "Dorm2"}
                ]
            },
            {"id": 2, "name": "Off-Campus", "list":
                [
                    {"id": 1, "name": "North Campus"},
                    {"id": 2, "name": "East Campus"}
                ]
            }
        ]
    }
];

My data for the array is in an SQL database. I am having trouble forming this multi-dimensional array in php and/or passing it with AJAX.

My javascript/jquery:

var locationsArray;
$.post( 
    'ajax/locationDropdown.php', 
    {
        //NO DATA THIS TIME
    },
    function (response) {
        console.log(response);
        parseResponse = $.parseJSON(response);
        var locationsArray = $.map(parseResponse, function(value, index) {
            return [value];
        });
        console.log(locationsArray);
    }
);

My php:

<?php 

include 'databaseConnection.php';

$sqlLD1 = '
    SELECT DISTINCT school
    FROM timeBlocks
    ORDER BY school ASC;
';

if (!$resultLD1 = $connection->query($sqlLD1)) {
    die ('There was an error running the queryLD1 [' . $connection->error . ']');
}

$locationArray = array(
    'id'=>array(),
    'name'=>array(),
    'list'=>array(
        'id'=>array(),
        'name'=>array(),
        'list'=>array(
            'id'=>array(),
            'name'=>array()
        )
    )
);
$i=0;
    while ($rowLD1 = $resultLD1->fetch_assoc()) {
        $school = $rowLD1["school"];
        $locationArray[$i][name] = $school;
        $sqlLD2 = '
            SELECT DISTINCT timeBlockLocation
            FROM timeBlocks
            WHERE school = "'.$rowLD1["school"].'"
            ORDER BY timeBlockLocation ASC;
        ';
        if (!$resultLD2 = $connection->query($sqlLD2)) {
            die ('There was an error running the queryLD2 [' . $connection->error . ']');
        }
            $j=0;
            while ($rowLD2 = $resultLD2->fetch_assoc()) {
                $timeBlockLocation = $rowLD2["timeBlockLocation"];
                $locationArray[$i][$j][name]=$timeBlockLocation;
                $sqlLD3 = '
                    SELECT DISTINCT timeBlockSubLocation
                    FROM timeBlocks
                    WHERE school = "'.$rowLD1["school"].'"
                    AND timeBlockLocation = "'.$rowLD2["timeBlockLocation"].'"
                    ORDER BY timeBlockSubLocation ASC;
                ';
                if (!$resultLD3 = $connection->query($sqlLD3)) {
                    die ('There was an error running the queryLD2 [' . $connection->error . ']');
                }
                    $k=0;
                    while ($rowLD3 = $resultLD3->fetch_assoc()) {
                        $timeBlockSubLocation = $rowLD3["timeBlockSubLocation"];
                        $locationArray[$i][$j][$k][name]=$timeBlockSubLocation;
                        $k++;
                }
                $j++;
        }
        $i++;
}

echo json_encode($locationArray);

?>

This is resulting in an array that looks like this:

{
  "0": {
    "0": {
      "0": {
        "name": "All Locations"
      },
      "name": "Off Campus"
    },
    "1": {
      "0": {
        "name": "Dorm1"
      },
      "1": {
        "name": "Dorm2"
      }
      "name": "Dorms"
    },
    "name": "University1"
  },
  "1": {
    "0": {
      "0": {
        "name": "All Locations"
      },
      "name": "Off-Campus"
    },
    "1": {
      "0": {
        "name": "Dorm1"
      },
      "name": "Dorms"
    }
    "name": "University2"
  },
  "id": [],
  "name": [],
  "list": {
    "id": [],
    "name": [],
    "list": {
      "id": [],
      "name": []
    }
  }
}

Don't Panic · Accepted Answer · 2017-01-31 17:33:05Z

1

From a performance standpoint, it's best to avoid executing queries in nested loops. Usually you can accomplish this by joining tables together. Fortunately, all of your results are coming from the same table, so you don't even have to do that. You can just run one simple query:

$sql = 'SELECT school, timeBlockLocation, timeBlockSubLocation
        FROM timeBlocks
        ORDER BY school, timeBlockLocation, timeBlockSubLocation';

$query_result = $connection->query($sql);

Then fetch your results using their values as array keys:

while ($row = $query_result->fetch_assoc()) {
    $schools[$row['school']][$row['timeBlockLocation']][$row['timeBlockSubLocation']] = 1;
    // (1 is meaningless, just a placeholder)
}

Using the values from the query result as array keys allows you to easily create the structure you need and prevents duplicate entries. This will get you an array like this:

$schools = [
    'University1' => [
        'Dorms' => ['Dorm1' => 1, 'Dorm2' => 1],
        'Off-Campus' => ['East Campus' => 1, 'North Campus' => 1]
    ],
    'University2' => [
        'Dorms' => ['Dorm1' => 1, 'Dorm2' => 1],
        'Off-Campus' => ['East Campus' => 1, 'North Campus' => 1]
    ]
];

Because each level of the end result you need has the same format, a recursive function will work well to convert the intermediate array to that format:

function reformat($array) {
    $id = 1;
    foreach ($array as $key => $value) {
        $branch = ['id' => $id++, 'name' => $key];

        // recursively reformat each level
        if (is_array($value)) $branch['list'] = reformat($value);

        $branches[] = $branch;
    }
    return $branches;
}

$locationArray = reformat($schools);

answered Jan 31, 2017 at 17:33

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

radleybobins Over a year ago

Wow, this is fantastic. I tried to do it something like this originally and just couldn't quite wrap my head around it. It's going to be a small list of locations forever, so I wasn't terribly concerned about performance, but this is definitely the way to do it. I might change answers here once I implement.

radleybobins Over a year ago

I switched it to your code, but I am receiving a Parse error: unexpected '[' on the line $branch = ['id' => $id++, 'name' => $key]; Any ideas?

Don't Panic Over a year ago

Probably you are using an older PHP version that doesn't support the short array syntax. Try using array('id' => ...) instead.

radleybobins Over a year ago

ah, this worked, thank you again, this is the best answer

charlietfl · Accepted Answer · 2017-01-31 16:59:20Z

1

I'm not going to completely rewrite this but rather point out a cleaner generic approach

Get rid of all the sub arrays in the declaration of $locationArray and simply do

$locationArray = array();

Then in your outer loop start a new array for each iteration. In the nested loops update the array started inside the outer loop, then at end of outer loop update main output array

while ($rowLD1 = $resultLD1->fetch_assoc()) {
        $school = array(
            'id' => $rowLD1["id"],
            'name'=> $rowLD1["school"],
            'list' => array()
        );
       // school query
       while ($rowLD2 = $resultLD2->fetch_assoc()) {
           // add to current school list array
           $school['list'][] = array(
                'prop1' => $rowLD2['prop1'],
                'prop2' => $rowLD2['prop2']
           );
       }

       // now add $school to main array
      $locationArray[] = $school;
}

echo json_encode....

edited Jan 31, 2017 at 16:59

answered Jan 31, 2017 at 16:54

charlietfl

172k14 gold badges125 silver badges154 bronze badges

5 Comments

radleybobins Over a year ago

oh, this makes a lot of sense, thanks for simplifying it. I'm going to try to fix the old code, just for the learning, but then I will likely convert it over to this. I will post back with results. thanks again!

radleybobins Over a year ago

This worked to get things in the right order. I had been thinking about how to build arrays wrong, thank you for the pointers.

radleybobins Over a year ago

I've had a chance to really dive into the result now, and I'm seeing that the lower-level arrays are actually objects. How do I specify that they should be arrays instead?

charlietfl Over a year ago

json_encode() will convert php associative array to json object notation

radleybobins Over a year ago

okay, that's helpful, but unfortunate (the plugin doesn't seem to like that)...thanks again for your help, I'm going to try and implement the brand new answer that will be easier on performance.

ramabarca · Accepted Answer · 2017-01-31 16:53:17Z

0

declare the array on each step, then populate it. In this case don't use counters $i, $j to push values

$arr1 = array(
    'id': <id>,
    ...
    'list': array()
);

while(<condition>){
    $arr1['list'][] = array(
        'id' => <list_id>
        ...     
    );
}
...

answered Jan 31, 2017 at 16:53

ramabarca

2982 silver badges14 bronze badges

Collectives™ on Stack Overflow

Creating Multi-Dimensional Array Using SQL data

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related