How to fetch data from mysql as array of arrays-CodePudding

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

CodePudding user response：

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']);

CodePudding user response：

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.