duanlei2150 2013-09-23 16:12
浏览 48
已采纳

PHP / MYSQL SEARCH QUERY RETURNS ERROR:子查询返回超过1行

please I am totally new to mysql, my problem is:

I have a table called 'cong' which has the following columns(id, sort_code, pin, name, state, lga, zip, address, min, min_photo, sec, min_phone, sec_phone) which contains all congregations.

The columns (state, lga) contains the id from the tables 'states' and 'local_govt'.

The 'states' table has the following columns (id, country_id, name), and the 'local_govt' table has the following columns (id, country_id, state_id, name).

I want to carry out a search on the 'cong' table which should search through the 'state' and 'local_govt' tables for matches, below is the search function I wrote:

      <?php
      function find_cong($term) {
      $query = "SELECT * FROM cong";
      $query .= " WHERE state rLIKE
      (SELECT id FROM states WHERE upper(name) rLIKE '{$term}')";
      $query .= " OR lga rLIKE
      (SELECT id FROM local_govt WHERE upper(name) rLIKE '{$term}')";
      $query .= " OR upper(name) rLIKE '{$term}'";
      $query .= " OR upper(address) rLIKE '{$term}'";
      $query .= " OR upper(sort_code) rLIKE '{$term}'";
      $query .= " OR upper(pin) rLIKE '{$term}'";
      $query .= " OR upper(zip) rLIKE '{$term}'";
      $query .= " OR upper(min) rLIKE '{$term}'";
      $query .= " OR upper(sec) rLIKE '{$term}'";
      $query .= " OR upper(min_phone) rLIKE '{$term}'";
      $query .= " OR upper(sec_phone) rLIKE '{$term}'";
      $result = mysql_query($query);
      confirm_query($result);
      return $result;
      }

      function confirm_query($query) {
          if (!$query) {
             die("Database query failed : " . mysql_error());
          }
      }

      ?>

The problem now is that, it searches some terms and comes up with accurate results, but for some specific terms like local_govt and state names it pops an error: (Database query failed : Subquery returns more than 1 row)

Please I need your help as I don't have any idea how to write the code better than that. Thanks.

  • 写回答

2条回答 默认 最新

  • douxing5199 2013-09-23 16:27
    关注

    You have subequeries in the states and local_govt portions of the WHERE statement. Presumably there are rows for a given value of $term where those queries will return a resultset of more than one row. Because you are using rLIKE, which expects to evaluate against one value (rather than multiple), the overall query will error out.

    Instead, refactor as follows:

    $query .= " WHERE state IN (SELECT id FROM states WHERE upper(name) rLIKE '{$term}')";
    $query .= " OR lga IN (SELECT id FROM local_govt WHERE upper(name) rLIKE '{$term}')";
    

    this will account for that contingency.

    Please note that the query as written is unlikely to be very performant. Since you are scanning many different columns, it would be best to try not to use regex here, because the optimizer won't be able to leverage indices. Ideally, reduce it to a constant, so that you can use:

    SELECT * FROM cong WHERE $term IN (upper(name), upper(address)...)
    

    but that may not be possible given your requirements. If that's the case, I would probably look at the design of your program and try to split the query into a lookup against one column at most from the application side, e.g.:

    SELECT * FROM cong WHERE $term rLIKE upper(NAME)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

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