在PHP中使用嵌套查询生成JSON对象

Question

I'm trying to pass JSON Object from PHP like this:

[{
    "id":"user1",
    "shops": [{
        "id":"shop1",
        "invoices": [{
            "id":"invoice1",
            "details": [{
                "id":"detailA"
            }, {
                "id":"detailB"
            }]
        }, {
        "id":"shop2",
        "invoices": [{
            "id":"invoice2",
            "details": [{
                "id":"detailC"
            }, {
                "id":"detailD"
            }]
        }]
    }]
}]

I have this function and it's working, but when database returns a lot of records I get memory limit exhausted. I don't want to increase memory limit. What is the best practice?

function loadusers() {
    $return = array();

    $query = "SELECT u.id FROM users u";
    $users = mysql_query($query);

    while($user = mysql_fetch_assoc($users)) {
        $query = "SELECT s.id FROM shops s WHERE s.id_user = ".$user['id'];
        $shops = mysql_query($query);

        while($shop = mysql_fetch_assoc($shops)) {                
            $query = "SELECT i.id FROM invoices i WHERE i.id_shop = ".$shop['id'];
            $invoices = mysql_query($query);

            while($invoice = mysql_fetch_assoc($invoices)) {                
                $query = "SELECT d.id FROM invoice_details d WHERE d.id_inv = ".$invoice['id'];
                $details = mysql_query($query);

                while($detail = mysql_fetch_assoc($details)) {                
                    $invoices['details'][] = $detail;
                }

                $shop['invoices'][] = $invoice;
            }

            $user['shops'][] = $shop;
        }

        $return[] = $user;
    }

    return $return;
}

Thanks in advance.

Following fiction's answer:

I created new query:

SELECT
    u.id as id_user, 
    s.id as id_shop,
    i.id as id_invoice,
    d.id as id_detail
FROM 
    users u LEFT JOIN shops s ON s.id_user = u.id
    LEFT JOIN invoices i ON i.id_shop = s.id
    LEFT JOIN invoice_details d ON d.id_invoice = i.id

Result:

Is there a syntax sugar to convert it to expected JSON object like above or do I have to manually parsing the result into PHP array then do the json_encode?

Answer 1

You can do it with one query using JOINS and you will not be needed cycles, query for your situation:

SELECT 
    users.id as uid, 
    shops.id as sid,
    invoices.id as iid,
    invoice_details.id as did
FROM 
    users INNER JOIN shops ON shops.id_user = users.id,
    invoices INNER JOIN shops ON invoices.id_shop = shops.is,
    invoice_details INNER JOIN invoices ON invoice_details.id_inv = invoices.id;

Maybe it will be better for you to use another join (not inner), depending on what you want, so some more about joins: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/