mysql subquery with multiple results error

Question

I have searched for a solution to this problem, some are close but cannot seem to find a solution. I have a table of "runs" with various information, one column being information on the company it applies to. I also have a table of companies storing all the information of the company. In the company column of the runs table, the company is identified by the "indexer" from the company table.

Now i have a search where you can search for any information about the run to find any number of records containing the search phrase, including company. Being the the company column of the run table just has in identifier number ie. 34, 23, 5 etc i need to also check for matches in the name column of the company table.

This is my query which works fine if only one company matches the search results. I need to return all company's that match.

$sql_results = "SELECT * FROM runs WHERE name LIKE '%$searchname%' OR company = (SELECT indexer FROM companies WHERE name LIKE '%$searchname%' ) OR feild LIKE '%$searchname%' ORDER BY date_due";

    while($result_results = @mysql_fetch_array($query_results))
    {
    $resultnm_array[]=$result_results['name'];
    $cmp_id = $result_results['company'];
        $sql2 = "SELECT name FROM companies WHERE indexer = '$cmp_id' LIMIT 1";
        $query2 = @mysql_query($sql2);
        $result2 = @mysql_fetch_array($query2);
        $resultcpy_array[]=$result2['name'];
    $resultfld_array[]=$result_results['feild'];
    $resultdt_array[]=$result_results['date_due'];
    $resultid_array[]=$result_results['indexer'];
    }

I am inputting results into an array and using the TBS template engine to create result tables etc. I am a bit of a novice so any help would be appreciated. From what I gather I need to use join. But i cant seem to make it work...

Answer 1

Can you not just use IN:

SELECT * 
FROM runs 
WHERE name LIKE '%$searchname%' 
    OR company IN (
        SELECT indexer 
        FROM companies 
        WHERE name LIKE '%$searchname%' ) 
    OR feild LIKE '%$searchname%' 
ORDER BY date_due

Given your comments: "works fine if only one company matches the search results" -- that leads me to believe if multiple companies match the search criteria, the query fails because of the =.

---

Given your edits, you could make this simpler using a JOIN and you'd no longer require the additional select statement in your loop:

SELECT r.*, c.name
FROM runs r
    JOIN companies c on r.company = c.indexer
WHERE r.name LIKE '%$searchname%' 
    OR r.feild LIKE '%$searchname%' 
ORDER BY r.date_due