php和mysql更新两个表以及一键插入两个表的问题

Question

这是我的结账页面的一些代码。我是php新手，这是我的第一个学期，我还在挣扎。该页面收集添加到购物车的订单，并完美地显示和汇总订单。这是我的问题。

有时会有 OrderIn 产品，但可能没有或不止一种，也可能有 OrderOut 产品，或者没有。很复杂，我知道。我可能尝试做的事情太多了。当我按下支付此发票按钮时，我想收集订单 ID，无论有多少或何种类型(出或入)，并将订单 ID 已付款设置为 yes，并将 OrderId 插入相应的发票、invoice_in 或发票_out，并将发货设置为“否”。

这是否可能，它将 OrderId_in、第一个产品仅更改为 yes，现在我收到 MySQL 错误“您的 SQL 语法中有错误；请检查与您的 MySQL 服务器版本相对应的手册”在第 2 行“75.18”、“否”)' 附近使用正确的语法”。我可以在这里使用一些指导。

<div class="tablecheckOut">
<form action='checkout.php' method='post'>
<p><strong>Purchases this invoice: </strong><br><br>
<?php
echo "<table class='middlecheckOut'> 
<tr>
<td class='td2'><b>Order ID: </b></td>
<td class='td2'><b>Product Name: </b></td>
<td class='td2'><b>Quantity: </b></td>
<td class='td2'><b>Price: </b></td>
</tr>";

if (isset($_GET['user_id'])) {     
    $user_id = $_GET['user_id']; 
} elseif (isset($_POST['user_id']))  {    
    $user_id = $_POST['user_id'];
} 

$display="SELECT * 
    FROM order_instate JOIN in_Product ON 
    order_instate.ip_id = in_product.ip_id
    WHERE user_id = '$user_id'; " ; 

$displayResult = @mysqli_query($dbhandle, $display)
            or die(mysqli_error($dbhandle));

$priceIn = 0;
while($row = mysqli_fetch_array($displayResult, MYSQLI_ASSOC)) { 
    if($row['orderIn_paid'] == "No") {      
echo "<tr>
<input type='hidden' name='ip_id' value='" . $row['ip_id'] . "' />
<td class='td2'>" . $row['orderIn_id'] . " &nbsp&nbsp</td>
<td class='td2'>" . $row['ip_name'] . " &nbsp&nbsp</td>
<td class='td2'>" . $row['orderIn_quantity'] . " &nbsp&nbsp</td>
<td class='td2'>$" . $row['orderIn_total'] . " &nbsp&nbsp</td>
 </tr>";

 $priceIn += $row['orderIn_total'];
 $orderIn_id = $row['orderIn_id'];
 $_SESSION['orderIn'] = $orderIn_id;
    }
 }

 if (isset($_GET['user_id'])) {     
    $user_id = $_GET['user_id']; 
} elseif (isset($_POST['user_id']))  {    
    $user_id = $_POST['user_id'];
} 

$display2="SELECT * 
    FROM order_outstate JOIN op_Product ON 
    order_outstate.op_id = op_product.op_id
    WHERE user_id = '$user_id'; " ; 

$displayResult2 = @mysqli_query($dbhandle, $display2)
            or die(mysqli_error($dbhandle));

 $priceOut = 0;
while($row2 = mysqli_fetch_array($displayResult2, MYSQLI_ASSOC)) { 
    if($row2['orderOut_paid'] == "No") {        
echo "<tr>
<input type='hidden' name='op_id' value='" . $row2['op_id'] . "' />
<td class='td2'>" . $row2['orderOut_id'] . " &nbsp&nbsp</td>
<td class='td2'>" . $row2['op_name'] . " &nbsp&nbsp</td>
<td class='td2'>" . $row2['orderOut_quantity'] . " &nbsp&nbsp</td>
<td class='td2'>$" . $row2['orderOut_total'] . " &nbsp&nbsp</td>
 </tr>";

 $priceOut += $row2['orderOut_total'];
 $orderOut_id = $row['orderOut_id'];
 $_SESSION['orderOut'] = $orderOut_id;

    }
 }
echo "</table>";

$subtotal = 0;
$tax = 0;
$gtotal = 0;
$subtotal = number_format($priceIn + $priceOut, 2);
$tax = number_format($subtotal * .074, 2);
$gtotal = number_format($subtotal + $tax, 2);

?>
</p>
<p><strong>Total Amount of Purchase(s): <?php echo "$" . " $subtotal " ?></strong></p>
<p><strong>Tax this invoice (7.4%): <?php echo "$" . " $tax " ?>  </strong></p>
<p><strong>Grand Total of Invoice: <?php echo "$" . " $gtotal " ?>  </strong></p>
<p>             
<input type="submit" name="submit" value="Pay This Invoice" style="width: 162px; height:   37px" >
<input type="button" name="print" value="Print This Invoice" style="width:162px; height: 37px" onclick="window.print()">
</p>
</form>
</div> 
</body>
</html>
<?php

if($_SERVER['METHOD'] == 'POST') {

    if(isset($_SESSION['orderIn'])) {
        $orderIn_id = $_SESSION['orderIn'];
        $orderIn_paid = "Yes";


    $changeVal="UPDATE order_instate
                 SET orderIn_paid = '$orderIn_paid'
                 WHERE orderIn_id = '$orderIn_id'; " ; 

    $changeCheck=mysqli_query($dbhandle, $changeVal) 
                        or die(mysqli_error($dbhandle));
     }


    if(isset($_SESSION['orderOut'])) {          
        $orderOut_id = $_SESSION['orderOut'];       
        $orderOut_paid = "Yes";


    $changeVal2="UPDATE order_outstate
                 SET  orderOut_paid = '$orderOut_paid'
                 WHERE orderOut_id = '$orderOut_id'; " ; 

    $changeCheck2=mysqli_query($dbhandle, $changeVal2) 
                        or die(mysqli_error($dbhandle));
    }

    $invoiceIn_total = 0;
    $invoiceIn_total = $gtotal;
    $invoiceIn_shipped = "No";

    $add ="INSERT INTO invoice_in(user_id, orderIn_id, invoiceIn_total, invoiceIn_shipped)
                VALUES ('$user_id', '$orderIn_id '$invoiceIn_total', '$invoiceIn_shipped')"; 

    $addCheck=mysqli_query($dbhandle, $add)
                        or die(mysqli_error($dbhandle));

    $invoiceOut_total = 0;
    $invoiceOut_total = $gtotal;
    $invoiceOut_shipped = "No";


    $add2 ="INSERT INTO invoice_out(user_id, orderOut_id, invoiceOut_total, invoiceOut_shipped)
                VALUES ('$user_id', '$orderOut_total '$invoiceOut_total', '$invoiceOut_shipped')"; 

    $addCheck2=mysqli_query($dbhandle, $add2)
                        or die(mysqli_error($dbhandle));

    header("location: userOrders.php");
}

?>

Answer 1

您的代码有一些问题。

有

VALUES ('$user_id', '$orderIn_id  '$invoiceIn_total',
                                ^^

缺少引号和逗号

做

VALUES ('$user_id', '$orderIn_id',  '$invoiceIn_total',

同样的事情

VALUES ('$user_id', '$orderOut_total  '$invoiceOut_total',
                                    ^^

做

VALUES ('$user_id', '$orderOut_total', '$invoiceOut_total',

这是 SQL 错误的来源。

$orderOut_total 在您的发布代码中未定义。

另外，根据您发表的评论:

“Fred，我发现为什么我的 OrderOut_id 没有被填充，我发现了一个语法错误，我在创建和定义变量时没有使用正确的 $row2 来获取它。它现在适用于 OrderIn 和 OrderOut ，虽然我还没有测试多个订单。但我正在让它工作，感谢 Fred，他帮助我找到了确切的语法错误。”

• 这是问题的最终解决方案。

<小时/>

我必须注意，您当前的代码对 SQL injection 开放。 。使用prepared statements ，或PDO with prepared statements ，它们更安全。

<小时/>

添加error reporting到文件顶部，这将有助于查找错误。

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

旁注:错误报告只能在暂存阶段完成，而不能在生产阶段完成。