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
– Steve

2014-07-28 10:45:29 +00:00
Commented Jul 28, 2014 at 10:45 — Steve
– Steve, Commented Jul 28, 2014 at 10:45
... or don't bother with the database!!!

Strawberry
– Strawberry

2014-07-28 12:54:57 +00:00
Commented Jul 28, 2014 at 12:54 — Strawberry
– Strawberry, Commented Jul 28, 2014 at 12:54

Stefan Steiger · Accepted Answer · 2014-07-28 11:10:07Z

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, 2014 at 11:10

answered Jul 28, 2014 at 11:02

Stefan Steiger

83.1k70 gold badges405 silver badges461 bronze badges

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

6 Comments

Stefan Steiger Over a year ago

@DarkBee: stackoverflow.com/questions/24952900/…

Anurag Jain Over a year ago

@Quandary: I also want to say array can be of unlimited no. of elements. I am showing just two elements only for example purpose

Michal Over a year ago

@AnuragJain If this answere helped you or did what you wanted / expected, please mark it as such.

Anurag Jain Over a year ago

@Michal-sk웃 No thats not the answer I am looking for because my table does not support full text search

Stefan Steiger Over a year ago

@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 );

|

Community · Accepted Answer · 2017-05-23 12:31:40Z

1

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.

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Jul 28, 2014 at 12:40

Marcus Adams

54k9 gold badges98 silver badges146 bronze badges

1 Comment

Stefan Steiger Over a year ago

If you use WHERE 1=1 instead of WHERE 1, then it will be compatible with standard-sql.

Anurag Jain · Accepted Answer · 2014-07-28 13:31:39Z

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, 2014 at 13:31

Anurag Jain

1949 bronze badges

2 Comments

Stefan Steiger Over a year ago

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.

Marcus Adams Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related