Why doesn't this work? PDO Prepared Statements

[Mark W]

I have an array like this:

 

$tran = array(
	array('libAddSetting', array('key' => 'foo', 'value' => 'bar') ),
	array('libUpdateSetting', array('value' => 'moo', 'key' => 'foo') ),
);

 

The first value in each array is an identifier for a query (since it's going to be specific per driver) and the second is an array containing the appropriate binds.

 

Here are the queries for those from the MySQL driver:

	// Add setting
	public function libAddSetting(){
		return "INSERT INTO `settings` (`key`, `value`) VALUES (:key, :value)";
	}
	// Update setting
	public function libUpdateSetting(){
		return "UPDATE `settings` SET `value` = :value WHERE `key` = :key";
	}

 

$tran is going to be passed to a function which prepares the queries and executes them - you can probably work out the idea here is to insert a record into the 'settings' table with the key 'foo' and the value 'bar' and then update that record so the value is 'moo'.

 

Here is the function that will handle that:

	public function transaction($queries=array())
	{
		try
		{
			$this->pdo->beginTransaction();
			foreach ($queries as $q)
			{
				$stmt = $this->pdo->prepare($this->$q[0]());
				foreach ($q[1] as $key => $value)
				{
					$stmt->bindParam(":{$key}", $value); 
				}
				$stmt->execute();
			}
			$this->pdo->commit();
		}
		catch (PDOException $e)
		{
			$this->pdo->rollback();
			throw new Exception($this->returnError($e), 1);
		}
	}

 

But it doesn't work, it inserts a record with the key and value both 'bar'. I can't for the life of me work out why. If I use this, it works fine:

 

	public function transaction($queries=array())
	{
		try
		{
			$this->pdo->beginTransaction();
			foreach ($queries as $q)
			{
				$stmt = $this->pdo->prepare($this->$q[0]());
				$stmt->bindParam(':key', $q[1]['key']);
				$stmt->bindParam(':value', $q[1]['value']);
				// foreach ($q[1] as $key => $value)
				// {
				// 	$stmt->bindParam(":{$key}", $value); 
				// }
				$stmt->execute();
			}
			$this->pdo->commit();
		}
		catch (PDOException $e)
		{
			$this->pdo->rollback();
			throw new Exception($this->returnError($e), 1);
		}
	}

 

Any ideas?

[Daniel0]

Can't you just do $stmt->execute($q[1]);?

[Mark W]

Well the point is the above should work, but I don't know why it doesn't.

[Daniel0]

Try to print $key and $value inside the foreach to check that it's getting done correctly.

[Mark W]

Yeah, it returns the correct values