Home » Database » MDB2 » Bug #6499

[2006-01-15 23:25 UTC] kuu at ivmasters dot ru

Description:
------------
Example:

$stmt = $mdb2->prepare( 'select * from :table where name=:name', array('text', 'text' ) );

// Execute constructs wrong statement, skipping :name param
// because second request parameter name differs from second
// value key.
$stmt->execute( array( 'table'=>'users', 'foo'=>'bar', 'name'=>'victor' ) );

Test script:
---------------
Solution, in MDB2.php _execute():

function &_execute($result_class = true, $result_wrap_class = false)
{
$query = '';
$last_position = 0;

// We should cycle by found request parameter names, not by // value names like in old version. Request parameter names // should exists in value array.
foreach ($this->statement as $parameter => $position) {

// If current value does'nt exists in request parameters we // should skip it's replacement
if ( !isset($this->statement[$parameter])) continue;
$value = $this->values[$parameter];
...

Note that request parameter array shoul have same key order than the values array.

[2006-01-16 13:55 UTC] kuu at ivmasters dot ru

I agree with you, my code might be used only as temporary solution for mysql driver.

But, I think, named placeholders value order should'nt be important for replacing, othewise for what they names are used for?

I think, this is the right solution:

Case 1)
$stmt = $mdb2->prepare( 'select * from table where x=? and y=? and z=?', array( 'integer', 'integer', 'integer' ) );
$stmt->execute( array( 1, 2, 3 ) );

SQL: select * from table where x=1 and y=2 and z=3

Case 2)
$stmt = $mdb2->prepare( 'select * from table where x=:x and y=:y and z=:z', array( 'integer', 'integer', 'integer' ) );
$stmt->execute( array( "z"=>1, "y"=>2, "foo"=>"bar", "x"=>3, "dumb"=>"none" ) );

SQL: select * from table where x=1 and y=2 and z=3

Formats the right query in spite of wrong value order and extra named values.

Case 3)
$stmt = $mdb2->prepare( 'select * from table where x=? and y=:y and z=?', array( 'integer', 'integer', 'integer' ) );
$stmt->execute( array( 1, "y"=>2, "foo"=>"bar", 3, "dumb"=>"none" ) );

SQL: select * from table where x=1 and y=2 and z=3

Named values order and extra named values does'nt matter. Unnamed placeholders are replacing with unnamed values in the order they are in values array.

I think, these solution would be useful, because it is not always possible to reorder form values, for example.

Am I right?