Multidimensional array from mysql result

Question

I have 2 database tables: categories and sub_categories. sub_categories table is linked to categories table by category id cat_id

In php I want to print the parent category and all the sub-categories belonging to it. Is it possible to do so by creating a multidimensional array from 1 mysql query?

Example result:

array
(
  "Category 1"=>array
  (
  "Sub-category",
  "Sub-category",
  "Sub-category"
  ),
  "Category 2"=>array
  (
  "Sub-category"
  ),
  "Category 3"=>array
  (
  "Sub-category",
  "Sub-category",
  "Sub-category"
  )
 );

My query returns just 1 sub category for each category:

SELECT `categories`.`cat_title`, `sub_categories`.`sub_cat_title` 
FROM (`categories`) 
LEFT JOIN `sub_categories` 
ON `sub_categories`.`cat_id` = `categories`.`cat_id` 
GROUP BY `categories`.`cat_title`

dano · Accepted Answer · 2012-07-27 02:12:34Z

3

MySQL wont return an array, it will only return rows, but you can easily create the array with php.. just do this

<?php
  $db = new mysqli('host','user','password','database');
  $query =  "SELECT c.cat_title, s.sub_cat_title
             FROM categories c
             LEFT JOIN sub_categories s USING (cat_id)
             ORDER BY cat_title";
  $result = $db->query($query);
  while($row = $result->fetch_assoc()){
    $cats[$row['cat_title']][] = $row['sub_cat_title'];
  }        

?>

answered Jul 27, 2012 at 2:12

dano

1,0431 gold badge6 silver badges11 bronze badges

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

1 Comment

dano Over a year ago

sorry typed group by, meant order by, if you group by you will only get one row for each title - although you don't need that either, php is pretty cool with arrays, it automatically inserts the next index into your multidimensional array and creates a new 1st dimension if it doesnt exist

Carlos Vergara · Accepted Answer · 2012-07-27 01:18:53Z

1

Try using ORDER BY instead.

ORDER BY `categories`.`cat_title`, `sub_categories`.`sub_cat_title`

answered Jul 27, 2012 at 1:18

Carlos Vergara

3,6325 gold badges35 silver badges56 bronze badges

Collectives™ on Stack Overflow

Multidimensional array from mysql result

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related