在PHP和MySQL中创建一个嵌套的循环数组

Question

我有2个MySQL表，我用以下SQL语句提取数据:

分类:SELECT cat_id, cat_name FROM categories

产品:SELECT pro_id, pro_name, pro_category FROM products

当然，pro_category是cat_id的外键

我想达到以下JSON数据:

"categories":[
    {
        "id": 1,
        "name": "Guitars",
        "products":[
            {
                "id": 1,
                "name": "Fender Statocaster"
            },
            {
                "id": 2,
                "name": "Gibson Les Paul"
            }
        ]
    },
    {
        "id": 2,
        "name": "Basses",
        "products":[
            {
                "id": 3,
                "name": "Fender Jazz Bass"
            },
            {
                "id": 4,
                "name": "MusicMan StingRay"
            }
        ]
    }   
]

所以我必须做一个类别数组，对于每个元素，一个产品的子数组(然后我将使用json_encode())。

循环通过类别和创建第一个数组是相当容易的，但我完全卡住了如何创建产品子数组…这是我的代码，问号是我停下来的地方，因为我不知道该怎么做:

$categories = array();
while ($row_rsCategories = mysql_fetch_assoc($rsCategories)) { 
    $categories[] = array(
        'id' => $row_rsCategories['cat_id'],
        'name' => $row_rsCategories['cat_name'],
        'products' => array(
            ???????
        ),
    );
}

请帮忙好吗?
谢谢!

Answer 1

$categories = array();
while ($row_rsCategories = mysql_fetch_assoc($rsCategories)) { 
    $product_array = array();
    $product_array_query = mysql_query("SELECT pro_id, pro_name, pro_category FROM products WHERE pro_category = '".$row_rsCategories['cat_id']."'");
    while($product_array_fetch = mysql_fetch_array($product_array_query)) {
       $product_array[] = array("id"=>$product_array_fetch['pro_id'],"name"=>$product_array_fetch['pro_name']);
    }                
    $categories[] = array(
        'id' => $row_rsCategories['cat_id'],
        'name' => $row_rsCategories['cat_name'],
        'products' => $product_array,
    );
}

Answer 2

$categories = array();
while ($row_rsCategories = mysql_fetch_assoc($rsCategories)) { 
    $categories[$row_rsCategories['cat_id']] = array(
        'id' => $row_rsCategories['cat_id'],
        'name' => $row_rsCategories['cat_name'],
        'products' => array(),
    );
}
while ($row_rsProducts = mysql_fetch_assoc($rsProducts)) {
    $categories[$row_rsProducts['pro_category']]['products'][] = array(
         'id' => $row_rsProducts['pro_id'],
         'name' => $row_rsProducts['pro_name'],
    );
}

Answer 3

我使用JOIN查询按类别id订购产品，这样属于一个类别的产品就会聚集在一起。如果看到cat_id更改，遍历结果数组并创建新类别。

$db = new PDO($connectionString);
$sql = "SELECT * FROM products t1 LEFT JOIN categories t2 ON t1.pro_category = t2.cat_id ORDER BY t2.cat_id";
$stmt = $db->prepare($sql);
if($stmt->execute())
{
    $rows = $stmt->fetchAll();
    $currentCategory = NULL;
    $categories = array();
    foreach($rows as $r)
    {
        if ($currentCategory == NULL || $r['cat_id' != $currentCategory['id'])
        {
            $currentCategory = array(
                'id' => $r['cat_id'],
                'name' => $r['cat_name'],
                'products' => array();
            );
            $categories[] = $currentCategory;
        }
        $currentCategory['products'][] = array(
            'id' => 'pro_id',
            'name' => 'pro_name',
        )
    }
}
else
{
    var_dump($stmt->errorInfo());
}