MySqli query like any values in a PHP array

Question

I have a PHP array containing values and I want to query the database to find any results where the ItemName contains any of the values in the array.

$current_item = 'Big Fancy Red Car';

$words = explode(' ',$current_item); // Array of words to search

And the query:

SELECT ID FROM store_accessories WHERE ItemName LIKE '%$words%'

How do I select the ID of any items in the table whose ItemName contains any of the values in the $words array?

What is the table engine of store_accessories? If possible, a FULLTEXT index is an ideal solution, available in MyISAM tables before MySQL 5.6, and InnoDB after. — Michael Berkowski
– Michael Berkowski, Commented Aug 9, 2015 at 22:30
See also these answers which build up a long string of column LIKE %$word% OR... conditions. Considerably less efficient than a FULLTEXT index. — Michael Berkowski
– Michael Berkowski, Commented Aug 9, 2015 at 22:33

Gonz · Accepted Answer · 2015-08-09 23:32:19Z

1

You can do it like this:

First explode the value into an array:

$terms = explode(' ', $search_term);

Loop the array to add the LIKE

foreach ($terms as $term) { $term = mysql_real_escape_string($term); $likes[] = "field LIKE '%$term%'"; }

Add to the query:

$sql = "select * from table where"; $sql .= implode(' OR ', $likes);

edited Aug 9, 2015 at 23:32

answered Aug 9, 2015 at 22:37

Gonz

1,20913 silver badges26 bronze badges

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

Comments

maraca · Accepted Answer · 2015-08-09 23:02:48Z

0

You can get the LIKE like this directly, also substituting ' with '' and escaping _ and %:

$field = "ItemName";
$like = "$field LIKE '%".str_replace(
   array("'" , "_"  , "%"  , " "                   ),
   array("''", "\\_", "\\%", "%' OR $field LIKE '%"),
   $currentItem)."%'";

answered Aug 9, 2015 at 23:02

maraca

9,5873 gold badges27 silver badges50 bronze badges

Collectives™ on Stack Overflow

MySqli query like any values in a PHP array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related