Zero
Zero

Reputation: 71

Using MySQLi prepared statements in PHP, getting "No data supplied for parameters in prepared statement "

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

Upvotes: 1

Views: 1323

Answers (1)

Barmar
Barmar

Reputation: 780713

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";
}

Upvotes: 2

Related Questions