PHP/MySQL Sort Multi-dimensional array

Question

I'm trying to sort an array in to a three-deep array. This is my current query:

SELECT * FROM question 
INNER JOIN category ON question.category_id = category.id
INNER JOIN difficulty ON question.difficulty_id = difficulty.id

Expected result is something like:

array(
    '1' => array( // category id 1
        '1' => array( // difficulty id 1
            '1' => array('...'), // question id 1
            '2' => array('...') // question id 2
        ),
        '2' => array(
            '3' => array('...'),
            '4' => array('...')
        )
    )
)

I did have the following:

foreach($categories as $category) {
    foreach($difficulties as $difficulty) {
        foreach($questions as $question) {
            if ($question['category_id'] == $category['id'] && $question['difficulty_id'] == $difficulty['id']) {
                $feed[$category['id']][$difficulty['id']][$question['id']] = $question;
            }
        }
    }
}

But there will be 10,000+ questions and performance will be bad so is there a way I can do this with one query and fewer loops?

It seems unlikely that you would need to dump all the data from these three tables into php for processing. You should probably be doing more of the data processing work in the database, rather than in php. — Gordon Linoff
– Gordon Linoff, Commented Apr 19, 2015 at 14:21
Essentially my goal is to achieve all the question data sorted in their related category and difficulties. — Sygon
– Sygon, Commented Apr 19, 2015 at 14:25
well that update came too late. What purpose? Storing in local storage (retrieved by ajax call) for offline apps or just display? — maraca
– maraca, Commented Apr 19, 2015 at 14:38
It is an AJAX request I then json encode the dataset and return it to my application for localStorage storing. — Sygon
– Sygon, Commented Apr 19, 2015 at 14:45
ok I added mysql specific information and how to build a structure in local storage, but i'm not good with JSON — maraca
– maraca, Commented Apr 19, 2015 at 15:19

maraca · Accepted Answer · 2015-04-22 01:15:00Z

Basically you could just return your query and order by the ids like so:

Category_ID      Difficulty_ID      Question_ID
     0                0                 0
     0                0                 1
     1                0                 2
     1                3                 3
     1                3                 4
     2                0                 5
     2                1                 6

Then parse everything in a while:

each time the category_ID changes add a new category with empty difficulty and reset previous difficulty
each time the difficulty changes add new difficulty to category with empty question
each time add the question to current difficulty.

To store this structure performantly in local storage:

define a unique delimiter (note: IE doesn't support control characters, this also means you can't store binary data without encoding it before, e.g. base64)
load each row of each table like this:
```
key: unique table prefix + id
value: columns (delimited with the delimiter defined before)
```
The easiest way to return a whole table at once is to define a second delimiter and then have some slightly ugly query in the form of:
```
SELECT id||delimiter||col1||delimiter||...||colN FROM ...
```
And then put it all together with a list aggregation using the second delimiter (group_concat() in mysql).
Sometimes you need maps (for N to M relations or also if you want to search questions by difficulty or category), but because each question only has one category and difficulty you are already done.

Alternative

If the data is not too big and doesn't change after login, then you can just use the application cache and echo your stuff in script tags.

Collectives™ on Stack Overflow

PHP/MySQL Sort Multi-dimensional array

1 Answer 1

To store this structure performantly in local storage:

Alternative

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

To store this structure performantly in local storage:

Alternative

Comments

Your Answer

Sign up or log in

Post as a guest

Related