php - MySQL 连接多个表

Question

概述:我正在尝试将多个表连接在一起，以创建一个根据提取数据的值构建的文本字符串。我已经到了需要提取 meta_key 字段的 meta_value 的地步，该字段等于 _sku 和 _price。但是，我无法弄清楚这是如何完成的。非常感谢为我指明正确方向的任何帮助。

细节:我想通过创建结构如下的字符串为每种可能的产品和车辆配置返回一行:

SKU、年份、品牌、型号、引擎、标题、价格

我有正确数量的结果，但我不知道如何从 wp_postmeta 中获取 _price 和 _sku 值表。

最终输出应该是这样的:

2T013-ADU00, 2013, Kia, Optima,   Hybrid, All Weather Floor Mats, 65.60
2T013-ADU00, 2013, Kia, Optima,   SX,     All Weather Floor Mats, 65.60
2T013-ADU00, 2013, Kia, Optima,   EX,     All Weather Floor Mats, 65.60
2T013-ADU00, 2013, Kia, Optima,   LX,     All Weather Floor Mats, 65.60
2T013-ADU00, 2012, Kia, Optima,   LX,     All Weather Floor Mats, 65.60
2T013-ADU00, 2012, Kia, Optima,   EX,     All Weather Floor Mats, 65.60
2T013-ADU00, 2012, Kia, Optima,   SX,     All Weather Floor Mats, 65.60
2T013-ADU00, 2012, Kia, Optima,   Hybrid, All Weather Floor Mats, 65.60
988503W000,  2011, Kia, Sportage, Base,   Wiper Blade,            15.75
988503W000,  2011, Kia, Sportage, EX,     Wiper Blade,            15.75

这是我的PHP:

$sql = "
SELECT filter.filterId as Filter, posts.*, model.ModelName as Model, make.MakeName as Make,   year.Year as Year, engine.EngineName as Engine, meta.*
FROM wp_sFilter filter
JOIN wp_posts posts ON filter.productId = posts.ID 
JOIN wp_sModel model ON filter.modelId  = model.modelId 
JOIN wp_sMake make ON filter.makeId  = make.makeId 
JOIN wp_sYear year ON filter.yearId  = year.yearId 
JOIN wp_sEngine engine ON filter.engineId  = engine.engineId 
JOIN wp_postmeta meta ON filter.productId  = meta.post_id 
WHERE post_status = 'publish' 
AND 
post_type = 'product'
AND
meta_key = '_price' OR '_sku'
";


if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}

while($row = $result->fetch_assoc()){
    echo $row['meta_value'].', '.$row['Year'].', '.$row['Make'].', '.$row['Model'].' '.$row['Engine'].', '.$row['post_title'].', '.$row['meta_value'] . '<br />';
}

这是mysql 表:

wp_posts

ID | post_status | post_type | post_title---+-------------------------------------------------9  | published   | product   | All Weather Floor Mats11 | published   | product   | Wiper Blade

wp_sFilter

filterId | productId | makeId | modelId | yearId | engineID---------+-----------+--------+---------+--------+---------8        | 9         | 1      | 1       | 3      | 127        | 9         | 1      | 1       | 3      | 176        | 9         | 1      | 1       | 3      | 115        | 9         | 1      | 1       | 3      | 109        | 9         | 1      | 1       | 2      | 510       | 9         | 1      | 1       | 2      | 611       | 9         | 1      | 1       | 2      | 812       | 9         | 1      | 1       | 2      | 913       | 11        | 1      | 4       | 5      | 1314       | 11        | 1      | 4       | 5      | 14

wp_sMake

makeId | MakeName-------+---------1      | Kia

wp_sModel

modelId | makeId | ModelName--------+--------+----------1       | 1      | Optima4       | 1      | Sportage

wp_sYear

yearId | makeId | modelId | Year-------+--------+---------+-----1      | 1      | 1       | 20112      | 1      | 1       | 20123      | 1      | 1       | 20134      | 1      | 1       | 20145      | 1      | 4       | 20116      | 1      | 4       | 20127      | 1      | 4       | 2013

wp_sEngine

engineId | makeId | modelId | yearId | EngineName---------+--------+---------+--------+-----------1        | 1      | 1       | 1      | LX2        | 1      | 1       | 1      | EX3        | 1      | 1       | 1      | SX4        | 1      | 1       | 1      | Hybrid5        | 1      | 1       | 2      | LX6        | 1      | 1       | 2      | EX8        | 1      | 1       | 2      | SX9        | 1      | 1       | 2      | Hybrid10       | 1      | 1       | 3      | LX11       | 1      | 1       | 3      | EX12       | 1      | 1       | 3      | Hybrid13       | 1      | 4       | 5      | Base14       | 1      | 4       | 5      | EX15       | 1      | 1       | 1      | LX16       | 1      | 4       | 5      | SX17       | 1      | 1       | 3      | SX

wp_postmeta

meta_id | post_id | meta_key | meta_value--------+---------+----------+------------20      | 9       | _sku     | 2T013-ADU0024      | 9       | _price   | 65.6050      | 11      | _sku     | 988503W00024      | 11      | _price   | 15.75

Answer 1

从 wp_postmeta 表获取价格和 sku 的一种方法是两次加入 wp_postmeta 表。

所以你会做类似的事情

...
JOIN wp_postmeta sku ON filter.productId  = sku.post_id 
JOIN wp_postmeta price ON filter.productId  = price.post_id
...

并将 SELECT 中的 meta.* 替换为 sku.meta_value as sku, price.meta_value as price

相应地调整其他部分或代码/sql，但希望你明白了。

基本上，您需要作为 sku 和价格加入表格。假设您有一个 sku 和价格表，您将如何进行连接，就连接而言，想法相同，它们只是在同一个表中。