我之所以把它放在循环中,是因为我想为指定月份的每一天添加特定的数据,因此循环运行'n'次,其中n是一个月中的天数。这是我的代码:$i=0;
while ($i < $loop_count) {
$day_prefix = $i + 1;
if (strlen($day_prefix) == 1) {
$day_prefix = "0" . $day_prefix; //this gives 2012-01-01 through to 2012-01-31
}
if ($stmt = $sql_con->prepare("UPDATE DailyBudget SET amount = ?
WHERE date=?, code=?")) {
$stmt->bind_param('isi', $budget_daily, $date_iterator . $day_prefix, $code);
$stmt->execute();
$stmt->close();
}else
echo "fail! </br>";
$i++;
}
$sql_con->close();
}
我花了比我想的更多的时间来弄清楚这一点,所以如果能提供一些帮助,我将不胜感激!
提前谢谢。
我认为您的主要问题是where条件。where条件是一个表达式,对于要更新的每一行,该表达式的计算结果都为true。作为一个表达式,组件/子条件必须通过逻辑运算符组合在where条件中。换句话说,在where子句/条件中使用AND
而不是逗号(,
)
考虑修改你的代码,使其看起来更像这样:
if ($stmt = $sql_con->prepare('UPDATE DailyBudget SET amount = ? WHERE date=? AND code=?')) {
$year = 2012;
$month = 1;
$days_in_month = (int) date('d',(mktime(0,0,0,$month+1,1,$year)-86400));
$day = 1;
while ($day <= $days_in_month) {
$budget_daily = 15.75 - .25 * $day;
$code = 1;
$date = $year.'-'.str_pad($month,2,0,STR_PAD_LEFT).'-'.str_pad($day,2,0,STR_PAD_LEFT);
$stmt->bind_param('dsi', $budget_daily, $date, $code);
$stmt->execute();
$day++;
}
$stmt->close();
}
else echo 'WARNING: There was an error with the prepare: '.$sql_con->error."n";
$sql_con->close();
在动态创建值并为每次插入准备语句时,似乎应该使用bind_value
而不是bind_param
。对于bind_param
,你应该尝试类似的东西
$i = 0;
$stmt = $sql_con->prepare("UPDATE DailyBudget SET amount = ? WHERE date=?, code=?");
$stmt->bind_param('isi', $budget_daily, $date_iterator_prefix, $code);
while ($i < $loop_count) {
$day_prefix = $i + 1;
if (strlen($day_prefix) == 1) {
$day_prefix = "0" . $day_prefix; //this gives 2012-01-01 through to 2012-01-31
}
$date_iterator_prefix = $date_iterator . $day_prefix;
$stmt->execute();
$i++;
}
$stmt->close();
$sql_con->close();
实际上,prepared语句的好处是它只准备一次,执行多次。
因此,您必须将prepare置于循环之前
$sth = $dbh->prepare('UPDATE DailyBudget SET amount = ? WHERE date=?, code=?"');
while($i < $loop_count) {
$sth->execute(array($budget_daily, $date_iterator.$day_prefix, $code));
}