My database query keeps setting all columns to the same value in MySQL

Question

I can't figure this one out; it's probably really obvious to everyone's eyes except mine.

Here is my update method for Kohana 3.

public function update($type, $id, $updates) {
        $info = $this->getInfo($type);
        $dbTable = $info['table'];

        $updatesKeysToValues = array();

        var_dump($updates);

        foreach($updates as $key => $value) {
            // if the value is null or the key isnt set for this, don't update!
            if ($value === null OR ! isset($info['columnsToUpdateData'][$key])) continue;
            $updatesKeyToValues[] = "`$key` = :$key";

        }

        $updatesKeyToValues = implode(', ', $updatesKeyToValues);

        $query = 'UPDATE `' . $dbTable . '` SET ' . $updatesKeyToValues . ' WHERE id = :id LIMIT 1' ; 

        echo $query;

        $dbQuery = DB::query(Database::UPDATE, $query);

        foreach($updates as $key => $value) {
                echo "$key === $value\n<br>";
                $dbQuery->bind(':' . $key, $value);
         }

        $success = $dbQuery->bind(':id', $id)    
                        ->execute();    

        var_dump($success);

    }

During every var_dump() and echo the data is fine. There is nothing to suggest why this is happening.

Essentially what I am doing is getting the data for this table from the config, building a query string with named params, looping and defining the named params and then executing. Instead of working, I end up with all fields the same (seems to be whatever was the last array value).

I can't seem to figure it out, can you? Many thanks for your time.

UPDATE

I just had a thought, are underscores valid in param names in a query?

ANOTHER UPDATE

Here is the output of the echo $query

UPDATE `personnel` SET `first_name` = :first_name, `last_name` = :last_name, `email` = :email WHERE id = :id LIMIT 1

I also cooked up that method of binding multiple params to the query too. I've never done it in a loop before, but I assumed it would work. In Kohana 2.x, I'd always used $bindings[] = '[email protected]' etc, but the new Kohana doesn't accept an array as far as I can tell.

FINAL UPDATE

Thanks everyone, I think it is being passed by reference. I got around it by setting it to $updates[$key]

Looks like I could of also used the param() method instead of bind. View source

Answer 1

the bind function is using a reference the your $value

public function bind($param, & $var)
{
 // Bind a value to a variable
    $this->_parameters[$param] =& $var;
    return $this;
}

Something that seems to work in a test

$a = array("a"=>1, "b"=>2, "c"=>3, "d"=>4, "e"=>5, "f"=>6);
$v = array();
$t = array();
$i = 0;
foreach($a as $key => $value)
{
    $t[] = $key;
    $v[] = &$t[$i];
    $i++;
}

print_r($v);

results are here: http://www.antiyes.com/test/hmm.php

do you think the $key & $value in

$dbQuery->bind(':' . $key, $value);

are being passed by reference ?

below didnt work

---

this line

$updatesKeyToValues[] = "`$key` = :$key";

could you change it to:

$updatesKeyToValues[] = "`" . $key ."` = " . ":" . $key;

and see what happens?

Answer 2

so are you saying you call this function once.. and all the rows in 'personnel' get updated?

or that first_name last_name and email are all set to the email value?

Answer 3

I don't know what data access layer you're using here, but I'm guessing this:

foreach($updates as $key => $value) {
    $dbQuery->bind(':' . $key, $value);
}

could be doing something really deceptive: taking the parameters by reference.

So what would happen would be that since $value is a real variable, the bind() function receives a reference to it and remembers that it's this variable — not the variable's current value — that it will bind to the given parameter. Then you go the next time round the foreach loop, and we've got the classic loop problem of C-like languages: you're not getting a new instance of $key and $value, you're actually changing the existing variables you already had, just like for the standard for ($i= 0... loop.

So, when it comes time to do the query, what's parameter :a? It's the current value of $value, the last one in the loop. What's parameter :b? The same. And so on.

I know some PHP parameterisation interfaces do this (mysqli, I think?), but in general receiving parameters by reference is IMO extremely likely to lead to unwanted behaviours like this, and I certainly consider it totally inappropriate for a parameter-binding interface like this.

ETA: just looked at query.php in the link you posted to John's comment. Yes. Sigh. It's taking parameters by reference. How awful.

Answer 4

Why don't you use the query-builder?

This is just a quick guess, as I haven't had enough time to play with the query-builder myself.

$query = DB::update();
$query->set($updates);
// etc

Check out the source and I'm sure you could figure out how the query-builder works :)