mysql新手：sql选择问题

Question

Guys, good day. im starting to learn mysql and still fiddling around. I just saw a table structures just like the example. How can i achieve an array querying in sql when i use var_dump in php? more power!

Table: categories
id |  name  |
1  |  Cat A |
2  |  Cat B |

Table: sub_categories
id | category_id |   name      |
1  |     1       |  Sub Cat A  |
2  |     2       |  Sub Cat B  |

array
  'Cat A' => 
      array
        1 => string 'Sub Cat A' (length=9)
  'Cat B' => 
      array
        2 => string 'Sub Cat B' (length=9)

Answer 1

You have to loop over your category table and then select all rows from this category and fill up your array.

You could also use JOIN like in the example below (leave out the where part if you want all categories):

$result = mysql_query("SELECT categories.name as catname
    , categories.id as catid
    , sub_categories.id as subcatid
    , sub_categories.name as subcatname 
  FROM categories 
  LEFT JOIN sub_categories ON sub_categories.category_id = categories.id 
  WHERE categories.id = 1");

$rows = mysql_fetch_object($result);
$result = array();
foreach($rows as $row) {
  $result[$row->catname][] = array(
     'id' => $row->subcatid,
     'category_id' => $row->catid,
     'name' => $row->subcatname
  );
}

var_dump($result);

UNTESTED but should work.