Freya Freya - 11 months ago 38
PHP Question

return rows where column in variable array in php and sql

I am writing a query that will return records when the value of one of the columns matches any of the elements of an array. If I use a static array it works (see sample):

sql = "SELECT *
FROM tasks
tasks.type IN ('homework', 'chores', 'laundry')";

What if I have an array of tasks that is populated from user input that I want to use from comparison? Like this:

$task_list = //declaration and populate the array. I know it works, so I'm skipping it

sql = "SELECT *
FROM tasks
tasks.type IN "+$task_list+" ";

This gives me an error.
I really am not any good at mixing php with sql and did search for the answer ... maybe I wasn't able to search properly for the right syntax.

For those that want to know,
prints the following:

array(3) { [0]=> string(8) "homework" [1]=> string(6) "chores" [2]=> string(7) "laundry" }

Answer Source

It fails because $task_list is an array, you should do something like this :

$task_list_str = sprintf('"%s"', implode('", "', $task_list));
$sql = 'SELECT *
       FROM tasks
           tasks.type IN (' . $task_list_str . ')';

It's a bit convoluted, but it stays as close as possible from your code.

Hope this helps.