search mysql LIKE using php array

Question

I have a small table with just few columns, one of them is storing the information about the Category as an INT (just a simple number) eg.

id | name             | category

11 | one file         | 1
15 | some other file  | 2

When displaying the data accross the site, I simply convert the category's number with the value stored in the PHP array:

$swm_array = array(
 '1' => 'Electrical',
 '2' => 'HVAC',
 '3' => 'Plumbing',
);

When searching the table using LIKE operator, I can easily find the category if I type a number. What I need is to actually type in the category's value, and not its key and then find it against the entries in the table. I've been trying to include the array to the search using completely weird stuff, but to no vail.

Found here, in these forums, a lead: implode INSIDE the query but I was not able to pull it.

If you guys could help me out here, I'd be eternally grateful :)

My SQL query:

SELECT DISTINCT
    `swm_id`,
    `name`,
    `desc1`,
    `category`,
    `file`
FROM
    `swims`

WHERE
  (
       `name` LIKE '%".$q."%'
    OR `desc1` LIKE '%".$q."%'
    OR `file` LIKE '%".$q."%'
    OR `swm_id` LIKE '%".$q."%'
    **OR `category` LIKE '%".$q."%'**
  )
  AND `active` = '1'

Thanks, a lot!

Regards, Greg

I would suggest that you have another table for the categories keyed by the category number you are using and containing the category name. Then you can join your swims table against this category table, and check against the category name from there. — Kickstart, Mar 13 '14 at 12:17

score 0 · Answer 1 · answered Mar 13 '14 at 12:16

0

Before your query, in php :

$r = array_search($q, $swm_array);

Then in your query :

**OR `category` = '".$r."'**

Note that this will not act as MySQL "LIKE" and will only work out when you don't type the full category.

answered Mar 13 '14 at 12:16

Loïc

11,804
1
31
49

score 0 · Answer 2 · edited May 23 '17 at 10:33

implode() would be a good solution if you were going to do a SQL query using IN (comma-separated-list), but since you're using LIKE, that won't work.

A REGEX like the solution here could work: MySQL LIKE IN()?

But you already have what you need in an array; might as well loop over it and create your query:

$sqlLikes = " 1=1 ";
foreach($swm_array as $swm) {
  $sqlLikes .= " OR  `name` LIKE '%".$swm."%'
    OR `desc1` LIKE '%".$swm."%'
    OR `file` LIKE '%".$swm."%'
    " ;
}

Which will give you $sqlLikes like this: https://eval.in/119441

string(338) " 1=1 OR  `name` LIKE '%Electrical%'
        OR `desc1` LIKE '%Electrical%'
        OR `file` LIKE '%Electrical%'
        OR  `name` LIKE '%HVAC%'
        OR `desc1` LIKE '%HVAC%'
        OR `file` LIKE '%HVAC%'
        OR  `name` LIKE '%Plumbing%'
        OR `desc1` LIKE '%Plumbing%'
        OR `file` LIKE '%Plumbing%'
        "

Then you can:

SELECT DISTINCT
    `swm_id`,
    `name`,
    `desc1`,
    `category`,
    `file`
FROM
    `swims`

WHERE
  (
      $sqlLikes
  )
  AND `active` = '1'

Hello, thank you for your input! The problem is I need to search for Category as well, and the Category is stored in the DB as a number. Hence, I needed to translate the search string into a number, eg. Electric into 1, HVAC into 2, Plumbing into 3 etc. I have no idea how to use the array in the search, more! I don't even know now if this is even possible. Thank you! — Greg Bialowas, Mar 13 '14 at 13:37

score 0 · Answer 3 · answered Mar 17 '18 at 16:29

I need to search for Category as well, and the Category is stored in the DB as a number. Hence, I needed to translate the search string into a number, eg. Electric into 1, HVAC into 2, Plumbing into 3 etc.

The recommended way is to create a category table and make the category entry of the table you've listed here a foreign key to that. That way you can join the two tables and search for a name attribute from the category table.

Barring that, you can create a reversed map of the array. Taking your original array:

$swm_array = array(
 '1' => 'Electrical',
 '2' => 'HVAC',
 '3' => 'Plumbing',
);

You could flip it so you have a reverse map.

$swm_flipped_map = array_flip($swm_array);

This will create:

[
    'Electrical' => '1',
    'HVAC' => '2',
    'Plumbing' => '3',
]

This way you can return the number associated with that category the same way you find items in any array by the key. Such as:

$electrical_num = $swm_flipped_map['Electrical'];

This returns '1'. Just incorporate this into your query using WHERE and OR statements you're already familiar with.

search mysql LIKE using php array

3 Answers3