1

I'm trying to update my site to use prepared statements, but I keep getting this error and I cannot seem to figure out why. I've been searching Google and Stackoverflow for a week, trying everything I've found but nothing has fixed the issue. I'm sure I'm just misunderstanding something somewhere. Here is the code that produces the error:

$query = "INSERT INTO `$table` (type, name, company, amount, currentbalance, interest, startingbalance, term, frequency, entrymonth, entryyear, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

echo "Preparing query...";
$addstmt = $db->prepare($query);
echo "(" . $addstmt->errno . ") " . $addstmt->error;
echo "<br>Binding params...";
$addstmt->bind_param('s', empty($type) ? "income" : $type);
$addstmt->bind_param('s', empty($name) ? "" : $name);
$addstmt->bind_param('s', empty($company) ? "" : $company);
$addstmt->bind_param('d', empty($amount) ? 0.0 : $amount);
$addstmt->bind_param('d', empty($currentbalance) ? 0.0 : $currentbalance);
$addstmt->bind_param('d', empty($interest) ? 0.0 : $interest);
$addstmt->bind_param('d', empty($startingbalance) ? 0.0 : $startingbalance);
$addstmt->bind_param('i', empty($term) ? 0 : $term);
$addstmt->bind_param('i', empty($freq) ? 4 : $freq);
$addstmt->bind_param('i', empty($month) ? 0 : $month);
$addstmt->bind_param('i', empty($year) ? 2015 : $year);
$addstmt->bind_param('s', empty($notes) ? "" : $notes);
echo "(" . $addstmt->errno . ") " . $addstmt->error;
echo "<br>Executing statement...";
$result = $addstmt->execute();
echo "(" . $addstmt->errno . ") " . $addstmt->error;

This code outputs the following:

Preparing query...(0)
Binding params...(0)
Executing statement...(2031) No data supplied for parameters in prepared statement

And obviously, nothing is inserted into the database. Please help me to understand what I am doing wrong. Thank you all in advance.

Eric

Zero
  • 71
  • 1
  • 13

1 Answers1

2

You don't call bind_param repeatedly for each parameter, you call it once with all the parameters.

$addstmt->bind_param('sssddddiiiis', $type, $name, $company, $amount, $currentbalance, $interest, $startingbalance, $term, $freq, $month, $year, $notes);

You also can't use expressions in the arguments. Parameters are bound to references, so you have to give variables. To provide defaults, you have to do it by setting the variables themselves, e.g.

if (empty($type)) {
    $type = "income";
}
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • This is perfect, thank you. This brings up another question however: suppose the number of parameters is dynamic. (e.g. the number of columns in a table will not always be the same) How would I make prepared statements work in such a case? – Zero Feb 13 '15 at 02:03
  • See http://stackoverflow.com/questions/5100046/how-to-bind-mysqli-bind-param-arguments-dynamically-in-php/5108167#5108167 – Barmar Feb 13 '15 at 07:48