How to return a row where all values in an array exists

Question

I have a database where values are stored like this:

Id                 Description
 1                  one,two,three,four,five
 2                  four,three,two
 3                  five,one

and also I have dynamic array coming from user like this:

1) $arr = array("five","one");

so I have to select description with id=1 and id=3 because their descriptions contains these words.

Another example

2) $arr = array("two","three");

so I have to select description with id=1 and id=2 because their descriptions contains these words.

so how to execute that query? Anybody can help?

Looks like you database needs normalizing – Steve Jul 28 '14 at 10:45 — Steve, Jul 28 '14 at 10:45
... or don't bother with the database!!! – Strawberry Jul 28 '14 at 12:54 — Strawberry, Jul 28 '14 at 12:54

Stefan Steiger · Answer 1 · 2014-07-28T11:10:07.053

1

You could try fulltext-search:

like

string str = string.Join(" +", $arr);
str = str.Replace("'","''");

string strSQL = @"SELECT * FROM 
your_table WHERE 
MATCH ( Description) 
AGAINST ('" + str + "' IN BOOLEAN MODE);"

Which results in:

 $sql = mysql_query("SELECT * FROM 
                     your_table WHERE 
                     MATCH ( Description) 
                     AGAINST ('+one +five' IN BOOLEAN MODE);");

Make sure you remove all stopwords from the mysql config file.

As for the plus sign:

1st sql example

SELECT * FROM your_table 
          WHERE match(Description) against('+one +five' IN BOOLEAN MODE) LIMIT 10";

result: contains the words 'one' and 'five'

2nd sql example

SELECT * FROM your_table 
           WHERE match(Description) against('+one five' IN BOOLEAN MODE) LIMIT 10";

result: contains the words 'one', but rank rows higher if they also contain 'five'

3rd sql example

SELECT * FROM your_table 
           WHERE match(Description) against('one five' IN BOOLEAN MODE) LIMIT 10";

result: contains the words 'one' or 'five'

edited Jul 28 '14 at 11:10

answered Jul 28 '14 at 11:02

Stefan Steiger

78,642
66
377
442

@DarkBee: http://stackoverflow.com/questions/24952900/mysql-search-query-keep-not-working/24953897#24953897 – Stefan Steiger Jul 28 '14 at 11:07
@Quandary: I also want to say array can be of unlimited no. of elements. I am showing just two elements only for example purpose – Anurag Jain Jul 28 '14 at 11:13
@AnuragJain If this answere helped you or did what you wanted / expected, please mark it as such. – Michal Jul 28 '14 at 11:42
@Michal-sk웃 No thats not the answer I am looking for because my table does not support full text search – Anurag Jain Jul 28 '14 at 11:46
@Anurag Jain: Full-text search is supported since MySQL V 3.23.23. You need to use the MyISAM engine for the table and add the fulltext-index: ALTER TABLE your_table ADD FULLTEXT( Description ); – Stefan Steiger Jul 28 '14 at 12:35
Full-text search is supported in both MyISAM and InnoDB since MySQL v5.6. – Marcus Adams Jul 28 '14 at 12:52

score 1 · Answer 2 · edited May 23 '17 at 12:31

Barring full-text search or properly normalizing your data (i.e. don't use comma delimited data if you must search through it), then you can use FIND_IN_SET() to search comma delimited strings.

However, since the first argument of FIND_IN_SET() doesn't accept a comma separated list, you must first separate the search terms (see PHP's explode).

With some iteration, you can create a query like this:

SELECT * FROM your_table
WHERE FIND_IN_SET('five', description)
  AND FIND_IN_SET('one', description)

Sometimes, when you're dynamically creating such queries, it's easier to create a query like this:

SELECT * FROM your_table
WHERE 1 = 1
/* Start dynamic portion */
  AND FIND_IN_SET('five', description)
  AND FIND_IN_SET('one', description)

Or, actually, if you're properly using PDO bound parameters:

SELECT * FROM your_table
WHERE 1 = 1
/* Start dynamic portion */
  AND FIND_IN_SET(?, description)
  AND FIND_IN_SET(?, description)

Use PHP's bindParam() to bind each parameter.

If you use WHERE 1=1 instead of WHERE 1, then it will be compatible with standard-sql. — Stefan Steiger, Jul 28 '14 at 13:01

score 1 · Answer 3 · answered Jul 28 '14 at 13:31

1

I have done it using "like" parameter. See the below code

$count = 1;


 foreach($arr as $a){
            if($count == 1){
               $str ="`Description` LIKE '%$a%'";
            }
             else{
               $str.= "AND `Description` LIKE '%$a%'"; 
            }
            $count++;
        }
$query = Select `id` From `my_table` Where $str;

This is not the best way.But this is giving me the result I am wanting

answered Jul 28 '14 at 13:31

Anurag Jain

194
9

If you do WHERE (1=1) + ==IN FOR LOOP === AND `Description` LIKE '%$a%' === END FOR LOOP === Then you can skip the count part, and it works great if you have a null array, and you save 7 lines of code. – Stefan Steiger Jul 28 '14 at 14:05
This method could work if you only have 'one' through 'thirteen', but if you go further, 'four' also appears in 'fourteen'. That's why `FIND_IN_SET()` is typically used for this kind of search, over wildcard string search. If you're to the point where you're searching for ',four,', then it's really getting complex. – Marcus Adams Jul 28 '14 at 14:08

How to return a row where all values in an array exists

3 Answers3