How to fetch data from mysql as array of arrays

Question

I have this sql query:

SELECT * 
FROM productoptions  
    INNER JOIN options on productoptions.OptionID = options.OptionID 
    INNER JOIN optiongroups ON productoptions.OptionGroupID = optiongroups.OptionGroupID;

Is there a way to fetch it as a multidimensional array? In a way that every optiongroupname is an array containing all of its optionaname?

I will be fetching it from a PHP Script

It woudl help if you showed us an example of the array you think you want — RiggsFolly, Feb 25 '22 at 17:32
Does this answer your question? https://stackoverflow.com/questions/1501274/get-array-of-rows-with-mysqli-result — Stevish, Feb 25 '22 at 17:34
Thanks a lot guys for showing your support. I had a feeling it could not be fetch as so. However can you help me do it using php? Here is what I want: if you see in the picture there are two unique values for OptionGroupName, I want to have two index array with this values and then their value be an array containing the optionname that belong to them — Jeff, Feb 25 '22 at 17:41
Here is an example: ['color' => ['red', 'blue', 'green'], 'size' => ['S', 'M', 'L', 'XL', 'XXL'] ]; — Jeff, Feb 25 '22 at 17:50
Show us your current PHP code that is processing this resultset — RiggsFolly, Feb 25 '22 at 17:50
Is there a good reason you can't just `foreach ($mysqli->query($sql) as $row) { $options[$row['OptionGroupname']][] = $row['optionName']; }` ? — mickmackusa, Jul 11 '22 at 23:26
If you need uniqueness. `foreach ($mysqli->query($sql) as $row) { $options[$row['OptionGroupname']][$row['optionName']] = $row['optionName']; }` ... then IF you wish to have indexed subarrays, you can call `array_values()` on the subarrays after looping. — mickmackusa, Jul 11 '22 at 23:32

score 0 · Answer 1 · answered Feb 25 '22 at 18:13

Use GROUP_CONCAT() to create concatenated strings in MySQL.

SELECT 
    GROUP_CONCAT(DISTINCT CASE WHEN OptionGroupName = 'color' THEN OptionName END) AS color,
    GROUP_CONCAT(DISTINCT CASE WHEN OptionGroupName = 'size' THEN OptionName END) AS size
FROM productoptions  
INNER JOIN options on productoptions.OptionID = options.OptionID 
INNER JOIN optiongroups ON productoptions.OptionGroupID = optiongroups.OptionGroupID;

Then in PHP you can explode this into arrays.

$row = $result->fetch_assoc();
$row['color'] = explode(',', $row['color']);
$row['size'] = explode(',', $row['size']);

score 0 · Answer 2 · edited Jul 11 '22 at 18:14

Your answer is great, Barmar. Thanks a lot. The problem is that I might be having more OptionGroupName and cant hardcode them as they will be dynamic. I achieved it with this php code

    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $variantsToBeTreated = [];
    $variantsArray = [];

    foreach ($rows as $key => $one) {

        $data[] = $one['OptionGroupName'];

        foreach ($one as $key => $two) {

            foreach ($data as $three) {

                if ($three == $two) {
                    $variantsToBeTreated[$two][] = $one['OptionName'];
                }
            }
        }
    }

    foreach ($variantsToBeTreated as $key => $val) {
        $variantsArray[$key] = array_unique($val);
    }

    return $variantsArray;

The thing about this approach is that it seems verbose.

How to fetch data from mysql as array of arrays

2 Answers2