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

Question

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.

Answer 1

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)