Search results returning wrong records - need some help

[Jax2]

Hi all. 

 

I have a database that contains a large list of businesses (over 35,000) and I am trying to create a search page to display what people are looking for (I.e. Plumbing Detroit or Plumbing 48312)... I am getting results with my SQL query, but they are not right.

 

Here is the submission form and the resulting search query...

 

Index.php:

<form action="search.php" method="get">
    <input type="text" name="type" id="query" size="30" placeholder="Business type/name"/> <input type="text" name="location" size="15" placeholder="City, Town or Zip">
    <input type="submit" value="Search"/>
</form>

Search.php:

if (isset($_GET['type'])){
    $type=$_GET['type'];
}
if (isset($_GET['location'])){
    $location=$_GET['location'];
}

$stmt = $db->prepare("SELECT * 
FROM  `listings` 
WHERE  `Primary_City` LIKE '%$location%' 
OR  `Primary_Zip` LIKE '$location'
AND  `Line_Of_Business` LIKE '%$type%'
OR  `Primary_Industry` LIKE '%$type%'
");
$stmt->execute();
$total=$stmt->rowCount();
echo "<div style='padding-left:50px; padding-top:10px;'> <strong>Your search for ".$type." in ".$location." returned ".$total." records.</strong></div><br /><br />";
while ($row=$stmt->fetch())
{?>

This returns (for one city!) over 5400 results, when I type in Plumbing Midland into the search boxes (type, location), with results from all different cities in the state, instead of just the city name or zip code I entered and I am unsure as to how to fix it to only show the records that have a Primary_Zip or Primary_City that equals $location...

 

Thanks if you can help!

[mac_gyver]

the query will match rows that result in a TRUE value for the WHERE clause. AND has a higher operator precedence then OR, so your query is matching all rows with a city like $location or industry like $type or (zip like $location AND business like $type).  to match the city or zip code like $location AND a business or industry like $type you would need to use - 

WHERE
 (`Primary_City` LIKE '%$location%' OR `Primary_Zip` LIKE '$location')
 AND
 (`Line_Of_Business` LIKE '%$type%' OR `Primary_Industry` LIKE '%$type%')

next, you are not using prepared queries correctly. you must put place-holders in the query where the external data values are at, then bind the external data to those place-holders. since you are using a LIKE comparison, the wild-card % characters must actually be in with the bound data, not in the query (i notice that you are probably also missing some % for the Primary_Zip term.)