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

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 · answered Feb 26 '22 at 10:02

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 dinamyc. 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