[RESOLVED] mysqli with prepare, bind_param and arrays for an insert class

[kender]

Hopefully the title says it all

I am trying to find a class that will perform a mysqli based insert with prepare & bind_param
actually, a class or combination of that would perform INSERT, DELETE, SELECT and UPDATE...

I have searched php.net (several functions that seem to be incomplete) and general google searches (lmgtfy is not necessary - unless you can word the query better than I have been able to)

I would like to use bind_param for the type specifications (ensuring I get the results I want), not just a generic real_query INSERT statement

The functionality is there, just not perfect, and if i could limit it to a string value i would be fine, but with an array of indefinite length it makes it harder (darn near impossible from my testing)

any help, even a point in the right direction, would be appreciated!

Thanks in advance

[Weedpacket]

It's not clear what you want - of course, if you could specify what you want precisely enough you'd be able to write it yourself: all the machinery is provided (it's called programming). For example [man]mysqli_stmt_bind_param[/man] allows you to specify the types of supplied values.

[kender]

I am looking for a function that will connect to a database, perform an insert with an array as the input values.. something like

forgive the poor quality of the example, I'm headed to bed and wanted to get this out before i went.. but you should get the idea of what i want.. (which is the functiont that actually does the work with an array of variable sizes not a set value

$link = new mysqli('db_server', 'db_user', 'db_pass', 'db_name');
$param_array = array("i","s","s","s");
$insert_array = array("Id" => "1", "first_name" => "John", "last_name" => "Smith", "state" => "New York");
insert_query_function($insert_array,$param_array,$link)
$sql = "INSERT INTO db_table ($arr1) VALUES($arr_of_question_marks_matching_length_of_arr1)";

[Weedpacket]

So ... which bit is the part causing you problems? [font=monospace]$arr1 = join(',', array_keys($insert_array));[/font]? [font=monospace]$arr_of_question_marks_matching_length_of_arr1 = join(',', array_fill(0, count($arr1), '?'));[/font]?

[kender]

the bind_param section, assigning variables, then defining them below it
the documentation on the function doesn't give me enough info to be able to figure that part out

[Weedpacket]

You mean like with [man]call_user_func_array[/man], as used for example with the class given in http://www.php.net/manual/en/mysqli-stmt.bind-param.php#109256 or http://www.php.net/manual/en/mysqli-stmt.bind-param.php#89171 then?

[kender]

something like this

<?php
$table 	= 'user';
$typeDef 	= array("s","s");
$data 	= array("first_name"=>"Jim", "last_name"=>"Jones");
$insert 	= sql_insert($data, $typeDef, $table);

function sql_insert ($data, $typeDef, $table) {
	$mysqli = mysqli_con();
	$data = cleanseArray($data);
	$keys = array_keys($data);
	$vals = array_values($data);

$key = '';
$val = '';
$type = '';
$params = '';
foreach($keys AS $key_value) {
	if($key == '') {
		$key = $key_value;
		$val = '?';
	} else {
		$key .= ', '.$key_value;
		$val .= ', ?';
	}
}
foreach($typeDef AS $value) {
	$type .= $value;
}
if ($stmt = $mysqli->prepare("INSERT INTO $table ($key) values ($val)")) {

	/* Set our params */
	for($i = 0; $i < count($vals); $i++) {
		$param_key[$i] = $vals[$i];
		if($params == '') {
			$params = "$$keys[$i]";
		} else {
			$params .= ", $$keys[$i]";
		}
	}

	/* Bind our params */
	$stmt->bind_param($type, $params);

	/* Execute the prepared Statement */
	$stmt->execute();

	/* Echo results */
	echo "Success";

	/* Close the statement */
	$stmt->close(); 
} else {
	/* Error */
	printf("Prepared Statement Error: %s\n", $mysqli->error);
}
}
?>

gives this error

arning: mysqli_stmt::bind_param() [<a href='mysqli-stmt.bind-param'>mysqli-stmt.bind-param</a>]: Number of elements in type definition string doesn't match number of bind variables in

[kender]

fixed it, here is the function if you would like to look at it
Any improvements or suggestions would be welcome!

Thanks

<?php
function bindParameters(&$statement, &$params) {
  $args   = array();
  $args[] = implode('', array_values($params));

  foreach ($params as $paramName => $paramType) {
    $args[] = &$params[$paramName];
    $params[$paramName] = null;
  }

  call_user_func_array(array(&$statement, 'bind_param'), $args);
}

function sql_insert ($data, $typeDef, $table) {
	$mysqli = mysqli_con();
	$keys = array_keys($data);
	$vals = array_values($data);

$key = '';
$val = '';
$type = '';
foreach($keys AS $key_value) {
	if($key == '') {
		$key = $key_value;
		$val = '?';
	} else {
		$key .= ', '.$key_value;
		$val .= ', ?';
	}
}

if ($stmt = $mysqli->prepare("INSERT INTO $table ($key) values ($val)")) {

	for($i = 0; $i < count($typeDef); $i++) {
		/* Set params value for binding */
		$params[$keys[$i]] = $typeDef[$i];
	}

	/* Bind our params */
	bindParameters($stmt, $params);

	for($i = 0; $i < count($typeDef); $i++) {
		/* Set params for query */
		$params[$keys[$i]] = $vals[$i];
	}

	/* Execute the prepared Statement */
	if(!$stmt->execute() ) { printf("Execute Statement Error: %s\n", $stmt->error); exit; }

	/* Echo results */
	$insert = "Success";

	/* Close the statement */
	$stmt->close(); 
} else {
	/* Error */
	$insert = "Failure";
}
return $insert;
}
?>

[PepoSkateSnow]

Hi Kender,

Your functions was a great help for me!

I have added an improvement to the sql_insert function that makes it easier to add the types to bindparam, since within the same foreach we can evaluate the field type and add the value to the array $ typeDef

        foreach ($data AS $k=>$value) {
                  
            if($key == '') {

                $key = $k;
                $val = '?';

            } else {

                $key .= ', '.$k;
                $val .= ', ?';
                        
            }

            $typeDef[]= is_int($value)==true ? 'i' : 's';
                    
        } 

Finally the script was like this

function sql_insert ($data, $typeDef, $table) {
$mysqli = mysqli_con();
$keys = array_keys($data);
$vals = array_values($data);

$key = '';
$val = '';
$type = '';
foreach ($data AS $k=>$value) {

            if($key == '') {

                $key = $k;
                $val = '?';

            } else {

                $key .= ', '.$k;
                $val .= ', ?';
                        
            }

            $typeDef[]= is_int($value)==true ? 'i' : 's';
             
        } 

if ($stmt = $mysqli->prepare("INSERT INTO $table ($key) values ($val)")) {

for($i = 0; $i < count($typeDef); $i++) {
	/* Set params value for binding */
	$params[$keys[$i]] = $typeDef[$i];
}

/* Bind our params */
bindParameters($stmt, $params);

for($i = 0; $i < count($typeDef); $i++) {
	/* Set params for query */
	$params[$keys[$i]] = $vals[$i];
}

/* Execute the prepared Statement */
if(!$stmt->execute() ) { printf("Execute Statement Error: %s\n", $stmt->error); exit; }

/* Echo results */
$insert = "Success";

/* Close the statement */
$stmt->close(); 

} else {
/ Error /
$insert = "Failure";
}
return $insert;
}
?>

Thanks a lot for your help!