Dynamic Prepared Statements

[linuxlover101]

Hello all,

 

I could use some assistance with some mysqli prepared statements.

 

I would like to make them based on parameters passed in a function.

 

Currently I have a database class that I'd like to have a function for

INSERT mysqli queries.

The code I have is below.

I would like to be able to call insertInto() in a page by just inserting the

parameters of the table name,

an array with the keys as the column names and the values as the inserted

values, and then the parameter types

for the prepared statement.

 

Ideally the $array would be $_POST[] from a form.

 

This would allow much flexibility in my project.

 

I think the main problem is having a dynamic number of possible columns to

insert into.

$this->stmt->bind_param() takes a "fixed" amount of args.

 

At first, I though I could use call_user_func_array, but I get a warning and

a fatal error:

 

Warning*: call_user_func_array()

[function.call-user-func-array<http://localhost/mbpa/function.call-user-func-array>]:

First argument is expected to be a valid callback, 'Array' was given in *

C:\xampp\htdocs\mbpa\classes\db.php* on line *45*

 

*Fatal error*: Call to a member function execute() on a non-object in *

C:\xampp\htdocs\mbpa\classes\db.php* on line *47*

 

What is causing this fatal error? I thought I could access an object method.

class Database {
function insertInto($table, $array, $param_types) {

	$set_string = $this->joinKeys($array);

	$query = "INSERT INTO ".$table." SET ".$set_string;

	$func_params = array_unshift($array, $param_types);

	$stmt = $this->mysqli->prepare($query);
	call_user_func_array(array($this->stmt, 'bind_param'), $func_params);

	$stmt->execute();
	$stmt->close();
}

    function joinKeys($array) {

            foreach($array as $key => $value) {
                            $output[] = $key." = ?";
            }

            $set = implode(", ", array_values($output));
            return $set;
    }
}
}

$db = new Database();

$array['username'] = '[email protected]';
$array['password'] = md5('something');
$array['last_name'] = 'Smith';
$array['first_name'] = 'John';

$db->insertInto('users', $array, 'ssss');

[dzelenika]

first time you are using $stmt as local function variable:

 

$stmt = $this->mysqli->prepare($query);

 

and second time as object variable:

 

call_user_func_array(array($this->stmt, 'bind_param'), $func_params);

[linuxlover101]

Even if both are local variables, it doesn't seem to work.

[dzelenika]

Even if both are local variables, it doesn't seem to work.

 

same or other error?

[linuxlover101]

Even if both are local variables, it doesn't seem to work.

 

same or other error?

 

Same.

 

Warning: call_user_func_array() [function.call-user-func-array]: First argument is expected to be a valid callback, 'Array' was given in C:\wamp\www\mbpa\classes\db.php on line 48

 

Fatal error: Call to a member function execute() on a non-object in C:\wamp\www\mbpa\classes\db.php on line 49

[dzelenika]

I have similar solution in my code:

 

return call_user_func_array(array(static::$_class, $name), $arguments);

 

and it works perfectly.

Are You sure that the previous statement:

 

$stmt = $this->mysqli->prepare($query);

 

executed corectly?

[linuxlover101]

I have similar solution in my code:

 

return call_user_func_array(array(static::$_class, $name), $arguments);

 

and it works perfectly.

Are You sure that the previous statement:

 

$stmt = $this->mysqli->prepare($query);

 

executed corectly?

 

I edited insertInto().

 

function insertInto($table, $array, $param_types) {

	$set_string = $this->joinKeys($array);

	$query = "INSERT INTO ".$table." SET ".$set_string;

	$func_params = array_unshift($array, $param_types);

	if($stmt = $this->mysqli->prepare($query)) {
		call_user_func_array(array($stmt, 'bind_param'), $func_params);	
		$stmt->execute();
		$stmt->close();
	} else {
		echo 'Fail.';
	}
}

 

Now, I get Warning: Wrong parameter count for mysqli_stmt::bind_param() in C:\wamp\www\mbpa\classes\db.php on line 48

[linuxlover101]

Interesting...

 

$func_params is printing out '5'.

[dzelenika]

Interesting...

 

$func_params is printing out '5'.

 

that's the point

 

array_unshift returns number of elements in array where you added element

[dzelenika]

Interesting...

 

$func_params is printing out '5'.

 

that's the point

 

array_unshift returns number of elements in array where you added element

 

should stay:

 

call_user_func_array(array($stmt, 'bind_param'), $array);

 

because $param_types are now element of $array

 

 

[linuxlover101]

Interesting...

 

$func_params is printing out '5'.

 

that's the point

 

array_unshift returns number of elements in array where you added element

 

should stay:

 

call_user_func_array(array($stmt, 'bind_param'), $array);

 

because $param_types are now element of $array

 

Oy, just saw that in the php manual.

 

Script works now. Thanks!!