PHP Pdo Bindparam Was Falling in a Foreach

PHP PDO bindParam was falling in a foreach

However, thanks to this guys. I found out that you need to pass the value by reference with a & before like that :

foreach($Fields as $Name => &$Value){
    $Query->bindParam(':'.$Name, $Value, PDO::PARAM_STR);
}

This was driving me nuts.

Actual quote from PHP.net :

Vili 28-May-2010 12:01

This works ($val by reference):

<?php
foreach ($params as $key => &$val){
    $sth->bindParam($key, $val);
}
?>

This will fail ($val by value, because bindParam needs &$variable):

<?php
foreach ($params as $key => $val) {
    $sth->bindParam($key, $val);
}
?>

PHP PDO bindParam not working with array content

Let's see what your code does:

$query->bindParam(':product_id', $row['product_id']);

Here you are implicitly creating an array $row with one element and pass this element as reference to bindParam(). This results in a structure as follows:

$row : Array(
           'product_id' => &null
       )

Note that $row itself is not a reference! As soon as you reach the loop:

foreach ($rows as $row)

the Array $row gets overwritten. At this point you are losing the reference in your old $row-Array, the array does not exist anymore. A reference cannot automagically hop over into a different array, just because it has the same key and happens to be assigned to the same variable.

Long story short: what you are trying to do is not possible.

PDO and MySQL UPDATE in Foreach Loop

For starters you're using bindParam() like it's bindValue(), they're quite different.

Without seeing where you're getting your array values from it's a little harder to see what's going on with certainty. It looks like the information you're providing is likely not actually the code you're using and has been modified, particularly regarding the foreach loops and the data_array variables as what you're describing is an issue common with BindParam so that's the assumption I'll be working on. If that is the case, it's in general a good idea to provide actual code snippets including the initialization of the variables used and the blocks where the issue is found rather than just the code in those blocks.

Here's another answer with why, basically make sure your passing by reference the value portion of your foreach loop or your changing the bindParams to bindValues. You'll also want to make sure that you're using two separate objects here instead of one if you plan to continue using this structure since you're running both bindParam() methods each time you call execute().

So something like, say, were the code structure not changed (which it should probably be since this is all in loops and only Execute should be in a loop):

$set_data1 = "UPDATE data_table
          SET data_status = 'PROCESSED' 
          WHERE data_id = :data_id1";

$stmt = $db->prepare($set_data1);

$stmt->bindValue(':data_id1', $data_array1['data_id'], PDO::PARAM_INT);

$stmt->execute();

$set_data2 = "UPDATE data_table
              SET data_status = 'PENDING'
              WHERE data_id = :data_id2";

$stmt2 = $db->prepare($set_data2);

$stmt2->bindValue(':data_id2', $data_array2['data_id'], PDO::PARAM_INT);

$stmt2->execute();

A more optimal way to do this though would be something like (keep in mind this is just a general example):

$set_data = "UPDATE data_table
          SET data_status = :data_status 
          WHERE data_id = :data_id";

$data_array = array( array('data_status' => $dataStatus1, 'data_id' => $dataId), array('data_status' => $dataStatus2, 'data_id' => $dataId2) ); 
/* this is just to represent a multidimensional array (or a multidimensional object) containing the data status and the data id which should be handled and decided before you pass them into a loop. */

$stmt = $db->prepare($set_data);

$data_status = null;
$data_id = null;

$stmt->bindParam(':data_status', $data_status);
$stmt->bindParam(':data_id', $data_id);

foreach( $data_array as $name => $val ) {
    $data_status = $val['data_status'];
    $data_id = $val['data_id'];
    $stmt->execute()';
}

PDO Foreach fetch loop returns first row with specific id and rest as false

Okay, I figured it out! All I had to do is to loop users messages array once they were fetched and then push each of those separately into another array that got returned in the end.

while($row = $getMessage->fetchObject()){
       array_push($messages, $row);   
} 

Here is the complete function.

public function listMessages($group_id){ 
        $db = initDb(); 
        $getGroupUsers = $db->prepare("SELECT user_id FROM in_group WHERE group_id = :group_id");
        $getGroupUsers->bindParam(":group_id", $group_id, PDO::PARAM_INT);
        $getGroupUsers->execute();
        $users = $getGroupUsers->fetchAll(PDO::FETCH_ASSOC);
        $messages = array();
        foreach ($users as $item) {
            $getMessage = $db->prepare("SELECT * FROM task_messages WHERE user_id = :user_id ORDER BY visited ASC");
            $getMessage->bindParam(":user_id", $item['user_id'], PDO::PARAM_INT);
            $getMessage->execute();                
            if($getMessage->rowCount() > 0){                                        
                while($row = $getMessage->fetchObject()){
                    array_push($messages, $row);   
                }                                 
            }

        }
        return json_encode($messages); 
}

Now returned array looks like this:

Parsing HTML tags to PDO MySQL

The way you are passing the data to the prepared statement is incorrect, you using the index to the array - which is a 0 based numerically indexed array. This is why you get the error. Although not sure why you need this array...

$rows = array($domain, $url);

Instead I would suggest using...

$stmt = $conn->prepare("INSERT INTO linkss(id, domain, url) 
                          VALUES (NULL, :domain, :url)");
foreach($url as $value){
     $stmt->bindParam(':domain', $domain);
     $stmt->bindParam(':url', $value);
     $stmt -> execute();  
}

This should also insert a record for each URL rather than the last one as the execute() is inside the loop.

Update:

You also need to amend the code which builds the list of URL's, this was previously overwriting the last URL all the time, this will create a list of all of the URLs...

$url = array();
foreach($matches as $match) {
    $url[]=$match[2];
}

mySQL bind_param with IN(?)

This is one place placeholders fall on their faces. Minus the auto-escaping, they're almost literally just a string replacement operation internally, meaning that if you have WHERE Locations IN (?), and pass in 1,2,3,4, you'll get the equivalent of

WHERE Locations IN ('1,2,3,4')  // note, it's a string, not individual comma-separated integers

logically equivalent to

WHERE Locations = '1,2,3,4' // again, just a string

instead of the intended

WHERE Locations = 1 OR Locations = 2 OR Locations = 3 OR Locations = 4

The only practical solution is to build your own list of comma-separated placeholders (?), e.g:

$placeholders = implode(',', array_fill(0, count($values), '?'));
$sql = "SELECT Name FROM Users WHERE Locations IN ($placeholders)";

and then bind your parameters are usual.

Do PHP PDO prepared statements need to be escaped?

PDO does not escape the variables. The variables and the SQL command are transferred independently over the MySQL connection. And the SQL tokenizer (parser) never looks at the values. Values are just copied verbatim into the database storage without the possibility of ever causing any harm. That's why there is no need to marshall the data with prepared statements.

Note that this is mostly a speed advantage. With mysql_real_escape_string() you first marshall your variables in PHP, then send an inefficient SQL command to the server, which has to costly segregate the actual SQL command from the values again. That's why it's often said that the security advantage is only implicit, not the primary reason for using PDO.

If you concat the SQL command and don't actually use prepared statments (not good!), then yes, there still is an escape function for PDO: $pdo->quote($string)

PDO INSERT not working with $_POST Variables

Empty your database table and see if it works... Likely you have a primary key in your db table that you are trying to overwrite.

Check your db table as well, using EXPLAIN inventory

PHP PDO insert operation failing

You need to set this

$pdoConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdoConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

You will now get the error because you have set the PDO error mode to exception.

function insertInclusao($usuarioId, $tipoFaturaId, $parametrosValidadores, $identificadorExtra, $optin) {
$pdoConnection = new PDO("mysql:host=host;dbname=dbname", "user", "pass");
$pdoConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdoConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
try {
    $stmte = $pdoConnection->prepare("INSERT INTO SF_INCLUSAO (UsuarioId, TipoFaturaId, ParametrosValidadores, DataInclusao, IdentificadorExtra, Optin) VALUES (?, ?, ?, NOW(), ?, ?)");
    $stmte->bindParam(1, $usuarioId , PDO::PARAM_INT);
    $stmte->bindParam(2, $tipoFaturaId , PDO::PARAM_INT);
    $stmte->bindParam(3, $parametrosValidadores , PDO::PARAM_STR);
    $stmte->bindParam(4, $identificadorExtra , PDO::PARAM_STR);
    $stmte->bindParam(5, $optin , PDO::PARAM_INT);
    $executarInclusao = $stmte->execute();
    if($executarInclusao) {
        return $pdoConnection->lastInsertId();
    } else {
        return 0;
    }
} catch (PDOException $e) {
   return $e->getMessage();
}
}

---

Related Topics