The bindValue is not working properly here

Question

if($_GET['s']<>''){

    $explode = explode(' ',$_GET['s']);
    $num = count($explode);

    for($i=0;$i<$num;$i++){
        $busca .= " `nome` LIKE :busca$i ";//OR `district` LIKE :busca$i OR `city` LIKE :busca$i OR `area` LIKE :busca$i ";

        if($i<>$num-1){ $busca .= ' AND '; }

    }
    $buscar = DB::getConn()->prepare("SELECT * FROM users WHERE $busca");
    for($i=0;$i<$num;$i++){
        $buscar->bindValue(":busca$i",'%'.$explode[$i].'%',PDO::PARAM_STR);
    }
    echo "$busca";  
    echo "$explode[0]";
    $buscar->execute(); 

}

The echos work alright but the its not binding the values on $buscar it returns:

nome LIKE :busca0

It does not put the value from $explode[0] on $busca0 and the echo for explode is working properly...

I'm pretty sure bind_value is (always) working properly, though you might be using it wrongly "here" — Ivan Hušnjak, Sep 10 '13 at 18:55
did you tried to echo your created query? Show us the result — Ivan Hušnjak, Sep 10 '13 at 18:57
So any sugestions for using it right in this particular code? — Fernando H. E. Bellincanta, Sep 10 '13 at 18:57
I don't understand what 'it returns' means here. Can you check the log and see how the query was _executed_? — raina77ow, Sep 10 '13 at 19:05

LexLythius · Answer 1 · 2013-09-10T19:44:47.590

0

EDIT: Note that bindValue won't replace your query string prior to execution.

So, as someone else asked, you need to see what is actually being executed at the DB server.

You may have to turn on MySQL general log to do so.

EDIT: Since you can't use the general log, try PDO debugdumpparams

--

Note that when feeding more than one parameter, your query base looks like this:

 `nome` LIKE :busca0  AND  `nome` LIKE :busca1  AND  `nome` LIKE :busca2

Which won't work unless all params are the same

edited Sep 10 '13 at 19:44

answered Sep 10 '13 at 19:02

LexLythius

1,904
1
12
20

yeah but im testing it with only one parameter and it dont work... – Fernando H. E. Bellincanta Sep 10 '13 at 19:03
What DB driver are you using? – LexLythius Sep 10 '13 at 19:05
1

... and turn this log off after checking is done. ) – raina77ow Sep 10 '13 at 19:16
external hosting site does not allow me to see the sql logs :( – Fernando H. E. Bellincanta Sep 10 '13 at 19:22
The try this: http://php.net/manual/en/pdostatement.debugdumpparams.php – LexLythius Sep 10 '13 at 19:24
the debugdumpparams prints: "SQL: [47] SELECT * FROM users WHERE `nome` LIKE :busca0 Params: 1 Key: Name: [7] :busca0 paramno=-1 name=[7] ":busca0" is_param=1 param_type=2" – Fernando H. E. Bellincanta Sep 15 '13 at 15:55
I feel very stupid right now, the problem was the collation that was case sensitive... Thanks for the help anyway :D – Fernando H. E. Bellincanta Sep 15 '13 at 18:02

cske · Answer 2 · 2013-09-10T19:14:53.477

0

if($_GET['s']<>''){

    $explode = explode(' ',$_GET['s']);
    $num = count($explode);

    for($i=0;$i<$num;$i++){
        $busca .= " `nome` LIKE :busca$i ";//OR `district` LIKE :busca$i OR `city` LIKE :busca$i OR `area` LIKE :busca$i ";

        if($i<>$num-1){ $busca .= ' AND '; }

    }
    $buscar = DB::getConn()->prepare("SELECT * FROM users WHERE $busca");
    for($i=0;$i<$num;$i++){
        $buscar->bindValue(":busca$i",'%'.$explode[$i].'%',PDO::PARAM_STR);
    }
    echo "$busca";  
    echo "$explode[0]";
    echo var_export($buscar->execute()->fetchAll(),true); 

}

bind will escape ' and you don't want that

edited Sep 10 '13 at 19:14

answered Sep 10 '13 at 19:05

cske

2,233
4
26
24

You've just moved the % from the bound value to the query base, or am I missing something? – LexLythius Sep 10 '13 at 19:06
yes, but may not work, haven't tried sorry about that – cske Sep 10 '13 at 19:08
Yeah, and it didnt worked :P just changed the return value to: `nome` LIKE '%:busca0%' – Fernando H. E. Bellincanta Sep 10 '13 at 19:10
If it didnt worked like that it should have returned a PDO exception and it does not – Fernando H. E. Bellincanta Sep 10 '13 at 19:11
What do you mean by `return value`? This function in your question doesn't return _anything_ at all; can you update the question adding the returning part of the code? – raina77ow Sep 10 '13 at 19:11
What does `echo $buscar->queryString` prints – cske Sep 10 '13 at 19:13
return i mean when i do an echo "$busca"; – Fernando H. E. Bellincanta Sep 10 '13 at 19:13
echo $buscar->queryString prints: SELECT * FROM users WHERE `nome` LIKE :busca0 – Fernando H. E. Bellincanta Sep 10 '13 at 19:14
And it's its normal behavior. You'll see that param when you check the query itself on MySQL side, not in PHP. – raina77ow Sep 10 '13 at 19:15
Thats fine to see the param, did not excepted replacement – cske Sep 10 '13 at 19:18

The bindValue is not working properly here

2 Answers2