doucan8276 2017-05-06 10:39
浏览 101
已采纳

如何使用分隔的列和值数组生成查询

I have this array:

$filter=['color*black','color*blue','color*red','paint*apex','paint*dalton'];

Each value in $filter has two substrings separated by *. The first substring represents a database table column and the second represents a desired value for that column.

My products table looks like this:

id    name    color    paint
1     p1      black     compo
2     p2      red       dalton
3     p3      pink      apex
4     p4      blue      apex
5     p5      cream     compo

Using $filter, I need to search the products table and return all rows with a paint value of apex or dalton AND a color value of black, blue, or red.

The desired output is a mysql query that will only return these rows:

id    name    color    paint
2     p2      red       dalton
4     p4      blue      apex
  • 写回答

2条回答 默认 最新

  • duanji4449 2017-05-06 11:02
    关注

    If You need to construct a query like this SELECT * FROM products WHERE (color IN ('black', 'blue', 'red')) AND (paint IN ('apex', 'dalton')), then the code below might be useful (please, check it here):

    $filter = array(
        0 => "color*black",
        1 => "color*blue",
        2 => "color*red",
        3 => "paint*apex",
        4 => "paint*dalton"
    );
    
    $elements = [];
    
    foreach ($filter as $value) {
        list($before, $after) = explode('*', $value);
        $elements[$before][] = $after;
    }
    
    $parts = [];
    
    foreach ($elements as $column => $values) {
        $parts[] = "(`$column` IN ('" . implode("', '", $values) . "'))";
    }
    
    $query = 'SELECT * FROM `products` WHERE ' . implode(' AND ', $parts);
    

    Running this query against the given table data structure:

    id    name    color    paint
    1     p1      black     compo
    2     p2      red       dalton
    3     p3      pink      apex
    4     p4      blue      apex
    5     p5      cream     compo
    

    will match the following rows:

    2     p2      red       dalton
    4     p4      blue      apex
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥100 连续两帧图像高速减法
  • ¥15 组策略中的计算机配置策略无法下发
  • ¥15 如何绘制动力学系统的相图
  • ¥15 对接wps接口实现获取元数据
  • ¥20 给自己本科IT专业毕业的妹m找个实习工作
  • ¥15 用友U8:向一个无法连接的网络尝试了一个套接字操作,如何解决?
  • ¥30 我的代码按理说完成了模型的搭建、训练、验证测试等工作(标签-网络|关键词-变化检测)
  • ¥50 mac mini外接显示器 画质字体模糊
  • ¥15 TLS1.2协议通信解密
  • ¥40 图书信息管理系统程序编写