php - 如何在 php mysql 中将搜索选项中的数据分配为 json 格式

Question

My Mysql table is in following format and with json format for shows_id 

id |  assign_id  |  rights    | party      |shows_id
---+-------------+------------+------------+---------
1  |4            |  12        | xyz        | ["2","3","13","14"]
2  |4            |   5        | xyz        | ["2","3","13","59"]
3  |6            |  14        | abc        | ["56","57","59","60"]
4  |6            |  5         | abc        | ["56","57","59","60"]
5  |7            |  12        | def        | ["2","41","42","54"]
6  |7            |  5         | def        | ["2","41","42","59"]

我希望以表格格式输出:
权利搜索结果 5

     | party                |shows_id
-----+----------------------+----------
     | xyz  ,def            | 2
     | xyz                  | 3
     | xyz                  |13
     | xyz ,def,abc         |59
     | abc                  | 56         
     | abc                  | 57
     | abc                  | 59
     | abc                  | 60
     | def                  | 41
     | def                  | 42

我在数组中获取了 shows_id 的所有 id，但是如果 K 出现在三方中，如何在其上显示方名？

Answer 1

使用提供的设计，不可能获得上述输出，因为shows_id存储为(json)字符串(这也不是一个好的设计)。我建议创建一个包含 party 和 show_id 列的新表来存储这些映射，然后我们可以查询该表以生成所需的输出。

假设我们有一个 shows 表来存储这些映射，this SQL Fiddle 描述了如何生成输出。