How can I use PHP arrays in queries?

Question

Hi guys I have this query:

$URLA=implode("','",$Array);
$Data=$connection->prepare("SELECT Category,ID FROM SITES WHERE URL IN('".$Array."') AND Email=:Email ORDER BY Category DESC");
$Data->bindValue(':URL', $URL);
$Data->bindValue(':Email', $Email);
$Data->execute();

This code give this error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens. How can I solve this problem? Note that Array is a variable that comes out from a while loop.And contain more that one variable. I tryied bind this array like this:

$Data->bindValue(':Array', $Array);

But this didn't worked :'(

you're also not using `:URL` in the query. I hope you weren't intending on using this for `WHERE URL`. — Funk Forty Niner, Dec 29 '17 at 18:30
@ Funk Forty Niner nono, because the implode will do the array like: 1','2,' so with ' 'it will be '1','2' — user9154057, Dec 29 '17 at 18:32
yes, and with the implode it will become like: WHERE URL IN ('1','2') — user9154057, Dec 29 '17 at 18:35
@Abdulla Nilam the array can contain also string, for example WHERE URL IN ('1','2','ABC') — user9154057, Dec 29 '17 at 18:38

Abdulla Nilam · Answer 1 · 2017-12-29T18:45:51.497

0

As you mentioned, your array looks like this array(1,2,'ABC');,

try this

$URLA= implode("', '", $array); # wrapping with single quote 

$Data=$connection->prepare("SELECT Category,ID FROM SITES WHERE URL IN(".$URLA.") AND Email=:Email ORDER BY Category DESC");
// $Data->bindValue(':URL', $URL); Seems no longer needed.
$Data->bindValue(':Email', $Email);

Or

$URLA= implode("', '", $array); # wrapping with single quote 

$Data=$connection->prepare("SELECT Category,ID FROM SITES WHERE URL IN(:url) AND Email=:Email ORDER BY Category DESC");
$Data->bindValue(':URL', $URLA);
$Data->bindValue(':Email', $Email);

edited Dec 29 '17 at 18:45

answered Dec 29 '17 at 18:41

Abdulla Nilam

36,589
17
64
85

Could I bind the array for avoid SQL Injection? – user9154057 Dec 29 '17 at 18:44
1

you can use this `$Data->bindValue(':URL', $URL);` with `IN(:url)` – Abdulla Nilam Dec 29 '17 at 18:45
@user9154057 check the [edit now](https://stackoverflow.com/posts/48026972/revisions) and check this [answer too](https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition) – Abdulla Nilam Dec 29 '17 at 18:46

How can I use PHP arrays in queries?

1 Answers1