PHP, MySQL: mysql substitute for php in_array function

Question

Hi all,

Say if I have an array and I want to check if an element is a part of that array, I can go ahead and use in_array( needle, haystack ) to determine the results. I am trying to see the PHP equivalent of this for my purpose. Now you might have an instant answer for me and you might be tempted to say "Use IN". Yes, I can use IN, but that's not fetching the desired results. Let me explain with an example:

I have a column called "pets" in DB table. For a record, it has a value: Cat, dog, Camel (Yes, the column data is a comma separated value). Consider that this row has an id of 1.

Now I have a form where I can enter the value in the form input and use that value check against the value in the DB. So say I enter the following comma separated value in the form input: CAT, camel (yes, CAT is uppercase & intentional as some users tend to enter it that way).

Now when I enter the above info in the form input and submit, I can collect the POST'ed info and use the following query:

$search = $_POST['pets'];
$sql = "SELECT id FROM table WHERE pets IN ('$search') "; 

1. The above query is not fetching me the row that already exists in the DB (remember the record which has Cat, dog, Camel as the value for the pets column?). I am trying to get the records to act as a superset and the values from the form input as subsets. So in this case I am expecting the id value to show up as the values exist in the column, but this is not happending.

2. Now say if I enter just CAT as the form input and perform the search, it should show me the ID 1 row.

3. Now say if I enter just camel, cAT as the form input and perform the search, it should show me the ID 1 row.

How can I achieve the above?

Thank you.

Answer 1

Yes, the column data is a comma separated value

Here is your fault.
No, it shouldn't be comma separated value
And your database structure should be normalized.

Answer 2

I've got several things for you in terms of feedback & in direct response to your questions:

First, I suggest you sanitize the input. Everybody is going to tell you that. For that, see What’s the best method for sanitizing user input with PHP?.

Second, normalize the input with UPPER() or LOWER() if you want to use MySQL and need to store user-formatted input, or use strtoupper() and strtolower() if you wanted to process the input before storing it.

You're still left with the order in the user query. E.g. "cat, dog" ought to yield the same result as "dog, cat". If you were to code that with a LIKE statement, performance issues are going to eat you alive. Not only would you have to create the query dynamically, you'd also end up with huge and unnecessarily complex queries. In short, forget it. You have to change the way you store your data.

One way to accomplish this is by creating a relationship table that references a table of unique user input and your record. This table would look similar to

user_id | pet_id

Every user could have more than one pet_id associated with them. I've set up a database a long time ago the same way you did and ran into the same issues. Performance-wise it never paid off and it's anything but good style. I ended up changing my structure because of that to the above-mentioned method.

Answer 3

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc