[SOLVED] Prepared array statement (Been searching for days)

[malikah]

Hi. I've been searching for days on how to insert an array of values using a prepared statement. If you look in particular at

$stmt->bind_param($esses, $values);

this is where the problem is ($values). I get the error:

 

"Number of elements in type definition string doesn't match number of bind variables"

 

I know this is where the problem is because if I use a comma-separated list it works fine. How on earth can I just use the array passed through the function?

 

$columns = array("a", "b", "c", "d", "e", "f", "g");
$values = array("1","2","3","4","5","6","7");
$numberOfEntries = sizeof($values);

insertData($columns, $values, $numberOfEntries);

function insertData($columns, $values, $numberOfEntries)
{
$questionMarks = array();
$esses = "";
$columnNames = implode(',',$columns);

for( $i=0; $i<$numberOfEntries; $i++ )
{
	$questionMarks[$i] = "?";
	$esses .= "s";
}

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

$query = "INSERT INTO theTable ($columnNames) VALUES ($questionMarks)";

if($stmt = $this->conn->prepare($query))
{		
	$stmt->bind_param($esses, $values);
	$stmt->execute();
	$stmt->close();
	return true;
}
}

[PFMaBiSmAd]

$stmt->bind_param($esses, $values);

$values is a string that contains a list of comma separated variable names. That is not the same as supplying a list of comma separated parameters to bind_param(). To do what you want would require building and executing the $stmt->bind_param(); statement using eval() since you are trying to dynamically build a line of php code.

[malikah]

$stmt->bind_param($esses, $values);

$values is a string that contains a list of comma separated variable names. That is not the same as supplying a list of comma separated parameters to bind_param(). To do what you want would require building and executing the $stmt->bind_param(); statement using eval() since you are trying to dynamically build a line of php code.

 

I still don't get it. I've been here: http://us3.php.net/manual/en/function.eval.php but the examples don't apply to my situation.

All I want to do is insert an array of values into a database using a prepared statement.

Surely there's an easy way?

 

Cheers.

[TeNDoLLA]

What module you are using PDO or something else? My guess is that you have to bind every value separately... then do the insert.

[PFMaBiSmAd]

In addition to what I already stated, your code has another problem in that $values is an array of values, while bind_param() expects a list of variables that would contain the actual values -

 

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )

 

You either need actual variables and use eval() to build the line of php code or you cannot use prepared statements to do what you are trying to do.

[malikah]

In addition to what I already stated, your code has another problem in that $values is an array of values, while bind_param() expects a list of variables that would contain the actual values -

 

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )

 

You either need actual variables and use eval() to build the line of php code or you cannot use prepared statements to do what you are trying to do.

 

I've managed to echo any one of the individual $variables values, but for some reason the prepared statement sees $values as a single value and not as a list of variables. How do you suggest using eval() in this situation?

[PFMaBiSmAd]

   $code = '$stmt->bind_param($esses, '.$variables.');';
   eval($code);

 

Where $variables is a string that contains a comma separated list of variables $variables = '$var1,$var2, ...';

 

Edit: $var1, ... could also be any type of variables, like elements of an array -

$variables = '$array[0],$array[1], ...';

[malikah]

   $code = '$stmt->bind_param($esses, '.$variables.');';
   eval($code);

 

Where $variables is a string that contains a comma separated list of variables $variables = '$var1,$var2, ...';

 

Edit: $var1, ... could also be any type of variables, like elements of an array -

$variables = '$array[0],$array[1], ...';

 

OK, here's my function with sample arrays (the real arrays are much larger):

 

$columns = array("name","address","phone","company");
$values = array("Jon","123 here st","1234567890","company Inc.");
$numberOfEntries = sizeof($values);

employerSignUpForm($columns, $values, $numberOfEntries);

function employerSignUpForm($columns, $values, $numberOfEntries)
{
$questionMarks = array();
$esses = "";
$columnNames = implode(',',$columns);
$newValues = array();

for( $i=0; $i<$numberOfEntries; $i++ )
{
	$questionMarks[$i] = "?";
	$esses .= "s";
	$$columns[$i] = $values[$i];
	$newValues[$i] = "$".$columns[$i];
}

$newValues = implode(',',$newValues);
$questionMarks = implode(',', $questionMarks);

$query = "INSERT INTO userinfo ($columnNames) VALUES ($questionMarks)";

if($stmt = $this->conn->prepare($query))
{			
	$stmt->bind_param($esses, $newValues);
	$stmt->execute();
	$stmt->close();
	return true;
}
}

 

This will lead to the error:

 

"...Number of elements in type definition string doesn't match number of bind variables..."

[PFMaBiSmAd]

You have been told multiple times that bind_param() accepts a list of variables. I even posted the bind_param() syntax definition from the documentation that shows that it requires variables as parameters. The error message you just got mentioned the bind variables. There are examples in the documentation... If you aren't, can't, or won't read the information in the documentation, what is provided in the replies, and what error messages state, you are not going to be successful at getting your code to work.

[malikah]

OK, figured it out:

 

I changed:

 

$stmt->bind_param($esses, $newValues);

 

To:

 

eval("\$stmt->bind_param(\$esses," . $newValues . ");");

 

Works flawlessly..