PDO MySQL php foreach loop

Question

I UPDATE a single mysql record using a foreach loop which obtains the name and value of $_POST variable and UPDATES the record column at the time, the names of the $_POST variables are the same as the mysql column names

Here is the code

foreach ($_POST as $key => $value)  {  
    $value = mysqli_real_escape_string($con, $value );  
    $value = strip_tags($value);  
    $sql="UPDATE properties SET $key = '$value' WHERE propertyID='$propertyID'";
    $query = mysqli_query($con, $sql);
    if (mysqli_errno($con)){$error=1;}
}//end foreach loop
unset($value);
unset($key);

This works fine

However I'm trying to convert the loop to use PDO. I have tried looking at previous posts on this subject but am still unable to make it work

Here is the code I have tried:

foreach ($_POST as $key => $value) {  
    $value = mysqli_real_escape_string($con, $value );  
    $value = strip_tags($value);  
    $sql="UPDATE vendors SET $key = '$value' WHERE vendorID='$vendorID'";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue($key, $value); //have also tried bindParam!!
    $stmt->execute();
}//end foreach loop
unset($value);
unset($key);

This runs but doesn't update any of the columns, can anybody help please?

Thanx

Bob

Is this for real, you update the same table ONE COLUMN AT A TIME for MULTIPLE COLUMNS. `You cannot be serious` As you are revisiting this mess, ***try to refactor your basic concept at the same time*** — RiggsFolly, Aug 06 '15 at 12:12
possible duplicate of [PHP: While loop not working after adjusting SELECT for SQL injection prevention](http://stackoverflow.com/questions/30979758/php-while-loop-not-working-after-adjusting-select-for-sql-injection-prevention) — Jay Blanchard, Aug 06 '15 at 12:17
Hey Bob. Its a bit impolite to _eat and run_ Did anybodies answer help? If they got close then ask for more information. If an answer answered your question then accept that answer. It stops people continuing to provide answers and lets others know what solved your problem when they search for a similiar issue. — RiggsFolly, Aug 07 '15 at 15:05

score 0 · Answer 1 · answered Aug 06 '15 at 12:18

0

You are not binding value properly, Do it like this:

$sql="UPDATE vendors SET $key = :value WHERE vendorID='$vendorID'";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':value', $value); //have also tried bindParam!!

answered Aug 06 '15 at 12:18

Crunch Much

1,537
1
11
14

score 0 · Answer 2 · edited May 23 '17 at 11:44

That's how I'll do it :

foreach ($_POST as $key => $value) {  
    $query = 'UPDATE vendors SET ' . $key .' = :key WHERE vendorID=:vendorID';
    $statement = $pdo->prepare($query);
    $statement->execute(['key' => $key, 'vendorID' => $vendorID]);
}

But that's not optimized, you're updating column one by one..

check out this thread, It explains how to implode $key and $value from an array, use this and put it in your SET statement.

How to implode array with key and value without foreach in PHP

RiggsFolly · Answer 3 · 2015-08-06T12:45:14.287

How about this as a better idea, you process the $_POST bit dangerous that and build a single query that updates all the provided columns in one query. It could reduce the load on your database server 1000 fold.

$columns = '';
foreach ( $_POST as $field_name=> $value ) {
    $columns .= "$field_name = ?,";
}
$columns = rtrim($columns, ',');  // loose the trailing comma

$sql="UPDATE vendors SET $columns WHERE vendorID=?";

$stmt = $stmt = $pdo->prepare($sql);
if ( ! $stmt ) {
    print_r( $pdo->errorInfo() );
    exit;
}

// add the parameter using the bindValue
$col = 1;
foreach ( $_POST as $idx => $value ) {
    $stmt->bindValue($col, $value);
    $col++;
}
$stmt->bindValue($col, $vendorID); // finally bind the vendorID

$res = $stmt->execute();
if ( ! $res ) {
    print_r( $stmt->errorInfo() );
    exit;
}

I would love to know if this actually works as I have not tested it. If it does you will probably be able to hear your database server audibly breath a sign of relief.

score 0 · Answer 4 · answered Aug 06 '15 at 13:18

Problem is that you are binding value but you don't send any parameter query just remove that line:

$stmt->bindValue($key, $value);

And then the code will be:

foreach ($_POST as $key => $value) {  
$value = mysqli_real_escape_string($con, $value );  
$value = strip_tags($value);  
$sql="UPDATE vendors SET $key = '$value' WHERE vendorID='$vendorID'";
$stmt = $pdo->prepare($sql);
$stmt->execute(); 
}  //end foreach loop
unset($value);
unset($key);

One another option to put parameter in the query which is more secured but it needs more work and expertise.

PDO MySQL php foreach loop

4 Answers4