dounao4179 2016-08-09 14:26
浏览 5
已采纳

PHP选择类别和计数

Table categories:

id, name,    
-------------
1   category1
2   category2
3   category3
4   category4

Table posts:

id, category, title
--------------------
1   1, 3      title1
2   4, 2      title2
3   1, 4      title3

I would like to have a show categories and counter posts in this category. In the category column in the table posts I IDs of categories, which are separated by ', '. How to do that is searched when category = category ID and show counter with a minimum of SQL queries.

  • 写回答

1条回答 默认 最新

  • duanqian3464 2016-08-09 14:31
    关注

    You should fix your data structure to have one row per post and category. A comma-separated list is not a SQLish way to store data for many reasons. You should change this to a junction table.

    Here are some reasons why:

    • Numbers should be stored as numbers and not strings.
    • Foreign key relationships should be properly defined, and you can't do that with a string to a number.
    • An attribute should contain one value.
    • MySQL doesn't have very good support for string processing functions.
    • Queries using the column cannot be optimized using indexes.

    You should have a table called PostCategories for this information.

    Sometimes, we are stuck with other peoples bad design decisions. If so, you can use a query such as:

    select c.id, count(*)
    from posts p join
         categories c
         on find_in_set(c.id, replace(p.category)) > 0
    group by c.id;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题