What is the proper way of doing the following in a mysql query?:
$string="apple banana orange";
mysql column A = "banana"
mysql query: column A not included in $string
Thanks!
-
What are you trying to do?Gordon Linoff– Gordon Linoff2013-03-21 20:32:45 +00:00Commented Mar 21, 2013 at 20:32
-
Thanks all! Let me try to clarify. I have a string $string="apple banana orange"; How can I do a mysql query where: a) column A IS included in $string b) column A IS NOT included in $string. Ex mysql_query("select * from DB where [A IS NOT included in $string]");John Anderson– John Anderson2013-03-21 22:15:00 +00:00Commented Mar 21, 2013 at 22:15
Add a comment
|
3 Answers
If each row has a column called A that has space separated tokens you can run this:
SELECT * FROM table_name
WHERE LOCATE(CONCAT(' ',A,' '),'apple banana orange') = 0;
The reason I padded a blank on both sides would be to cover a situation like this:
SELECT * FROM table_name
WHERE LOCATE(CONCAT(' ',A,' '),'pineapple banana orange') = 0;
As a character string
appleis inapple banana orangeappleis inpineapple banana orange
In terms of semantics
appleis inapple banana orangeappleis not inpineapple banana orange
What if you were looking for grape ?
As a character string
grapeis inapple banana orange grapegrapeis inapple banana orange grapefruit
In terms of semantics
grapeis inapple banana orange grapegrapeis not inapple banana orange grapefruit
Give it a Try !!!
CAVEAT
The query itself would yield a full table scan.
You should look into doing FULLTEXT indexes.
Comments
SELECT * FROM table_name
WHERE A NOT IN ('apple', 'banana', 'orange');
answered Mar 21, 2013 at 20:34
Hunter McMillen
61.8k25 gold badges124 silver badges176 bronze badges
Comments
Maybe something like this? (I have not tested it though but I think it would work)
<?php
$string = "apple banana orange";
//Make an array of each fruit in string (with delimiter space)
$items = explode(" " , $string);
//None of the fruits in $string would be listed in resultset
$query = "SELECT * FROM fruits WHERE columnA NOT IN ($items)";
//db-handling...
?>
UPDATE: Don't make an array... ..do this instead...! $items = str_replace(" ", ",", $string);
answered Mar 21, 2013 at 20:47
bestprogrammerintheworld
5,5299 gold badges48 silver badges76 bronze badges
2 Comments
Michael Berkowski
A PHP array cannot be placed into a query's
IN () clause.
bestprogrammerintheworld
Of course! You're right! $items could be using str_replace() - replacing spaces with comma in $string.
