Nested arrays

Hi there,

i’m working on a project which requires the output of data from an array made up of a mysql query of a many-to-many relationship.

Here’s an excerpt of the ‘raw’ array returned from the query:


Array
(
    [0] => Array
        (
            [courseid] => 1
            [coursetitle] => Foundation Stage
            [sessiontitle] => Introduction
            [bgcolor] => #ffffff
            [color] => #000000
        )

    [1] => Array
        (
            [courseid] => 1
            [coursetitle] => Foundation Stage
            [sessiontitle] => Processing Instructions & Information
            [bgcolor] => #009933
            [color] => #000000
        )

    [2] => Array
        (
            [courseid] => 1
            [coursetitle] => Foundation Stage
            [sessiontitle] => Understanding the Meaning of Words
            [bgcolor] => #990099
            [color] => #000000
        )

    [23] => Array
        (
            [courseid] => 2
            [coursetitle] => Key Stages 1 & 2
            [sessiontitle] => Introduction
            [bgcolor] => #ffffff
            [color] => #000000
        )

    [34] => Array
        (
            [courseid] => 2
            [coursetitle] => Key Stages 1 & 2
            [sessiontitle] => Providing Visual Support
            [bgcolor] => #009933
            [color] => #000000
        )

    [35] => Array
        (
            [courseid] => 2
            [coursetitle] => Key Stages 1 & 2
            [sessiontitle] => Visual Timetables
            [bgcolor] => #009933
            [color] => #000000
        )

    [46] => Array
        (
            [courseid] => 3
            [coursetitle] => Key Stages 3 & 4
            [sessiontitle] => Introduction
            [bgcolor] => #ffffff
            [color] => #000000
        )

    [47] => Array
        (
            [courseid] => 3
            [coursetitle] => Key Stages 3 & 4
            [sessiontitle] => Processing Instructions & Information
            [bgcolor] => #009933
            [color] => #000000
        )

    [48] => Array
        (
            [courseid] => 3
            [coursetitle] => Key Stages 3 & 4
            [sessiontitle] => Understanding the Meaning of Words
            [bgcolor] => #990099
            [color] => #000000
        )

)

As you can see there’s a lot of repeated data that I want to avoid. So here’s the array i’d like to have:


Array
(
    [0] => Array
        (
            [courseid] => 1
            [coursetitle] => Foundation Stage
            [sessions] => Array
            (
                [0] => Array
                    (
                        [sessiontitle] => Introduction
                        [bgcolor] => #ffffff
                        [color] => #000000
                    )
                [1] => Array
                    (
                        [sessiontitle] => Processing Instructions & Information
                        [bgcolor] => #009933
                        [color] => #000000
                    )
                [2] => Array
                    (
                        [sessiontitle] => Understanding the Meaning of Words
                        [bgcolor] => #990099
                        [color] => #000000
                    )
            )  
        
        )
    [1] => Array
        (
            [courseid] => 2
            [coursetitle] => Key Stages 1 & 2
            [sessions] => Array
            (
                [0] => Array
                    (
                        [sessiontitle] => Introduction
                        [bgcolor] => #ffffff
                        [color] => #000000
                    )
                [1] => Array
                    (
                        [sessiontitle] => Providing Visual Support
                        [bgcolor] => #009933
                        [color] => #000000
                    )
                [2] => Array
                    (
                        [sessiontitle] => Visual Timetables
                        [bgcolor] => #009933
                        [color] => #000000
                    )
            )  
        
        )
    [2] => Array
        (
            [courseid] => 3
            [coursetitle] => Key Stages 3 & 4
            [sessions] => Array
            (
                [0] => Array
                    (
                        [sessiontitle] => Introduction
                        [bgcolor] => #ffffff
                        [color] => #000000
                    )
                [1] => Array
                    (
                        [sessiontitle] => Processing Instructions & Information
                        [bgcolor] => #009933
                        [color] => #000000
                    )
                [2] => Array
                    (
                        [sessiontitle] => Understanding the Meaning of Words
                        [bgcolor] => #990099
                        [color] => #000000
                    )
            )  
        
        )

)

Now, I believe the correct way to do this is to make one request to the database then use PHP to build the array.

So I can make the request, i just don’t know how to load the data into the array.

Thanks in advance for any input

Best,
Mike

Can you post the code that created the first array?

Yeah sure,


while ($row = mysqli_fetch_assoc($result))
{
  $courses = array('courseid' =>$row['courseid'], 'coursetitle' => $row['coursetitle'],
  'sessiontitle' => $row['sessiontitle'], 'bgcolor' => $row['bgcolor'], 'color' => $row[color]);
}

Instead of that while() loop, you could create your desired structure as you pull results from the database.


$courses = array();
while ($row = mysqli_fetch_assoc($result)) {
    // If we have not seen this course before then
    // create the course structure
    if ( ! array_key_exists($row['courseid'], $arr)) {
        $courses[$row['courseid']] = array(
            'courseid'    => $row['courseid'],
            'coursetitle' => $row['coursetitle'],
            'sessions'    => array()
        );
    }
    
    // Add this session to the course
    $courses[$row['courseid']]['sessions'][] = array(
        'sessiontitle' => $row['sessiontitle'],
        'bgcolor'      => $row['bgcolor'],
        'color'        => $row['color']
    );
}

If you can’t quite see how that works, let us know. Otherwise, have fun. (:

Thanks Salathe,

So I got an error message: The second argument should be either an array or an object on line 19, which is this one:


if ( ! array_key_exists($row['courseid'], [B]$arr[/B]))

So I can see that it’s referring to ‘$arr’, but I’m not sure what that is. Having a look at the manual I can see that is the array to search, so I changed it to:


if ( ! array_key_exists($row['courseid'], [B]$row[/B]))

And I ended up with this array:


Array
(
    [1] => Array
        (
            [courseid] => 1
            [coursetitle] => Foundation Stage
            [sessions] => Array
                (
                    [0] => Array
                        (
                            [sessiontitle] => Acitivy Resource Trail
                            [bgcolor] => #fed486
                            [color] => #000000
                        )

                )

        )

    [2] => Array
        (
            [courseid] => 2
            [coursetitle] => Key Stages 1 & 2
            [sessions] => Array
                (
                    [0] => Array
                        (
                            [sessiontitle] => Acitivy Resource Trail
                            [bgcolor] => #fed486
                            [color] => #000000
                        )

                )

        )

    [3] => Array
        (
            [courseid] => 3
            [coursetitle] => Key Stages 3 & 4
            [sessions] => Array
                (
                    [0] => Array
                        (
                            [sessiontitle] => Acitivy Resource Trail
                            [bgcolor] => #fed486
                            [color] => #000000
                        )

                )

        )

)

Which is better, but not quite right, there should be around 8 sessions per course.

Through trial and error, I got this solution, but again it’s not right. This will just assign EVERY session to each course:


while ($row = mysqli_fetch_assoc($result))
{
  
  $sessions[] = array('sessiontitle' => $row['sessiontitle'], 'bgcolor' => $row['bgcolor'], 'color' => $row['color']);
  $courses[$row['courseid']] = array('courseid' => $row['courseid'], 'coursetitle' => $row['coursetitle'], 'description' => $row['description'], 'sessions' => array());
  $courses[$row['courseid']]['sessions'] = $sessions;
  
}

And the resultant array (excerpt):


Array
(
    [1] => Array
        (
            [courseid] => 1
            [coursetitle] => Foundation Stage
            [sessions] => Array
                (
                    [0] => Array
                        (
                            [sessiontitle] => Introduction
                            [bgcolor] => #ffffff
                            [color] => #000000
                        )
                        .
                        .
                        .

                    [22] => Array
                        (
                            [sessiontitle] => Acitivy Resource Trail
                            [bgcolor] => #fed486
                            [color] => #000000
                        )

                )

        )

    [2] => Array
        (
            [courseid] => 2
            [coursetitle] => Key Stages 1 & 2
            [sessions] => Array
                (
                    [0] => Array
                        (
                            [sessiontitle] => Introduction
                            [bgcolor] => #ffffff
                            [color] => #000000
                        )
                        .
                        .
                        .

                    [45] => Array
                        (
                            [sessiontitle] => Acitivy Resource Trail
                            [bgcolor] => #fed486
                            [color] => #000000
                        )

                )

        )

)

etc etc, you get the idea. Actually I’ve noticed that with this ‘solution’ each new course has an extra set of sessions.

Thanks for your help, I’ll keep at it.

Sorry for the $arr, that’s a typo. It should be $courses.

Hey man, thanks for that.

So I changed that line, now I get kinda what i had before but a little better. Once again every session is present in every course. I’ve just run my sql query, and I can see that that’s playing up.

Not entirely sure what’s going on here, but perhaps you could indulge me a second:


$courses = array();
while ($row = mysqli_fetch_assoc($result)) {
    // If we have not seen this course before then
    // create the course structure
    if ( ! array_key_exists($row['courseid'], $courses)) {
        $courses[$row['courseid']] = array(
            'courseid'    => $row['courseid'],
            'coursetitle' => $row['coursetitle'],
            'sessions'    => array()
        );
    }
    
    // Add this session to the course
    $courses[$row['courseid']]['sessions'][] = array(
        'sessiontitle' => $row['sessiontitle'],
        'bgcolor'      => $row['bgcolor'],
        'color'        => $row['color']
    );
}

So basically if the courseid is new then initialise the key courseid as an array which contains the relavent course info and an array called sessions. Otherwise, load session title, bgcolor and color into the sessions array.

Brilliant.

That’s how it was working in my head. This seems like a very concise solution… now back to that sql query…

Thanks Salathe, hope the snow ain’t givin’ you too much grief north of the border!

Mike

Does that mean it’s working now, or …?

[ot]

I haven’t gotten into work for two whole weeks! (But that’s OK, because I can work from home.) (:[/ot]

Yes, you can create nested array for array of array. You have put the basic logic of nested array, but you have need it for sql so you have to convert it in sql code. so the other’s code is right. If you got any error in those then search any sql code converter in Google.

Yeah, sorry that wasn’t very clear.

The code works a treat, and it was great that, for the first time probably, I actually understood how!

The reason I was getting all the sessions for every course was cos I had joined up me tables incorrectly on my sql query.

Thanks again