Array help. Looking for a better way

Question

EDIT:: Maybe I should be asking what the proper way to get a result set from the database is. When you have 5 joins where there is a 1:M relationship, do you go to the database 5 different times for the data??

I asked this question about an hour ago but haven't been able to get an answer that was fitting. I went ahead and wrote some code that does exactly what I need but am looking for a better way to do it

This array gives me multiple rows of which only some are needed once and others are needed many times. I need to filter these as I have done below but want a better way of doing this if possible.

Array
(
    [0] => Array
        (
            [cid] => one line
            [model] => one line
            [mfgr] => one line
            [color] => one line
            [orderid] => one line
            [product] => many lines
            [location] => many lines
        )
    [1] => Array
        (
            .. repeats for as many rows as were found
        )
)

This code works perfectly but again, I think there is a more efficient way of doing this. Is there a PHP function that will allow me to clean this up a bit?

    // these are the two columns that produce more than 1 result.
    $product = '';
    $orderid = '';

    foreach($res as $key)
    {
        // these produce many results but I only need one.
        $cid = $key['cid'];
        $model = $key['model'];
        $mfgr = $key['mfgr'];
        $color = $key['color'];
        $orderid = $key['orderid'];

        // these are the two columns that produce more than 1 result.
        if($key['flag'] == 'product')
        {
            $product .= $key['content'];
        }
        if($key['flag'] == 'orderid')
        {
            $orderid .= $key['content'];
        }
    }

// my variables from above in string format:

---

Here is the requested SQL

SELECT
cid,
model,
mfgr,
color,
orderid,
product,
flag
FROM products Inner Join bluas ON products.cid = bluas.cid
WHERE bluas.cid = 332
ORDER BY bluas.location ASC

Answer 1

Without seeing your database structure it's a bit hard to decipher how you actually want to manipulate your data.

Perhaps this is what you're looking for though?

SELECT p.cid, p.model, p.mfgr, p.color, p.orderid, p.product, p.flag, GROUP_CONCAT(p.content SEPARATOR ', ')
FROM products AS p
INNER JOIN bluas AS b ON p.cid = b.cid
WHERE b.cid = 332
GROUP BY p.cid, p.flag
ORDER BY b.location ASC

So now for each product cid each flag will have an entry consisting of a comma separated list instead of there being many repeating for each flag entry.

Then after you're done with the string you can quickly turn it into an array for further manipulation by doing something like:

explode(', ', $key['content']);

Again it's really hard to tell what information you're trying to pull without seeing your database structure. Your SQL query also doesn't really match up with your code, like I don't even see you grabbing content.

At any rate I'm pretty sure some combination of GROUP BY and GROUP_CONCAT (more info) is what you're looking for.

If you can share more of your database structure and go into more detail of what information exactly you're trying to pull and how you want it formatted I can probably help you with the SQL if you need.