我的mysql查询使用PDO，我可以得到一个多维数组，但当涉及到格式化它时，就像我上面的图像，我完全迷路了。

下面是我得到的数组:

Array (
//the first array is only for displaying the size
[Taille] => Array
    (
        [0] => 6
        [1] => 7
        [2] => 7 <sup>1/2</sup>
        [3] => 8
        [4] => 8 <sup>1/2</sup>
        [5] => 9
        [6] => 9 <sup>1/2</sup>
        [7] => 10
        [8] => 10 <sup>1/2</sup>
        [9] => 11
        [10] => 12
        [11] => 13
    )

[KLBR] => Array
    (
        [0] => 1
        [1] => 
        [2] => 2
        [3] => 
        [4] => 1
        [5] => 
        [6] => 2
        [7] => 2
        [8] => 
        [9] => 4
        [10] => 
        [11] => 
    )

[SEB] => Array
    (
        [0] => 2
        [1] => 2
        [2] => 
        [3] => 
        [4] => 4
        [5] => 
        [6] => 3
        [7] => 3
        [8] => 
        [9] => 2
        [10] => 1
        [11] => 
    )
)

$query = $db->prepare('SELECT shop, size_6, size_7, size_75, size_8, size_85, size_9, size_95, size_10, size_105, size_11, size_12, size_13  FROM shoes_ref r LEFT JOIN shoes_stock s ON s.id_ref = r.id WHERE r.model = "Paris" AND r.color = "black"');
$query->execute();
$list = $query->fetchAll(PDO::FETCH_ASSOC);

然后在结果数组中插入所有大小:

$results["Taille"] = array("6","7","7 <sup>1/2</sup>","8","8 <sup>1/2</sup>","9","9 <sup>1/2</sup>","10","10 <sup>1/2</sup>","11","12","13");

最后，我在循环中存储每个产品的可用数量:

foreach($list as $row) {
            
            switch ($row["shop"]) {
                    
                case "k":
                    $shopname = "KLBR";
                break;
                
                case "f":
                    $shopname = "CHMPS";
                break;

                case "s":
                    $shopname = "SEB";
                break;
                    
            }
            
            $results[$shopname] = array($row["size_6"],
                $row["size_7"],
                $row["size_75"],
                $row["size_8"],
                $row["size_85"],
                $row["size_9"],
                $row["size_95"],
                $row["size_10"],
                $row["size_105"],
                $row["size_11"],
                $row["size_12"],
                $row["size_13"]);
        }
}

为了更好地理解，下面是我的表:

CREATE TABLE `shoes_ref` (
`id` smallint(8) UNSIGNED NOT NULL,
`model` varchar(14) NOT NULL,
`color` varchar(15) NOT NULL,
`last_update` datetime DEFAULT NULL,
`shop` char(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `shoes_ref` (`id`, `model`, `color`, `last_update`, `shop`) VALUES
(1, 'Paris', 'black', '2020-07-01 00:00:00', 'k'),
(2, 'Paris', 'black', '2020-06-24 00:00:00', 's');

CREATE TABLE `shoes_stock` (
`id` smallint(8) UNSIGNED NOT NULL,
`id_ref` smallint(8) UNSIGNED NOT NULL,
`size_6` tinyint(1) UNSIGNED DEFAULT NULL,
`size_7` tinyint(1) UNSIGNED DEFAULT NULL,
`size_75` tinyint(1) UNSIGNED DEFAULT NULL,
`size_8` tinyint(1) UNSIGNED DEFAULT NULL,
`size_85` tinyint(1) UNSIGNED DEFAULT NULL,
`size_9` tinyint(1) UNSIGNED DEFAULT NULL,
`size_95` tinyint(1) UNSIGNED DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `shoes_stock` (`id`, `id_ref`, `size_6`, `size_7`, `size_75`, `size_8`, `size_85`, `size_9`, `size_95`) VALUES
(2, 1, 1, NULL, 2, NULL, 1, NULL, 2),
(3, 2, 2, 2, NULL, NULL, 4, NULL, 3);

所以，在这个例子中，“巴黎黑”只在K和S商店买到，而不是在F商店。

也许我的Mysql结构不是那么好。 任何意见都将被接受。。。