mysql - finding string from array

Question

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.

Without a little more to refer to, it's hard to identify what the syntax problem might be...for example I don't see the WHERE...is there and AND missing, or an OR? "NOT LIKE '%mouse%' is correct syntax, so your usage of it within the rest of the SQL statement must be wrong. — GDP
– GDP, Commented May 13, 2012 at 17:12
My guess is the OP wants the logic done in the PHP code. So the SQL statement is most likely a "select * from table"; it is unclear if they want the solution via the sql command or the php code. — Gaʀʀʏ
– Gaʀʀʏ, Commented May 13, 2012 at 17:15

Gaʀʀʏ · Accepted Answer · 2012-05-13 17:19:49Z

1

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.

edited May 13, 2012 at 17:19

answered May 13, 2012 at 17:12

Gaʀʀʏ

4,5403 gold badges43 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1291842 Over a year ago

thanks - this works! how do i modify this if there are more than one string to be excluded?

Gaʀʀʏ Over a year ago

Sorry, I needed to delete that last comment due to an error. It is best to write out the logic on paper for how you want the code to behave, so you don't get mixed up in NOTs, ANDs, and ORs. For example, you dont want any result that has "mouse" or "dog" would look like: !strstr($row['notes'], 'mouse') || !strstr($row['notes'], 'dog') Literally meaning: "No occurrence of mouse OR no occurrence of dog".... You can also write the code without the NOT operand-- "strstr($row['notes'], 'mouse')" - which means, "if(Notes contains mouse) ... "

user1291842 Over a year ago

actually - the || operand appears not to be working, while stacking && one after the other does

Thom Wiggers · Accepted Answer · 2012-05-13 17:43:07Z

0

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;";

edited May 13, 2012 at 17:43

answered May 13, 2012 at 17:12

Thom Wiggers

7,0822 gold badges41 silver badges66 bronze badges

Collectives™ on Stack Overflow

mysql - finding string from array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related