douyiken0968 2015-11-21 09:20
浏览 113
已采纳

如何使用匹配的字符串过滤sql查询结果

I have a database table in which one column contain the string value that is looks like dictionary in php.

Table:College

Id  name    requirement
1   x   {"users": ["A", "B"], "name": "*", "pool": "CSE", "place": "Bangalore"}
2   y   {"users": ["A", "C"], "name": "*", "pool": "CSE", "place": "Chennai"}
3   z   {"users": ["A", "B"], "name": "*", "pool": "ECE", "place": "Bangalore"}
4   r   {"users": ["A", "D"], "name": "*", "pool": "EEE", "place": "UP"}

From this table I need to filter only those rows in which the requirements column contain “pool”:”CSE”. That is if pool is CSE or pool is ECE etc .

Currently my php code for fetching whole table is:

    $query = "SELECT * FROM waitqueue";
    $result = mysql_query($query);

    echo "[";
    echo json_encode(mysql_fetch_assoc($result));
    while ($row = mysql_fetch_assoc($result))
        echo "," . json_encode($row);
    echo "]";

I dont want to change the above sql query.Because I need both conditions(full table and selected pool rows) from single query.So I wish to parse from results.

How to match the "pool" and how can I filter those rows?

Any one please suggest me.

Thanks in advance.

  • 写回答

1条回答 默认 最新

  • dsaeyrq451928 2015-11-21 11:50
    关注

    In your case you can filter like this

    WHERE requirement LIKE '%"pool": "CSE"%'
    

    But this is very big crutch :)

    It is prefer to separate column requirement to columns: name, pool, place. Than you may filter like this, for example:

    WHERE pool = "CSE"
    

    Other method is to filter rows in your while cycle like this

    $output = '';
    while ($row = mysql_fetch_assoc($result)) {
        $requirement = json_decode($row['requirement'], true);
        if ($requirement['pool'] === 'CSE') {
            $output .= $output ? ', ' : '';
            $output .= json_encode($row);
        }
    }
    $output = '[' . $output . ']';
    echo $output;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题