Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have 2 MySQL tables, where I extract data with following SQL statements:

Categories: SELECT cat_id, cat_name FROM categories

Products: SELECT pro_id, pro_name, pro_category FROM products

Of course, pro_category is foreign key for cat_id

I'd like to reach the following JSON data:

"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"
            }
        ]
    }   
]

So I have to do an array of categories and, for each element, a sub-array of products (then I'll go with json_encode()).

Looping through categories and create the first array is quite easy, but I'm totally stuck on how to create the products sub-array... Here's my code, and the question marks are the point where I stopped because I don't know what to do:

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

Please, can you help?
Thanks!

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted
$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,
    );
}
share|improve this answer
 
Thnaks, this works!!! Do the products query, called so many times, can affect performance? –  Ivan Mar 23 at 13:15
 
@Ivan: I think you will have to use a inner for loop. please consider using PHP PDO or mysqli extentions instead of the default mysql_* functions as they are no longer recommended by php community and are vulnerable to security threats like mysql injection. –  mithunsatheesh Mar 23 at 13:28
add comment
$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'],
    );
}
share|improve this answer
add comment

I use a JOIN query to order product by its category id, so that products belongs to an category will come together. Traverse through the result array and create new category if you see the cat_id change.

$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());
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.