CODEWITHSUNDEEP
phpmysqlpdobindparam
EE-SHI-RO
EE-SHI-RO

Reputation: 61

HOW TO LOOP PHP'S PDO BIND PARAM

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.

Upvotes: 0

Views: 3467

Answers (4)

u_mulder
u_mulder

Reputation: 54796

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);

Upvotes: 6

mjosh
mjosh

Reputation: 142

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();

Upvotes: -1

theking2
theking2

Reputation: 2852

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();
}

Upvotes: -1

Chris J
Chris J

Reputation: 1447

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.

Upvotes: 0

Related Questions