Dynamically bind values PDO with foreach()

Question

I have a need to generate a MySQL query dynamically, and values of specific types may or may not appear in specific column types. Here is a case for a query generated for two x values and one y value, each of which must be present in either of respective sets of columns (please to not read it too close, since the query itself has been tested extensively and works alright if the proper parameters are inserted manually):

SELECT
    *
FROM
    TABLE
WHERE

(
/*start of block x0 */

    (
        (columm_x0 = ':value_type1_index1')
        OR (column_x1 = ':value_type1_index1')
        OR (column_x2 = ':value_type1_index1')
        OR (column_x3 = ':value_type1_index1')
        OR (column_x4 = ':value_type1_index1')
    ) 
    /* end of block 0*/
    /*start of block x1 */
    OR (
        (columm_x0 = ':value_type1_index2')
        OR (column_x1 = ':value_type1_index2')
        OR (column_x2 = ':value_type1_index2')
        OR (column_x3 = ':value_type1_index2')
        OR (column_x4 = ':value_type1_index2')
    ) 
    /*end of block x1*/
    /*start of block y1*/
    AND (
        (columm_y0 = ':value_type2_index1')
        OR (column_y1 = ':value_type2_index1')
        OR (column_y2 = ':value_type2_index1')
        OR (column_y3 = ':value_type2_index1')
        OR (column_y4 = ':value_type2_index1')
    ) /*end of block y1*/
)

This whole query is supplied to $query variable. So each time we must search for values in all specific columns no matter what. The parameters themselves are supplied as array:

$values = Array ( [type1] => Array ( [0] => value1 [1] => value2 )[type2] => Array ( [0] => value3 ))

My PDO looks like the following:

try {
    $connect = new PDO("mysql:host=".$db['server'].";dbname=".$db['db'], $db['mysql_login'], $db['mysql_pass'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
    $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt=$connect->prepare($query);
    foreach ($values as $type => $typevalue){
        foreach ($typevalue as $element => $elementvalue){
            $parameter = ":value_{$type}_index{$element}";
            $stmt->bindValue($parameter, $elementvalue, PDO::PARAM_STR);//i think here is the problem!
            echo "<br>$parameter = $elementvalue<br>"; //shows exactly correct parameter and value
        }
    }
    if ($stmt->execute() AND $stmt->rowCount() > 0){
        echo "success";
        //do some stuff
        } else {
        echo "false".' '. $stmt->rowCount() . '<br>';
        $stmt->debugDumpParams();
    }
}
catch(PDOException $e) {
    echo  $e->getMessage();
}

The resulting query using pdo always returns 0 rows although the parameters replaced with values manually result in success. As i said above, i don't think that bindValue() method allows variables in parameter. Any help would be appreciated.

[How to create MCVE for a PDO related problem.](https://phpdelusions.net/pdo/mcve) — Your Common Sense, May 03 '17 at 14:58
@YourCommonSense Thank you very much on your notes, I will stick to MCVE guideline next time. It appears that this time, however, all I had to do was to remove the quotes around parameters in query: `(column_x1 = :value_type1_index2)` instead of `(column_x1 = ':value_type1_index2')` — RWS, May 04 '17 at 07:04
@YourCommonSense I would like to appeal that this is not a duplicate question on the grounds that I couldn't have predicted that the issue was caused by improper quote use. My initial suspicion was that problem was caused by the way of dynamic values binding (use of variables in parameter). — RWS, May 04 '17 at 07:56

score -1 · Answer 1 · answered May 04 '17 at 07:05

-1

All i had to do was to remove the quotes around the parameters in query so this is correct:

(column_x1 = :value_type1_index2)

and this is NOT correct:

(column_x1 = ':value_type1_index2')

answered May 04 '17 at 07:05

RWS

538
5
14

Dynamically bind values PDO with foreach()

1 Answers1