CODEWITHSUNDEEP
mysqlarrayssql-like
user1291842
user1291842

Reputation: 53

mysql - finding string from array

Database table: 

id| p1 | p2 | notes
1 | 1  | a  | cat, mouse, dog
2 | 1  | a  | cat, horse, dog

I now need to run a query that selects the row where "notes" does not contain a string defined in the $exclusions array. I have tried the LIKE '%mouse%' operator, but that gave an error. 

$exclusions = array ("mouse");
if($row['p1'] == 1 && $row['p2'] == "a" && $row['notes'] not like '%mouse%') {...}

Thank you.

Upvotes: 0

Views: 381

Answers (2)

Thom Wiggers
Thom Wiggers

Reputation: 7034

if($row['p1'] == 1 && $row['p2'] == "a" && $row['notes'] not like '%mouse%') {...}

This is not MySQL syntax. It looks like PHP, and in PHP you can not use LIKE. Try a string comparison operator like strstr. http://php.net/manual/en/function.strstr.php

Mysql Style

A query to get all rows without mouse could be this: 

 SELECT * FROM `tablename` 
 WHERE `notes` NOT LIKE '%mouse%';

Or to get the exclusions from a php array:

 $condition = "WHERE ";
 $first = true;
 foreach($exclusions as $ex) {
      if(!$first) $condition .= " AND "; // or use OR
      $condition .= "`notes` NOT LIKE \"%$ex%\"";
      $first = false;
 }

 $complete_query = "SELECT * FROM `tablename` $condition;";

Upvotes: 0

Gaʀʀʏ
Gaʀʀʏ

Reputation: 4540

Looks like you are doing the logic in a mix of PHP code and sql. To do it in php, you can do 

!strstr($row['notes'], 'mouse')

That says "If there is no occurrence of "mouse" in $row['notes']"

The ! will make it return true if there is no occurrence.

Upvotes: 1

Related Questions