php and mysql problems with updating two tables, and inserting into two tables with one button

Question

Here is some of my code for my checkout page. I am new to php, this is my first semester, and I am still struggling. This page collects orders added to the cart and displays and totals the orders perfectly. Here is my problem.

Sometimes there will be OrderIn products and there can be none or more than one of these, and there can also be OrderOut products, or none. complicated, I know. I may be trying to do too much. When I press the pay this invoice button, I want to collect the Order ID's, no matter how many or what kind, (out or in) and set the order ID Paid to yes, and insert the OrderId's into the appropriate invoice, invoice_in or invoice_out, and set shipped to NO.

Is this possible, it is changing the OrderId_in, first product only to yes, and now I am getting a MySQL error of "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '75.18', 'No')' at line 2". I could use some direction here please.

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

?>

score 2 · Accepted Answer · edited May 23 '17 at 10:26

There are a few things wrong with your code.

There's

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

is missing a quote and a comma

do

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

same thing for

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

do

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

which are where the SQL errors come from.

$orderOut_total is undefined in your posted code.

Plus, from a comment you made:

"Fred, I found why my OrderOut_id was not getting populated, I found a syntax error, I was creating and defining the variable without using the correct $row2 to grab it. It now works for both OrderIn and OrderOut, although I have not tested for multiple orders. But I am getting it working, thanks to you Fred, that worked in finding my exact syntax error."

Which came to be the final solution to the problem.

I must note that your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

Add error reporting to the top of your file(s) which will help find errors.

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

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

@user3447733 You're very much welcome, glad to see the matter was resolved, *cheers* — Funk Forty Niner, Nov 25 '14 at 17:17
@Fred-ii- I'm marking this up because watching the discussion this was more than an answer this was a Save, :) Good work sir! — Martin, Nov 25 '14 at 17:22

php and mysql problems with updating two tables, and inserting into two tables with one button

1 Answers1