MySQL PHP, SELECT WHERE condition is an array

I am trying to SELECT products from db WHERE condition is an array. My method is working when array is declared but not when elements of array are generate after submitting form.

    $selected_categories = array();        //create empty array

    if(isset($_POST['submit'])){          //if form is submitted

        $name = $_POST['category'];

        foreach ($name as $category){
            $selected_categories[] = $category;          //add all checked checkboxes values into the array, every single $category element is text i.e. programming science
        }


        $get_products = "SELECT P.*, C.category_name, GROUP_CONCAT(category_name SEPARATOR ', ') AS cat FROM products P NATURAL JOIN categories C NATURAL JOIN product_to_categories WHERE category_name IN ('$selected_categories') GROUP BY product_name ORDER BY 1 DESC LIMIT $start_from,$per_page";
    }

the problem (i think) is in array, because when I am using one element from the array:

... WHERE category_name IN ('$selected_categories[1]')...

is working correctly it is also working when using:

... WHERE category_name IN ('programming', 'science')...

Answer

WHERE category_name IN ('$selected_categories') is wrong – the parameter will be treated as ONE string literal, not as a list of separate literals.

Use WHERE FIND_IN_SET(category_name, '$selected_categories').

Separate values in $selected_categories must be separated by comma strictly (if some another separator is used then replace it).

Advertisement

Answer