PHP 准备 MySQL 查询来填充 HTML 表

Question

我必须使用准备好的语句从 PHP 中的 MySQL 表准备查询，以便构建多种语言的开放时间 HTML 表。后备语言是英语(即 en)，因此如果 session 语言不存在值(例如 fr)，则应返回后备语言 en>.

表_A

id  | att_id | item_id | lang | row | col | value
1   | 260    | 15      | en   | 0   | 0   | Monday  
2   | 260    | 15      | en   | 0   | 1   | 10:30 - 15:00  
3   | 260    | 15      | en   | 0   | 2   | 18:30 - 24:00  
4   | 260    | 15      | fr   | 0   | 0   | Lundi  
5   | 260    | 15      | fr   | 0   | 1   | 10:30 - 15:00  
6   | 260    | 15      | fr   | 0   | 2   | 18:30 - 24:00  
7   | 260    | 15      | en   | 1   | 0   | Tuesday  
8   | 260    | 15      | en   | 1   | 1   | 10:30 - 15:00  
9   | 260    | 15      | en   | 1   | 2   | 18:30 - 24:00  
10  | 260    | 15      | fr   | 1   | 0   | Mardi  
11  | 260    | 15      | fr   | 1   | 1   | 10:30 - 15:00  
12  | 260    | 15      | fr   | 1   | 2   | 18:30 - 24:00
13  | 260    | 15      | en   | 2   | 0   | Wednesday  
14  | 260    | 15      | en   | 2   | 1   | 10:30 - 15:00  
15  | 260    | 15      | en   | 2   | 2   | 18:30 - 24:00 
16  | 260    | 13      | en   | 0   | 0   | Monday  
17  | 260    | 13      | en   | 0   | 1   | 10:30 - 15:00  
18  | 260    | 13      | en   | 0   | 2   | 18:30 - 24:00 

item_id 是公司，因此我必须传递公司 ID，在本例中为 15，att_id 是字段，因此我必须传递 260.

对于后备语言，我可以在同一个表上使用带有 JOIN 的COALESCE。

它有效，但返回双结果，而不是一个。使用COALESCE它应该返回第一个结果NOT NULL。

这是选择:

SELECT 
        TABLE_A.item_id, TABLE_A.row, 
        GROUP_CONCAT(TABLE_A.col) AS col, TABLE_A.value,

        GROUP_CONCAT(COALESCE(
            (
                SELECT 
                    TABLE_A.value FROM TABLE_A 
                WHERE 
                TABLE_A.item_id = '15' AND 
                TABLE_A.att_id = '260' AND 
                TABLE_A.row = T.row AND 
                TABLE_A.col = T.col AND 
                TABLE_A.langcode = 'fr'
            ),
            (
                SELECT 
                    TABLE_A.value FROM TABLE_A 
                WHERE 
                TABLE_A.item_id = '15' AND 
                TABLE_A.att_id = '260' AND 
                TABLE_A.row = T.row AND 
                TABLE_A.col = T.col AND 
                TABLE_A.langcode = 'en'
            )
        )) AS name

    FROM TABLE_A

    JOIN TABLE_A AS T ON T.id = TABLE_A.id

    WHERE TABLE_A.att_id = '260' AND TABLE_A.item_id = '15' GROUP BY TABLE_A.row

如果 session 语言是fr，则结果是:

[{"row":"0","col":"0,0,1,1,2,2","value":"Lundi,Lundi,10:30 - 15:00,10:30 - 15
:00,18:30 - 24:00,18:30 - 24:00"},{"row":"1","col":"0,0,1,1,2,2","value":"Mardi,Mardi,10
:30 - 15:00,10:30 - 15:00,18:30 - 24:00,18:30 - 24:00"},{"row":"2","col":"0,0,1,1,2,2","value":"Wednesday,
Wednesday,10:30 - 15:00,10:30 - 15:00,18:30 - 24:00,18:30 - 24:00"}]

如果 session 语言是en，则结果是:

[{"row":"0","col":"0,0,1,1,2,2","value":"Monday,Monday,10:30 - 15:00,10:30 - 15
:00,18:30 - 24:00,18:30 - 24:00"},{"row":"1","col":"0,0,1,1,2,2","value":"Tuesday,Tuesday,10
:30 - 15:00,10:30 - 15:00,18:30 - 24:00,18:30 - 24:00"},{"row":"2","col":"0,0,1,1,2,2","value":"Wednesday,
Wednesday,10:30 - 15:00,10:30 - 15:00,18:30 - 24:00,18:30 - 24:00"}]

应该返回什么:

如果 session 语言是fr:

[{"row":"0","col":"0,1,2","value":"Lundi,10:30 - 15
:00,18:30 - 24:00"},{"row":"1","col":"0,1,2","value":"Mardi,10
:30 - 15:00,18:30 - 24:00"},{"row":"2","col":"0,1,2","value":"
Wednesday,10:30 - 15:00,18:30 - 24:00"}]

如果 session 语言是en:

[{"row":"0","col":"0,1,2","value":"Monday,10:30 - 15
:00,18:30 - 24:00"},{"row":"1","col":"0,1,2","value":"Tuesday,10
:30 - 15:00,18:30 - 24:00"},{"row":"2","col":"0,1,2","value":"
Wednesday,10:30 - 15:00,18:30 - 24:00"}]

我做错了什么？

Answer 1

不要选择所有语言记录，而是仅选择英语记录，并外连接所选语言的记录。然后使用coalesce首先使用第二个表中的值，然后(作为后备)使用第一个表中的值:

SELECT      a.item_id, 
            a.row, 
            GROUP_CONCAT(a.col) AS col, 
            GROUP_CONCAT(COALESCE(b.value, a.value)) AS name
FROM        TABLE_A a
LEFT JOIN   TABLE_A b 
         ON a.item_id = b.item_id
        AND a.att_id = b.att_id
        AND a.row = b.row
        AND a.col = b.col
        AND b.langcode = ?
WHERE       a.langcode = 'en'
        AND a.att_id = ? 
        AND a.item_id = ? 
GROUP BY    a.row