How do I select values from the database that are separated by a comma?

#1

I have a field in the database that says ‘category’ its values ​​are: ‘1,2,3,4’. I use ajax to select a category to output a product from the database. But I can not successfully extract the data from the database because I can not compare the data I send with those from this field.

The field in the database: category = 1,2,3,4

The data I select by ajax and compare it with this field: $_POST['category'] = 1,2

I try to compare them with the following code:

	        if ($_POST['category']!="") {
	          $cat_filter = implode(",", $_POST['category']);
	          $sqlProducts[] = "( category IN('".$cat_filter."'))";
	        }

I also tried with FIND_IN_SET

#2

I could use a little more info, but I assume category can only have a single value. In which case, you need to remove the single quotes:

$sqlProducts[] = "( category IN(".$cat_filter."))";
#3

A) Don’t store a comma delimited list of values in a column. It takes more code to insert/update the values to prevent duplicates and the query cannot use an index, so any attempt to find data will be slow. Store this type of data in a separate table, one row per value, related back to the parent data through the parent’s id. An IN() comparison will work for properly normalized data.

B) Don’t put external data directly into an sql query statement. Use a prepared query, with place-holders for each value, then supply the actual data when executing the query. An IN() comparison requires a separate prepared query place-holder for each value.

C) An IN() comparison won’t work for a comma separated list.

2 Likes
#4

Thank you ! I’ll try to redraw the code.