CODEWITHSUNDEEP
phpmysqlsqldatabasepdo
Gideon
Gideon

Reputation: 1886

Solving inequality issue between bound variables and tokens, when number of bound variables is dynamic. (PDO/PHP MySQL)

Context:

I am attempting to update a record with a set of values in a MySQL database using the usual UPDATE syntax, via PDO method of binding tokens to data.

The Problem:

The number of fields to be updated is dynamic, and is adjusted depending on what data has been POSTed to the page. Using an if(isset($_POST[])) to filter out updates to fields which may already have data in, but I don't want to be overwritten by empty data.

This means that the number of tokens I am creating varies, but my syntax for binding data to the tokens is static, and I'm not sure how to adjust it.

This of course results in: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

My Code:

Please note that $data is an array of all the column names in my database. It is also the set of name attributes of the variables that may be posted, such that $_POST[x] should be updated to the column x in a given record, and x is a member of $data.

$sql_b =    "UPDATE `temp_data` SET ";  
            foreach($data as $value)
            {
            if(isset($_POST[$value]))
            $sql_b .="$value = :$value, ";
            }

            $sql_b = rtrim($sql_b,', ');    
            $sql_b .=" WHERE UID = '$uid'";

try     
{
$pdo = new PDO('mysql:host=localhost; dbname=db01', $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   
$stmt = $pdo->prepare($sql_b);

foreach($data as $value)
{
$stmt->bindParam(":$value", $_POST[$value]);
}

$stmt->bindParam(":sv_215_hidden", $_POST[sv_215_hidden]);
$stmt->bindParam(":sv_216_hidden", $_POST[sv_216_hidden]);

$stmt->execute();

# Affected Rows? echo $stmt->rowCount(); // 1 } catch(PDOException $e) { echo 'Error: ' . $e->getMessage(); }

To highlight the problem areas of this:

This code:

foreach($data as $value)
            {
            if(isset($_POST[$value]))
            $sql_b .="$value = :$value, ";
            }

Creates a subset of possible UPDATE requests

Where as this code:

foreach($data as $value)
{
$stmt->bindParam(":$value", $_POST[$value]);
}

Always creates a full set of bindings between all possible tokens.

Central Question:

How can I adjust the number of bindings to match only the fields being updated?

Upvotes: 1

Views: 164

Answers (2)

Chris Carson
Chris Carson

Reputation: 1845

I think you need to do a couple of extra steps:

$keys = array('foo', 'bar', 'baz', 'foobar');
$updates = array();
$bound = array();
foreach($keys as $key){
    if (isset($_POST[$key])){
        $updates[] = $key . '= :' . $key;
        $bound[':' . $key] = $_POST[$key];
    }
}
$sql = 'UPDATE table SET ' . implode(',', $updates) . ' WHERE etc'; //add your where clause
$stmt = $pdo->prepare($sql);
$stmt->execute($bound);

In other words, you can pass an array of bound parameters to PDOStatement::execute rather than binding them individually beforehand; and doing things as above makes sure that the bound parameters match what's in your SQL.

Upvotes: 0

andrewsi
andrewsi

Reputation: 10732

You're already there; you're checking to see what fields are set when you generate the SQL statement:

foreach($data as $value)
        {
        if(isset($_POST[$value]))
        $sql_b .="$value = :$value, ";
        }

So you can use the exact same logic when you're calling bindParam():

foreach($data as $value)
        {
        if(isset($_POST[$value]))
        $stmt->bindParam(":$value", $_POST[$value]);
        }

Upvotes: 1

Related Questions