HOW TO LOOP PHP'S PDO BIND PARAM

Question

Im current creating my own query builder now and Im stuck with PDO's prepared statement. Isn't it possible to loop the the PDO's BindParam. I did it using foreach() but it's not working it only on works on the last data that the loop executed.

$sql = "SELECT * FROM users WHERE id = :a OR fname = :b";

$array = array(":a"=>"10002345", "Josh");
$stmt = $conn->prepare($sql); 

foreach($array as $key => $value ) {
    $stmt->bindParam($key, $value);
}

$stmt->execute();

it only binds the last data executed by the loop.

*"it only on works on the last data that the loop executed"* – Please clarify what that means. — deceze, Sep 12 '18 at 06:54
I think you override the params of bind you also have to do __$stmt->execute();__ in the for loop. — Ace of Spade, Sep 12 '18 at 06:57

score 4 · Accepted Answer · edited Mar 27 '23 at 13:18

4

It is better to simply pass your array to execute:

$sql = "SELECT * FROM users WHERE id = :a OR fname = :b";
$array = array("a" => "10002345", "b" => "Josh");
$stmt = $conn->prepare($sql); 
$stmt->execute($array);

Or you can do it even simpler with ? placeholders:

$sql = "SELECT * FROM users WHERE id = ? OR fname = ?";
$array = array("10002345", "Josh"); // you don't even need keys here
$stmt = $conn->prepare($sql);
$stmt->execute($array);

edited Mar 27 '23 at 13:18

Your Common Sense

156,878
40
214
345

answered Sep 12 '18 at 06:48

u_mulder

54,101
5
48
64

Why is it "better" to use `?`? You can pass even named parameters to `execute()`. – GrumpyCrouton Sep 14 '18 at 17:39

score 0 · Answer 2 · answered Jan 05 '19 at 16:25

Only just stumbled across this, but just for future reference...

Firstly, I'll work on the assumption that your example was supposed to read $array = array(":a"=>"10002345", ":b"=>"Josh");, as there would be an issue even if your :b key was absent.

In this bit:

foreach($array as $key => $value ) {
    $stmt->bindParam($key, $value);
}

You haven't 'passed by reference'. The $value should be amended to &$value

foreach($array as $key => &$value ) {
    $stmt->bindParam($key, $value);
}

This is because the bindParam method signature requires the value to be a variable reference:

public function bindParam ($parameter, &$variable, $data_type = PDO::PARAM_STR, $length = null, $driver_options = null) {}

(note the & before $variable).

The end result of your original query (sans &) is that all :params would be set to the value that is in the last iteration of $value in your original loop.

So, the result of

$sql = "SELECT * FROM users WHERE id = :a OR fname = :b";

$array = array(":a"=>"10002345", ":b"=>"Josh");

$stmt = $conn->prepare($sql); 

foreach($array as $key => $value ) {
    $stmt->bindParam($key, $value);
}

$stmt->execute();

Would be SELECT * FROM users WHERE id = 'Josh' OR fname = 'Josh'

Using named parameters (:param) has advantages over positional params (?), so it's worth reserving that option for prepared statements, as opposed to the accepted answer of "it's better to use ? placeholders", which is not the case.

Should be the accepter answer, but beware of the number of named parameters. Using dynamic trough a loop don't work very well with static number of named parameters. I mean that sometimes, array of params/value, could count more or less values than the named parameters in the query.. — mik3fly-4steri5k, Dec 13 '19 at 21:18

score -1 · Answer 3 · answered Mar 14 '20 at 18:14

In my database abstraction layer I use the following utility functions:

/**
 * getFieldList return the list with or without PK column
 * @param bool $withID - true when including parameter
 */
static protected function getFieldList( $withID = false )
{
    if( $withID )
        $result = '`' . static::getTableName( ) . '`' .
            '.`' . static::getPrimaryKeyName( ) . '`, ';
    else
        $result = '';

    return $result .= '`' . static::getTableName( ) . '`.' . 
        '`' . implode( '`, `'.static::getTableName( ) . '`.`', static::getFieldNames( ) ) . '`';
}

/**
 * getFieldPlaceholders - 
 * @return string - all PDO place holders prefixed :
 */
static protected function getFieldPlacholders( )
{
    return ':' . implode( ',:', static::getFieldNames( ) );
}

/**
 * getUpdateList - SQL updates section
 * @return string
 */
static private function getUpdateList( )
{
    $result = array( );
    foreach( static::getFieldNames( ) as $field ) {
        if( $field === static::getPrimaryKeyName() ) continue;
        $result[] = '`' . $field . '`=:' . $field;
    }
    return implode( ',', $result );
}

/**
 * Bind the fields to PDO placeholdes
 * @param PDOStatement $stmt statement that the fields are bound to
 * @return void
 */
protected function bindFields( $stmt )
{
    foreach( array_keys($this->fields) as $field ) {
        if( $field === static::getPrimaryKeyName() ) continue;
        $stmt->bindParam( ':' . $field, $this->fields[$field] );

        // echo $field . '->' . $this->fields[$field] . '<br>';
    }
}
/**
 * Bind the fields to the placeholders
 * @param PDOStatement $stmt - that the fields are bind to
 * @return void
 */
protected function bindColumns( $stmt, $withID = false )
{
    if( $withID )
        $stmt->bindColumn( static::getPrimaryKeyName(), $this->ID );
    foreach( static::getFieldNames() as $fieldname )
    {
        $stmt->bindColumn( $fieldname, $this->fields[$fieldname] );
    }   
}

/**
 * parseResultset
 * Set the values of the select results, resets dirty (object is in sync)
 * @param mixed[] $result - associative array
 */
protected function parseResultset( $result )
{
    foreach( $result as $field=> $value ) {
        if( $field === static::getPrimaryKeyName() )
            $this->ID = $value;
        $this->fields[$field] = $value;
    }
    $this->dirty = array();
}

score -1 · Answer 4 · answered Mar 27 '23 at 12:44

-1

For anyone still having issues with the above, you can use:

$stmt= $conn->prepare($sql);
foreach($data as $key => &$value) {
    $stmt->bindParam(':'.$key.'', $value);   
}  
$stmt->execute();

answered Mar 27 '23 at 12:44

mjosh

132
1
7

HOW TO LOOP PHP'S PDO BIND PARAM

4 Answers4