問題描述

我正在努力修改以下(有效)以使用準備好的語句:

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)"；}//結(jié)束 foreachecho "</table></div>";

我一直在嘗試調(diào)整在同一頁面中運行在此之上的(工作)準備好的語句/查詢，它將單行插入到不同的表中.但是這個第二個查詢(到不同的數(shù)據(jù)庫表中)從源表單中的行項目表的多個(動態(tài)#)行插入數(shù)據(jù).

我已經(jīng)研究了幾個小時，但我無法完全弄清楚如何使用行項目循環(huán)實現(xiàn)準備好的語句.我以為會是這樣，但這不是插入.

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 ->執(zhí)行();$stmt->close();}//如果 $stmt}//結(jié)束 foreachecho "</table></div>";

有人可以透露一些信息嗎?非常感謝.

svs

你不需要

$stmt = $mysqli->stmt_init();

你應該可以打電話

$stmt = $mysqli->prepare($query);

另一個問題是您在循環(huán)中內(nèi)部設置查詢.你不應該那樣做.將 $stmt 移到循環(huán)外，并在設置變量后僅在其中運行 execute.最后需要加上?，讓MySQL知道參數(shù)

$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 循環(huán)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！