Setting PHP Query into a nested array

Question

I am trying to use PHP to logically store all of that data in a nested associative arrays, and then whenever I loop through the data or need to reference the data I would refer to the array pointer rather than doing new queries each time.

For example:

My query is

$query = $db->query("
SELECT
    c.id campaign_id,
    c.campaign_title,
    c.start_date campaign_start_date,
    c.end_date campaign_end_date,
    cat.id category_id,
    cat.category_title,
    n.id nominee_id,
    n.title nominee_title,
    u.id voter_id,
    u.fullname voter_name
FROM
    campaign c
LEFT JOIN categories cat ON cat.campaign_id = c.id
LEFT JOIN category_nominees cn ON cn.category_id = cat.id
LEFT JOIN nominees n ON n.id = cn.nominee_id
LEFT JOIN category_votes cv ON cv.campaign_id = c.id
AND cv.category_id = cat.id
AND cv.nominee_id = n.id
LEFT JOIN users u on u.id = cv.user_id
WHERE
    c.active = 1
ORDER BY
    u.fullname,
    c.campaign_title,
    cat.category_order,
    cat.category_title,
    cn.nominee_order,
    n.title
") or die(mysqli_error());

and im trying to set up the nested array like

$array = array() ;

while($row = mysqli_fetch_assoc($query)) {

  $array[$row['campaign_id']] = $row['campaign_id'];
  $array[$row['campaign_id']]['campaign_title'] = $row['campaign_title'];
  $array[$row['campaign_id']]['campaign_start_date'] = $row['campaign_start_date'];
  $array[$row['campaign_id']]['campaign_end_date'] = $row['campaign_end_date'];
  $array[$row['campaign_id']]['campaign_title'] = $row['campaign_title'];
  $array[$row['campaign_id']]['category_id'] = $row['category_id'];
  $array[$row['campaign_id']]['category_id']['category_title'] = $row['category_title'];
}

So whenever I point to:

$array[2][3]['category_title']

It would print the category title

Do you have any ideas? I literally been at it for months and can't figure it out.

Sry but i do not understand at all what you're doing. Could you show us a short example of the resulting array you want ? — Unex
– Unex, Commented Apr 1, 2015 at 16:28
Are you sure the SQL request is involved in the problem, or is this just how to access values in the arrays? — mins
– mins, Commented Apr 1, 2015 at 18:15

Barmar · Accepted Answer · 2015-04-01 20:13:17Z

1

Use the following:

while ($row = mysqli_fetch_assoc($query)) {
    if (!isset($row['campaign_id'])) {
        $array[$row['campaign_id']] = $row;
    }
    if (!isset($array[$row['campaign_id']]['categories'])) {
        $array[$row['campaign_id']]['categories'] = array();
    } 
    $array[$row['campaign_id']]['categories'][$row['category_id']] = array('category_id' => $row['category_id'], 'category_title' => $row['category_title']);
    }
}

$row is already an associative array, you don't need to assign each element separately. You only need to deal specially with the category information, which has to be put into a nested associative array that I've called categories. So you would access it as

$array[0]['categories'][1]['category_title']

You can loop over all the categories with:

foreach ($array[$campaign]['categories'] as $cat) {
    echo "Category ID: {$cat['category_id']} Title: {$cat['category_title']}<br>";
}

edited Apr 1, 2015 at 20:13

answered Apr 1, 2015 at 16:28

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

4 Comments

lancey Over a year ago

Thanks so much! Would it be ok if instead of isset we do !array_key_exists? Wouldn't that be a better choice?

Barmar Over a year ago

You can use that if you like. I prefer isset(), since it's terser and easier to read IMHO.

lancey Over a year ago

How would you do a foreach loop to show each category title?

Barmar Over a year ago

Added it to the answer.

Collectives™ on Stack Overflow

Setting PHP Query into a nested array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related