Binding Params for Pdo Statement Inside a Loop

Binding params for PDO statement inside a loop doesn't work

If you apply this from the PDO manual

PDOStatement::bindParam

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

You will understand that in your loop you are using the same variable X times. Overwriting it each time round the loop.

So when the actual binding is done, at the time you ->execute() your prepared query, you only have one value, the last from your loop in those variables

Binding params for PDO statement inside a loop

The problem is that bindParam requires a reference. It binds the variable to the statement, not the value. Since the variable in a foreach loop is unset at the end of each iteration, you can't use the code in the question.

You can do the following, using a reference in the foreach:

foreach ($reindex as $key => &$value) {  //pass $value as a reference to the array item
    $stmt->bindParam($key, $value);  // bind the variable to the statement
}

Or you could do this, using bindValue:

foreach ($reindex as $key => $value) {
    $stmt->bindValue($key, $value);  // bind the value to the statement
}

HOW TO LOOP PHP'S PDO BIND PARAM

It is better to use ? placeholders in a query and pass array of data to execute:

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

PDO bindParam() PHP Foreach Loop

You should use bindParam like this:

$sql = 'SELECT * FROM Organization WHERE City = :City AND State= :State';
$wherestr = array('string1', 'string2');
$stmt = $db->prepare($sql);
$stmt->bindParam(':City', $wherestr[0]);
$stmt->bindParam(':State', $wherestr[1]);
$stmt->execute();

if you insist using foreach, here is a way:

$stmt = $db->prepare($sql);
$params = array(':City' => 'string1', ':State' => 'string2');
foreach ($params as $key => &$val) {
    $stmt->bindParam($key, $val);
}
$stmt->execute();

note that we use pass by reference in the loop, which is a must.

PDO bindParam not working in loop

Trying to bindParam to an array element like $array['key'] causes a few issues because its bound as reference, but its not. Its, just not done that way.

So three ways:

$stmt = $dbh->prepare($sql);
// bind to variables that can be a reference
$stmt->bindParam(":GROUP_ID", $id, PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_1", $pt1, PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_2", $pt2, PDO::PARAM_INT);
foreach ($installed_groups as $installed_group){
        $installed_version_parts = explode('.', $installed_group['version']);
        // assign the referenced vars their new value before execute
        $id = $installed_group['group_id'];
        $pt1 = $installed_version_parts[1];
        $pt2 = $installed_version_parts[2];
        $stmt->execute();
}

Or: (less efficient)

$stmt = $dbh->prepare($sql);
foreach ($installed_groups as $installed_group){
        $installed_version_parts = explode('.', $installed_group['version']);

        // use bindValue (not bindParam) INSIDE the loop
        // bindValue doesn't set them by reference, so any value expression works
        $stmt->bindValue(":GROUP_ID", $installed_group['group_id'], PDO::PARAM_INT);
        $stmt->bindValue(":INSTALLED_VERSION_NUM_1", $installed_version_parts[1], PDO::PARAM_INT);
        $stmt->bindValue(":INSTALLED_VERSION_NUM_2", $installed_version_parts[2], PDO::PARAM_INT);
        $stmt->execute();
}

Or:

$stmt = $dbh->prepare($sql);
foreach ($installed_groups as $installed_group){
        $installed_version_parts = explode('.', $installed_group['version']);

        // pass them on execute directly
        $stmt->execute(array(':GROUP_ID'=>$installed_group['group_id'],
                             ':INSTALLED_VERSION_NUM_1'=>$installed_version_parts[1],
                             ':INSTALLED_VERSION_NUM_2'=>$installed_version_parts[2]));
}

Loop bindParam from key value post array in pdo prepared statements

Yes, it's safe. If you're using parameterized queries, you won't be vulnerable to injection attacks.

That being said, it seems that you're reinventing the wheel here, which is most often not the right way to do things. However; that's outside the scope of your question.

Also please see this very similar question where the accepted answer has this to say:

Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

PHP bind in for loop

Had to change

$locn= "'" . $location . "%'"; 

TO

$locn= $location . "%";

I realized that with binding, there is no need for single quote.

Safe Way To Loop PDO Statement

Use numbered parameters instead of named parameters, and build the query and parameters dynamically.

$sql = "INSERT INTO inbox (efrom,subject,msg,eread,date) VALUES ";
// array_fill will create an array of N "(?, ?, ?, ?, ?)" strings
// implode will then join them together with comma separators
$sql .= implode(', ', array_fill(0, count($inbox_from), "(?, ?, ?, ?, ?)"));
$STH = $DBH->prepare($sql);
$params = array();
// Populate the $params array with all the input values
foreach ($inbox_from as $i => $from) {
    $params[] = $from;
    $params[] = $inbox_subject[$i];
    $params[] = $inbox_msg[$i];
    $params[] = $inbox_read[$i];
    $params[] = $inbox_date[$i];
}
$STH->execute($params);

You can leave the id field out of the column list, and it will be filled in automatically using auto-increment.

To remove duplicate messages, you can do:

$check_stmt = $DBH->prepare("SELECT COUNT(*) AS count FROM inbox WHERE msg = :msg");
$check_stmt->bindParam(':msg', $msg);
$messages_seen = array();
foreach ($inbox_msg as $i => $msg) {
    // Check if the message is already in the DB
    $check_stmt->execute();
    $first_row = $check_stmt->fetch(PDO::FETCH_OBJ);
    $check_stmt->fetchAll(); // Fetch the rest of the query to get in sync
    if ($first_row->count > 0) {
        $messages_seen[$msg] = true; // Remember that we already saw this message
    } elseif (!isset($messages_seen[$msg])) // If we haven't already seen this message
        $params[] = $inbox_from[$i];
        $params[] = $inbox_subject[$i];
        $params[] = $msg;
        $params[] = $inbox_read[$i];
        $params[] = $inbox_date[$i];
        $messages_seen[$msg] = true; // Remember that we added this message
    }
}
$sql = "INSERT INTO inbox (efrom,subject,msg,eread,date) VALUES ";
// There's 1 (...) group for every 5 parameters, so divide the length of $params by 5 to know how many of them to put in the SQL
$sql .= implode(', ', array_fill(0, count($params)/5, "(?, ?, ?, ?, ?)"));
$STH = $DBH->prepare($sql);
$STH->execute($params);

When adding an index on a TEXT datatype, you have to specify the number of bytes of the text to store in the index. So it should be something like:

CREATE INDEX ix_msg ON inbox (msg(200));

PDO BindParam won't bind on while loop every time

You are preparing each incomplete query fragment inside the loop. You should only prepare once and you should prepare the complete SQL query.

Update: Not sure where you looked for documentation, but the bindParam() manual page has several examples:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

You won't get anything useful if you do this inside the loop:

$stmt->bindParam( ':s'.$x, $currTerm );  

... but you remove and create a new $stmt object right before.

---

Related Topics