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.