error in my sql code

[krv]

Hello i am simply trying to show data in html table from one mysql table and subtract the remainders.

 

 

This information is pulled from a table called original_invoice

Structure:

id, invoiceid, uid, qty1, qty2 etc.

 

As you can see i have a select option, two textboxes and a check box.

 

When you select and invoice and enter in a number in the textboxes and check the row where you just entered a number and click save, it will then post that information into a table called invoice_data.

Structure:

id, invoiceid, invoice_num, uid, qty1, qty2 etc.

 

invoiceid is the numbers that in the original_invoice table.

invoice_num is the selected invoice number that was posted along with the qty1,qty2 fields.

 

In the code below i have multiple sql queries in place. After the section the post/inserts data into the invoice_data table i have a query that checks to see if any data is in the table where invoiceid='$_GET['invoiceid']'. if that results to 0 it simply pulls the data from the original_invoice table that creates the standard form that needs to be shown. ( look at the image for reference).

If it does not:

the quantity left fields are to tell me how many items are left.

This works by adding up all the quantities found in invoice_data if anything exists then subtracts from the original_invoice table.

 

The problem i am running into is if i only select one item on the form and insert it into the database. i will on see that item in the html table once the page refreshes. This happens due to the sql query i run if data is found in the invoice_data table where invoiceid='$_GET['invoiceid']

 

This makes sense because thats what code reads. but i need it to show the data from original_invoice but also do the subtraction without displaying the information in the invoice_data table.

 

Sorry this is alot :/

 

<?php
///// this posts/inserts the data into the invoice_data table
            if (isset($_POST['save']))
            {
                if ($_POST['checkbox'] == 0)
                {
                    echo 'nothing checked.';
                } else
                {
                        // insert new qty's into database
                    $postinvoice = $_POST['invoice']; // gets invoice number
                    foreach ($_POST['checkbox'] as $id)
                    {
                        // create array to  insert all checked rows
                        $queries = array();
                        $poinsert = base64_decode($_POST['po']);
                        $pid = $_POST['hidProductId'][$id];
                        $qty1 = $_POST['txtQty1'][$id];
                        $qty2 = $_POST['txtQty2'][$id];
                        // keep line items consistent
                        $eno =0;
                        if ($_POST['txtQty1'][$id] > 0 && $_POST['txtQty2'][$id] > 0)
                        {
                            $msg = 'Item quanity found in both columns on same line item.';
                            $eno = 1;
                        }
                        if (empty($_POST['txtQty1'][$id]) && empty($_POST['txtQty2'][$id]))
                        {
                            $msg = 'You did not enter a quanity';
                            $eno = 1;
                        }
                        if ($eno == '0')
                        {
                            $queries[] = "('$_GET[invoice]','$postinvoice','$_GET[uid]','$poinsert','$pid','$qty1','$qty2','1')";
                            $piece = implode(", ", $queries);
                            $query1 = "INSERT INTO invoice_data (invoiceid,invoiced,uid,po,pid,qty1,qty2,status) VALUES $piece";
                            $result = mysql_query($query1) or die(mysql_error());
                        }
                        if ($result)
                        {
                            $msg = "Added";
                        }else{
                                // if $eno=1
                                echo $msg;
                        }
                    }
                }
            }
?>
<form method="post">
<tr>
        <td>part</td>
        <td>description</td>
        <td>price1</td>
        <td>price2</td>
        <td>qty1 remaining</td>
        <td>qty1 value</td>
        <td>qty2 remaining</td>
        <td>qty2 value</td>
        <td>save</td>
</tr>
<?php
// check to see if there is any records for this invoice to determine which sql script is displayed
// this script shows you remaing qty's by subtracting invoice_data qtys from the original_invoice qtys.
// the purpose of this script generates backorders
$checkID = "SELECT id,invoiceid FROM invoice_data WHERE invoiceid='$_GET[invoice]'";
$checkResult = mysql_query($checkID)or die(mysql_error());
if(mysql_num_rows($checkResult)=='0'){
// show the normal form to then input new values for qty's
$sql = "SELECT 
original_invoice.id as id,
original_invoice.invoiceid,
original_invoice.pid as pid,
original_invoice.qty1 as qty1,
original_invoice.qty2 as qty2,
customers.id as id,
products.part as part,
products.descr as descr,
products.price1 as price1,
products.price2 as price1
FROM
original_invoice,
customers,
products
WHERE customers.id='$_GET[uid]' AND original_invoice.invoiceid='$_GET[invoice]' AND original_invoice.cid='$_GET[uid]' AND original_invoice.pid = products.part";
$dataFound =0;
// end check
}else{
// else continue added quantity until items = 0
$sql = "SELECT 
original_invoice.id as id,
original_invoice.invoiceid,
original_invoice.invoiced,
original_invoice.pid as pid,
original_invoice.qty1 as qty1,
original_invoice.qty2 as qty2,
customers.id as id,
products.part as part,
products.descr as descr,
products.price1 as price1,
products.price2 as price1,
invoice_data.qty1 as iqty1,
invoice_data.qty2 as iqty2,
invoice_data.pid as ipid,
invoice_data.invoiceid as iinvoiceid,
invoice_data.uid as iuid
FROM
original_invoice,
customers,
products,
invoice_data
WHERE customers.id='$_GET[uid]' AND original_invoice.invoiceid='$_GET[invoice]' AND original_invoice.uid='$_GET[uid]' AND original_invoice.pid = products.part AND invoice_data.pid = products.part AND original_invoice.pid=invoice_data.pid";
// invoiceid is the original cart or invoice id for the database.
// invoiced is the new posted invoice number and is only in the invoice_data table.
$dataFound=1;   
}
$result = dbQuery($sql);

while ($row = dbFetchAssoc($result)) {
    
    // count the remainders
    if($dataFound=='0'){
        $qty1r = $row['qty1'];
        $qty2r = $row['qty2'];
    }else{
        $qty1r = $row['qty1']-$row['iqty1'];
        $qty2r = $row['qty2']-$row['iqty2'];            
    }
echo "
<tr>
<td>$row[part]</td>
<td>$row[descr]</td>
<td>$row[price1]</td>
<td>$row[price2]</td>
<td>$qty1r></td>
<td><input name=\"txtQty1[$row[part]]\" type=\"text\" size=\"3\" maxlength=\"3\"></td>
<td>$qty2r</td>
<td><input name=\"txtQty2[$row[part]]\" type=\"text\" size=\"3\" maxlength=\"3\">
<input name=\"hidProductId[$row[part]]\" type=\"hidden\" value=\"$row[part]\" /></td>
<td><input type='checkbox' name='checkbox[]' value='$part' /></td>
</tr>
";

}
?>
<input type="submit" name="save" value="save" />
</form>

[fenway]

TLDR... try again.