MySQLi准备好的语句&foreach 循环 [英] MySQLi prepared statement & foreach loop
问题描述
我正在努力修改以下(有效)以使用准备好的语句:
echo "<h4>值$_POSTed from *LINE-ITEMS TABLE* in input.php:</h4><表格>";foreach ($_POST['date'] as $i => $value) {$invNum = $_POST['invNum'];$date = $_POST['date'][$i];$hours = $_POST['hours'][$i];$rate = $_POST['rate'][$i];$dateTotal = $_POST['dateTotal'][$i];回声<tr><td>".$i."</td><td>".$date."</td><td>".$hours."</td><td>".$rate."</td><td>".$dateTotal."</td></tr>";$query = "INSERT INTO Invoice_Line_Items SETINVOICE_NUMBER = '$invNum',日期 = '$日期',小时 = '$小时',RATE = '$rate',DATE_TOTAL = '$dateTotal'关于重复密钥更新INVOICE_NUMBER = VALUES(INVOICE_NUMBER),日期 = 值(日期)，小时 = 值(小时)，费率 = 值(费率)，DATE_TOTAL = VALUES(DATE_TOTAL)"；}//结束 foreachecho "</table></div>";我一直在尝试调整在同一页面中运行在此之上的(工作)准备好的语句/查询，它将单行插入到不同的表中.但是这个第二个查询(到不同的数据库表中)从源表单中的行项目表的多个(动态#)行插入数据.
我已经研究了几个小时，但我无法完全弄清楚如何使用行项目循环实现准备好的语句.我以为会是这样，但这不是插入.
echo "<h4>值$_POSTed from *LINE-ITEMS TABLE* in input.php:</h4><表格>";//这是表单中的行项目表；我不必在查询之前获取这些值吗?foreach ($_POST['date'] as $i => $value) {$invNum = $_POST['invNum'];$date = $_POST['date'][$i];$hours = $_POST['hours'][$i];$rate = $_POST['rate'][$i];$dateTotal = $_POST['dateTotal'][$i];//确认变量/值echo "<tr><td>".$i."</td><td>".$date."</td><td>".$hours."</td><td>".$rate."</td><td>".$dateTotal."</td></tr>";$stmt = $mysqli->stmt_init();$query = "INSERT INTO Invoice_Line_ItemsINVOICE_NUMBER = '$invNum',日期 = '$日期',小时 = '$小时',RATE = '$rate',DATE_TOTAL = '$dateTotal'关于重复密钥更新INVOICE_NUMBER = VALUES(INVOICE_NUMBER),日期 = 值(日期)，小时 = 值(小时)，费率 = 值(费率)，DATE_TOTAL = VALUES(DATE_TOTAL)"；如果 ($stmt->prepare($query)) {$stmt ->bind_param("ssddd", $invNum, $date, $hours, $rate, $dateTotal);$stmt ->执行();$stmt->close();}//如果 $stmt}//结束 foreachecho "</table></div>";有人可以透露一些信息吗?非常感谢.
svs
解决方案 你不需要
$stmt = $mysqli->stmt_init();
你应该可以打电话
$stmt = $mysqli->prepare($query);
另一个问题是您在循环中内部设置查询.你不应该那样做.将 $stmt 移到循环外，并在设置变量后仅在其中运行 execute.最后需要加上?，让MySQL知道参数
$query = "INSERT INTO Invoice_Line_ItemsINVOICE_NUMBER = ?,日期 = ?,小时 = ?,率 = ?,DATE_TOTAL = ?关于重复密钥更新INVOICE_NUMBER = VALUES(INVOICE_NUMBER),日期 = 值(日期)，小时 = 值(小时)，费率 = 值(费率)，DATE_TOTAL = VALUES(DATE_TOTAL)"；$invNum = $date = $hours = $rate = $dateTotal = '';$stmt = $mysqli->prepare($query);$stmt->bind_param("ssddd", $invNum, $date, $hours, $rate, $dateTotal);foreach ($_POST['date'] as $i => $value) {$invNum = $_POST['invNum'];$date = $_POST['date'][$i];$hours = $_POST['hours'][$i];$rate = $_POST['rate'][$i];$dateTotal = $_POST['dateTotal'][$i];$stmt->execute();}
I'm struggling to revise the following (which works) to use a prepared statement:
echo "<div class="debug">
<h4>values $_POSTed from *LINE-ITEMS TABLE* in input.php:</h4>
<table>";
foreach ($_POST['date'] as $i => $value) {
$invNum = $_POST['invNum'];
$date = $_POST['date'][$i];
$hours = $_POST['hours'][$i];
$rate = $_POST['rate'][$i];
$dateTotal = $_POST['dateTotal'][$i];
echo "<tr>
<td>".$i."</td>
<td>".$date."</td>
<td>".$hours."</td>
<td>".$rate."</td>
<td>".$dateTotal."</td>
</tr>";
$query = "INSERT INTO Invoice_Line_Items SET
INVOICE_NUMBER = '$invNum',
DATE = '$date',
HOURS = '$hours',
RATE = '$rate',
DATE_TOTAL = '$dateTotal'
ON DUPLICATE KEY UPDATE
INVOICE_NUMBER = VALUES(INVOICE_NUMBER),
DATE = VALUES(DATE),
HOURS = VALUES(HOURS),
RATE = VALUES(RATE),
DATE_TOTAL = VALUES(DATE_TOTAL)
";
} // END foreach
echo "</table></div>";
I've been trying to adapt the (working) prepared statement/query running above this in the same page, which inserts a single row into a different table. But this 2nd query (into a different db table) inserts data from multiple (dynamic # of) rows from a line-items table within the source form.
I've been hacking at it for hours but I can't quite sort out how to implement a prepared statement with the line-items loop. I thought it would be along these lines, but this is not inserting.
echo "<div class="debug">
<h4>values $_POSTed from *LINE-ITEMS TABLE* in input.php:</h4>
<table>";
// this is the line-items table in the form; don't I have to get these values before the query?
foreach ($_POST['date'] as $i => $value) {
$invNum = $_POST['invNum'];
$date = $_POST['date'][$i];
$hours = $_POST['hours'][$i];
$rate = $_POST['rate'][$i];
$dateTotal = $_POST['dateTotal'][$i];
// confirm vars/values
echo "<tr><td>".$i."</td><td>".$date."</td><td>".$hours."</td><td>".$rate."</td><td>".$dateTotal."</td></tr>";
$stmt = $mysqli->stmt_init();
$query = "INSERT INTO Invoice_Line_Items
INVOICE_NUMBER = '$invNum',
DATE = '$date',
HOURS = '$hours',
RATE = '$rate',
DATE_TOTAL = '$dateTotal'
ON DUPLICATE KEY UPDATE
INVOICE_NUMBER = VALUES(INVOICE_NUMBER),
DATE = VALUES(DATE),
HOURS = VALUES(HOURS),
RATE = VALUES(RATE),
DATE_TOTAL = VALUES(DATE_TOTAL)
";
if ($stmt->prepare($query)) {
$stmt -> bind_param("ssddd", $invNum, $date, $hours, $rate, $dateTotal);
$stmt -> execute();
$stmt->close();
} // if $stmt
} // END foreach
echo "</table></div>";
Can someone please shed some light? Much appreciated.
svs
解决方案 You don't need
$stmt = $mysqli->stmt_init();
You should be able to just call
$stmt = $mysqli->prepare($query);
Another problem is you're setting the query up inside your loop. You shouldn't do that. Move $stmt outside the loop and only run execute inside once you've set your variables up. Finally, you need to add ? so MySQL knows the parameters
$query = "INSERT INTO Invoice_Line_Items
INVOICE_NUMBER = ?,
DATE = ?,
HOURS = ?,
RATE = ?,
DATE_TOTAL = ?
ON DUPLICATE KEY UPDATE
INVOICE_NUMBER = VALUES(INVOICE_NUMBER),
DATE = VALUES(DATE),
HOURS = VALUES(HOURS),
RATE = VALUES(RATE),
DATE_TOTAL = VALUES(DATE_TOTAL)
";
$invNum = $date = $hours = $rate = $dateTotal = '';
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ssddd", $invNum, $date, $hours, $rate, $dateTotal);
foreach ($_POST['date'] as $i => $value) {
$invNum = $_POST['invNum'];
$date = $_POST['date'][$i];
$hours = $_POST['hours'][$i];
$rate = $_POST['rate'][$i];
$dateTotal = $_POST['dateTotal'][$i];
$stmt->execute();
}
这篇关于MySQLi准备好的语句&foreach 循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
