when in clause with mysql pdo

Question

I would like to run a simple select query using 'where in' clause with mysql pdo.

$start = 12; $end = 14;

for($i=$start; $i<=$end;$i++)
{
$limitin[] = array($qstep,$i);
}

The array is

Array
(
    [0] => Array
        (
            [0] => 3
            [1] => 12
        )

    [1] => Array
        (
            [0] => 3
            [1] => 13
        )

    [2] => Array
        (
            [0] => 3
            [1] => 14
        )

)

Then

$questionmarks = str_repeat("?,", count($limitin)-1) . "?";

and the query is

$getans = $this->db->prepare("SELECT * 
                              FROM answers
                              WHERE qstep = ? 
                              and ansid in ($questionmarks) ");


$getans->execute($limitin); //$limitin is the array.

I am getting a blank resultset and a notice which is 'Notice: Array to string conversion '

Try this one, I think its already asked [Where in With PDO](http://stackoverflow.com/questions/2373562/pdo-with-where-in-queries) — Arun Kumar, Jul 29 '14 at 03:32
Getting invalid parameter number:mixed as I have qstep as well — Raj, Jul 29 '14 at 03:40
What is it that you're trying to insert, show us an example of what's generating the values for your `IN` — Funk Forty Niner, Jul 29 '14 at 03:44
Plus, add `setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened, see if it yields anything. — Funk Forty Niner, Jul 29 '14 at 03:45
I am not trying to insert.I have a array of values and a single value which I would like to bind with select query. — Raj, Jul 29 '14 at 03:46
Getting Fatal error: Call to undefined function setAttribute() — Raj, Jul 29 '14 at 03:48
Still same error I am getting. I think the problem is in binding the values. — Raj, Jul 29 '14 at 03:51
You're using a `foreach`, right? If so, can you show us the rest of the code you're using to generate it? — Funk Forty Niner, Jul 29 '14 at 03:54

Sean · Accepted Answer · 2014-07-29T04:29:49.047

3

$limitin is a multidimensional array, and is not a valid array to use in $getans->execute($limitin);. You need to create a single array. Try something like ->

$inarray=array();

foreach($limitin as $key=>$val){
       if($key==0) {$inarray[]=$val[0];} // set the qstep as the 1st array value
       $inarray[] = $val[1]; // add each value
}

$questionmarks = str_repeat("?,", count($limitin)-1) . "?";

$getans = $this->db->prepare("SELECT * 
                              FROM answers
                              WHERE qstep = ? 
                              and ansid in ($questionmarks) ");


$getans->execute($inarray);

edit

As @Fred-ii- has pointed out, bindParam() is another way to do this, for example

$questionmarks = str_repeat("?,", count($limitin)-1) . "?";

$getans = $this->db->prepare("SELECT * 
                              FROM answers
                              WHERE qstep = ? 
                              and ansid in ($questionmarks) ");


foreach($limitin as $key=>$val){
       if($key==0) {$getans->bindParam(1,$val[0]);} // set the qstep as the 1st array value
       $getans->bindParam($key+2,$val[1]); // add each value
}

$getans->execute();

edited Jul 29 '14 at 04:29

answered Jul 29 '14 at 04:12

Sean

12,443
3
29
47

So, how does this work without using [`bindParam()`](http://php.net/manual/en/pdostatement.bindparam.php), or am I missing something? – Funk Forty Niner Jul 29 '14 at 04:15
1

@Fred-ii- [`PDO::execute()`](http://php.net/manual/en/pdostatement.execute.php) has the option of adding the query parameters as an array directly in `execute()`, without the need to `bindParam()` -> `public bool PDOStatement::execute ([ array $input_parameters ] )`::`input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed.` – Sean Jul 29 '14 at 04:19
@Fred-ii- It only works with PDO, and not mysqli. Your suggestion of `bindParam()` is probably better, as when placing the array in `execute()` -> `All values are treated as PDO::PARAM_STR`. But since mysql loosely compares values, `('12','13','14')` will be treated like `(12,13,14)` when returning rows. – Sean Jul 29 '14 at 04:25
Since the OP's using integers, then that would work. Will wait to get a response from the OP, see what gives; *cheers* – Funk Forty Niner Jul 29 '14 at 04:27
Another questionable piece of code OP's using is `WHERE qstep = ?` I don't know how OP plans on binding that. OP's question is unclear. – Funk Forty Niner Jul 29 '14 at 04:39
1

@Fred-ii- true, that is why when I saw that the OP was placing `$qstep` as the 1st value of each `$limitin[]`, I did `if($key==0) {$inarray[]=$val[0];}` in the original example, and `if($key==0) {$getans->bindParam(1,$val[0]);}` in my edited example. – Sean Jul 29 '14 at 04:44
Hi guys first of all sorry for replying late as it was around 5 in the morning when I posted this question.Sean thank you very much for your brilliant idea and moreover a fantastic explanation of how array things work with execute function and yes thanks once again for your help.Selected :) – Raj Jul 29 '14 at 08:07
Hi Fred sorry for replying late.Actually in select query the qstep will be just a parameter with a integer value so it doesn't matter (or matter?) if I put the qstep at the beginning of where clause or at the end.Please correct me if I am wrong. And yes thanks very much for your support around half 4 in the morning when the night appeared to be more darker than usual. – Raj Jul 29 '14 at 08:11

when in clause with mysql pdo

1 Answers1