Mysql IN clause, php array

Question

I know that this has been asked and answered before, but for the life of me I cannot find where I am going wrong. My code is below.

$backa = array("1", "7", "8", "9", "12");
$backaa = implode(",", $backa);
/* code to create connection object $wkHook */
$getOpt=$wkHook->prepare("select movementId, movementName from Movement where movementId IN ($backaa) order by movementName asc");
$getOpt->execute();
$getOpt->store_result($id, $name);

Every time I run this I get one of two errors depending upon how I use the $backaa variable. More often than not I get a call to a non-object error indicating that $getOpt is not a proper Mysql query. I have tried every fashion of quoting, bracketing, etc for the $backaa variable but it's just not working for me. What obvious thing am I missing?

Answer 1

Aren't you just missing a )? It should be

$getOpt=$wkHook->prepare("select movementId, movementName from Movement where movementId IN ($backaa0) order by movementName asc");

Answer 2

The var in your query is $backaa0 and in your code is $backaa. And you are missing a ")".

$backaa = implode(",", $backa);
/* code to create connection object $wkHook */
$getOpt=$wkHook->prepare("select movementId, movementName from Movement where movementId IN ($backaa0 order by movementName asc");

Answer 3

As already pointed out, your query is invalid SQL. Read the error messages: they're there to help you.

Whatever, if you inject parameter values into plain SQL, you miss the whole point of prepared statements.

None of the DB libraries I've worked with support parameters that expand to more than one parameters. In general, you need to generate n different parameters from your array and bind them all individually so you end up with:

$getOpt=$wkHook->prepare("select movementId, movementName from Movement where movementId IN (?, ?, ?, ?) order by movementName asc");

It's easy to automate with a combination of array_keys() and implode().

Answer 4

If you bind $backaa, you are essentially saying:

...where movementId IN ('"1", "7", "8", "9", "12"') order by...

See, what's happening? It's being treated as 1 parameter, namely a string consisting of numbers, commas, and quotes. So I don't see how that would work.

As for binding it as an array, that makes more sense, but it sounds like that's not working out for you so I don't know.

I think this would work:

...where movementId IN (".$backaa.") order by...

But then you're not really binding anything, so you might as well break out your real_escape_string(). I suspect this may have been how you did this before.

Answer 5

the problem is the implode, try:

$backaa = "'". implode("', '", $backa) ."'";