CODEWITHSUNDEEP
phpmysqlregex
Stan
Stan

Reputation: 963

mysql regular expression for exact one or two word match

I was going to use the following code to display some sales numbers for 100 car brands:

$brand = str_replace ('-', '[- ]', $_GET['brand']);

$sql = "SELECT year, brand, number FROM `exampletable` WHERE brand REGEXP :brand ORDER BY `year` DESC";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":brand", $brand);
$stmt->execute();
if($stmt->rowCount())
{ while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
?>
<?php echo $result['year'];?>
<?php echo $result['number'];?>

<?php }} else { echo '0'; } ?>

Now this code works fine for 98 brands in my table, but there's a problem with the following brands because they contain the same word:

As a result the numbers for "Matra Renault" not only appear on the specific webpage for that brand, but also on the webpage for "Renault". The same thing happens for "Volkswagen" where I see the numbers for "Volkswagen Porsche" as well.

So, I completely understand why my code doesn't work, but I don't have enough knowledge about this topic to find a quick solution. I've already read older questions here but none of them seemed to discuss the same problem.

Note: As no other data will be added to my table in the future, a solution specifically for the above mentioned brands would suffice.

Upvotes: 1

Views: 115

Answers (1)

ishegg
ishegg

Reputation: 9937

The problem is that you're selecting with REGEXP, while you apparently don't need it. The regular expression means Volkswagen will match anything with Volkswagen in the brand's name, so you get those other unwanted results, specifically because your regular expression is just the whole brand name.

Note, incidentally, that searching for Volkswagen Porsche won't return Volkswagen, because Porsche is not in it. See this simplified example vs this.

The solution to your problem would be to simply select the exact name of the brand you're looking for with =:

$sql = "SELECT year, brand, number FROM `exampletable` WHERE brand = :brand ORDER BY `year` DESC";

And another solution, though total overkill, would be to fix your regular expression to match the words exactly:

$brand = "^".$brand."\$";

Then, it'll only look for exact matches. See example

Upvotes: 2

Related Questions