adding/subtracting rows from multiple tables
Experts,
I have a script that handles product_id's and quantities for a back order table.
Example setup-
table1: (order table)
id, user_id,order_id,product_i
table 2: (back orders)
id,user_id,invoice_id,prod
table 3: (product table)
id, product_id, price, etc..
table 4: (customers)
id, user name, etc...
I have a html table which displays the data from table 1:
This table also has a script that inserts new quantities per product_id into table 2.
My issues are in my sql script.
I am trying to have it show the html table with the data from table 1, but then also have a column next the the new qty field (inserted into table 2) that shows quantity left. ( subtracts quantity from table 2 to table 1 per product_id.
I am using Mysql/php 5.
I have attached an image that is an example of the html table.
Below is a link to another post related to this issue with more information. Any advice is greatly appreciated.
https://www.experts-exchange.com/questions/24272171/error-in-my-SQL-code.html
I have a script that handles product_id's and quantities for a back order table.
Example setup-
table1: (order table)
id, user_id,order_id,product_i
table 2: (back orders)
id,user_id,invoice_id,prod
table 3: (product table)
id, product_id, price, etc..
table 4: (customers)
id, user name, etc...
I have a html table which displays the data from table 1:
This table also has a script that inserts new quantities per product_id into table 2.
My issues are in my sql script.
I am trying to have it show the html table with the data from table 1, but then also have a column next the the new qty field (inserted into table 2) that shows quantity left. ( subtracts quantity from table 2 to table 1 per product_id.
I am using Mysql/php 5.
I have attached an image that is an example of the html table.
Below is a link to another post related to this issue with more information. Any advice is greatly appreciated.
https://www.experts-exchange.com/questions/24272171/error-in-my-SQL-code.html
<?php
$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";
?>
ASKER
Sorry for not making the tables clear. I can do the php subtraction but if you look at my code snippet the data in the html form should be from orginal_invoice or table1 ( the example table). But that snippet is pulling showing data from the invoice_data table in the html form. I access the invoice_data table just to do the subtraction. thats my issue.
I'm sorry, I don't understand what you mean. If you are pulling data from one table but should have pulled it from a different table, why don't you just change your query so that you pull the data from the correct table?
ASKER
I am pulling and comparing data from 2 tables.
table1: (order table or original_invoice)
id, user_id,order_id,product_i
table 2: (back orders or invoice_data)
id,user_id,order_id,invoic
example:
table1
1, 20,ORDERID123,PROD2423, 40
table 2
1, 20 , ORDERID123,INVOICE101, PROD2423, 20
1, 20 , ORDERID123,INVOICE102, PROD2423,20
Notice everything is the same except for INVOICE#
My snippet pulls everything where ORDERID = $_GET[invoice_id] user_id $_GET[uid] for both tables.
im echoing out only the data from table one but for some reason its displaying data from table 2. I think it has to do with part of my sql original_invoice.pid=invoi
Thank you for your help..I have been stuck on this for 3 days now.
table1: (order table or original_invoice)
id, user_id,order_id,product_i
table 2: (back orders or invoice_data)
id,user_id,order_id,invoic
example:
table1
1, 20,ORDERID123,PROD2423, 40
table 2
1, 20 , ORDERID123,INVOICE101, PROD2423, 20
1, 20 , ORDERID123,INVOICE102, PROD2423,20
Notice everything is the same except for INVOICE#
My snippet pulls everything where ORDERID = $_GET[invoice_id] user_id $_GET[uid] for both tables.
im echoing out only the data from table one but for some reason its displaying data from table 2. I think it has to do with part of my sql original_invoice.pid=invoi
Thank you for your help..I have been stuck on this for 3 days now.
First, let's just verify that I understand this correctly. The example shows one row from the original_invoice, user with user_id=20 have ordered a quantity of 40 of product with id PROD2423, the order id is 123. The two rows from invoice_data represents two invoices/deliverances, each have a quantity of 20, so these 40 ordered products are now fully delivered. Correct?
Why is invoice_data.id=1 in both rows? Isn't this column unique?
What is the primary key for both tables? Is order_id unique in the original_invoice table?
>> My snippet pulls everything where ORDERID = $_GET[invoice_id] user_id $_GET[uid] for both tables.
No... there is no ORDERID, no $_GET[invoice_id] and no user_id. These are the conditions from the query in the snippet:
customers.id='$_GET[uid]' AND
original_invoice.invoiceid
original_invoice.uid='$_GE
original_invoice.pid = products.part AND
invoice_data.pid = products.part AND
original_invoice.pid=invoi
The only condition for invoice_data is on invoice_data.pid, that seems strange. I would think this resulted in too many rows returned. When the same product exists in multiple original_invoice records, the wrong invoice_data records would be connected. You should have some more conditions in the where clause to connect the correct invoice_data records, at least something to connect it to the correct order:
invoice_data.order_id = original_invoice.order_id AND
invoice_data.pid = original_invoice.pid
If order_id is not unique in the original_invoice table, it must at least be unique for each user, and then you also need this:
invoice_data.uid = original_invoice.uid
>> im echoing out only the data from table one but for some reason its displaying data from table 2.
This could happen if the columns had the same names. You must use column aliases in the query to avoid this, but that is allready implemented in your query, except from customers.id/original_invo
If the extra data from table 2 is displayed on multiple unexpected rows, the problem is most likely the missing conditions in the where clause mentioned above.
Why is invoice_data.id=1 in both rows? Isn't this column unique?
What is the primary key for both tables? Is order_id unique in the original_invoice table?
>> My snippet pulls everything where ORDERID = $_GET[invoice_id] user_id $_GET[uid] for both tables.
No... there is no ORDERID, no $_GET[invoice_id] and no user_id. These are the conditions from the query in the snippet:
customers.id='$_GET[uid]' AND
original_invoice.invoiceid
original_invoice.uid='$_GE
original_invoice.pid = products.part AND
invoice_data.pid = products.part AND
original_invoice.pid=invoi
The only condition for invoice_data is on invoice_data.pid, that seems strange. I would think this resulted in too many rows returned. When the same product exists in multiple original_invoice records, the wrong invoice_data records would be connected. You should have some more conditions in the where clause to connect the correct invoice_data records, at least something to connect it to the correct order:
invoice_data.order_id = original_invoice.order_id AND
invoice_data.pid = original_invoice.pid
If order_id is not unique in the original_invoice table, it must at least be unique for each user, and then you also need this:
invoice_data.uid = original_invoice.uid
>> im echoing out only the data from table one but for some reason its displaying data from table 2.
This could happen if the columns had the same names. You must use column aliases in the query to avoid this, but that is allready implemented in your query, except from customers.id/original_invo
If the extra data from table 2 is displayed on multiple unexpected rows, the problem is most likely the missing conditions in the where clause mentioned above.
>> Is order_id unique in the original_invoice table?
Probably not, I guess there could be multiple products on the same order. But the combination of order_id and pid is perhaps unique? Or could there be multiple rows with the same product on the same order?
Probably not, I guess there could be multiple products on the same order. But the combination of order_id and pid is perhaps unique? Or could there be multiple rows with the same product on the same order?
ASKER
I have figured out some bugs that will help out my explanation.
First:
>>Why is invoice_data.id=1 in both rows? Isn't this column unique?
id means nothing.
invoice_id is a md5 hash. these two tables dont have that relationship.
what i need to do is either a sub select or union.
I ran the snippet code in phpmyadmin.
it combining both tables as the code says. my mistake. thats why im seeing multiple rows.
orider_id is unique per user_id
>>
First, let's just verify that I understand this correctly. The example shows one row from the original_invoice, user with user_id=20 have ordered a quantity of 40 of product with id PROD2423, the order id is 123. The two rows from invoice_data represents two invoices/deliverances, each have a quantity of 20, so these 40 ordered products are now fully delivered. Correct?
<<
Correct.
Just saw your new comment.
original_invoices can hold multiple product_id's and userid_id's the unique field is the invoice_id or the md5 hash.
when you insert products into invoice_data same concept applies for the unique field.
getting back to before.
i need to add up quantities per product_id on a single order_id within in the invoice_data table.
even if the invoice_id(or invoice_num, not the hash) is different.
that needs to be sub selected from original_invoice table?
so the final result is data from original_invoice dispalyed in html form but with columns next to the qty fields show the added amount from invoice_data subtracted from original_invoice showing quantity left per product_id
First:
>>Why is invoice_data.id=1 in both rows? Isn't this column unique?
id means nothing.
invoice_id is a md5 hash. these two tables dont have that relationship.
what i need to do is either a sub select or union.
I ran the snippet code in phpmyadmin.
it combining both tables as the code says. my mistake. thats why im seeing multiple rows.
orider_id is unique per user_id
>>
First, let's just verify that I understand this correctly. The example shows one row from the original_invoice, user with user_id=20 have ordered a quantity of 40 of product with id PROD2423, the order id is 123. The two rows from invoice_data represents two invoices/deliverances, each have a quantity of 20, so these 40 ordered products are now fully delivered. Correct?
<<
Correct.
Just saw your new comment.
original_invoices can hold multiple product_id's and userid_id's the unique field is the invoice_id or the md5 hash.
when you insert products into invoice_data same concept applies for the unique field.
getting back to before.
i need to add up quantities per product_id on a single order_id within in the invoice_data table.
even if the invoice_id(or invoice_num, not the hash) is different.
that needs to be sub selected from original_invoice table?
so the final result is data from original_invoice dispalyed in html form but with columns next to the qty fields show the added amount from invoice_data subtracted from original_invoice showing quantity left per product_id
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will try this out and get back to you. Thank you for all your help.
ASKER
Ok the code is working but the sum command working right.
if i use the code you provided it shows me the form with the original quantities and data correct.
I am using
>>
where
p.part=o.pid and
c.id=o.uid and
o.invoiceid=$invoiceid and
o.uid=$uid
<<
for the where the result i want to see if there is data in the invoice_data table work with
o.pid=i.pid
It calculates the remainder right. but then it only shows the data in the invoice_data table not both of them combined.
any suggestions?
if i use the code you provided it shows me the form with the original quantities and data correct.
I am using
>>
where
p.part=o.pid and
c.id=o.uid and
o.invoiceid=$invoiceid and
o.uid=$uid
<<
for the where the result i want to see if there is data in the invoice_data table work with
o.pid=i.pid
It calculates the remainder right. but then it only shows the data in the invoice_data table not both of them combined.
any suggestions?
>> the result i want to see if there is data in the invoice_data table work with
>> o.pid=i.pid
This criteria is allready in the ON clause for the left join. Why do you need it in the where clause?
>> It calculates the remainder right.
What reminder? Do you mean the difference of original_invoice.qty and sum(invoice_data.qty)?
>> but then it only shows the data in the invoice_data table not both of them combined.
What do you mean it only shows the data in invoice_data? Are the other columns empty? Please show your query.
>> o.pid=i.pid
This criteria is allready in the ON clause for the left join. Why do you need it in the where clause?
>> It calculates the remainder right.
What reminder? Do you mean the difference of original_invoice.qty and sum(invoice_data.qty)?
>> but then it only shows the data in the invoice_data table not both of them combined.
What do you mean it only shows the data in invoice_data? Are the other columns empty? Please show your query.
ASKER
>>What reminder? Do you mean the difference of original_invoice.qty and sum(invoice_data.qty)?
yes
im trying to get invoice_data.qty1 - original_invoice.qty1 per pid
yes
im trying to get invoice_data.qty1 - original_invoice.qty1 per pid
Wouldn't that result in a negative number? Are you still using the group by query?
Please show your query.
Please show your query.
ASKER
I think i am having issues with the group by. But do you understand what i trying to do with the subtraction?
Treat h as o, cqty as qty1 and tqty as qty2
>>
SELECT
h.invoiceid as hinvoiceid,
h.pid as hpid,
h.cqty as hcqty,
h.tqty as htqty,
c.id as cid,
c.price_struct,
p.part as part,
p.descr as descr,
p.sell1_china,
p.sell2_china,
p.sell3_china,
p.sell4_china,
p.sell1_taiwan,
p.sell2_taiwan,
p.sell3_taiwan,
p.sell4_taiwan,
sum(i.cqty) as icqty,
sum(i.tqty) as itqty
FROM
invoice_h as h,customers as c,products as p
LEFT JOIN invoice_data as i ON
i.invoiceid='$_GET[documen
WHERE
p.part=h.pid AND
c.id=h.cid AND
h.invoiceid='$_GET[documen
h.cid='$_GET[cid]'
GROUP BY 1,2,3,4,5,i.cqty,i.tqty
Treat h as o, cqty as qty1 and tqty as qty2
>>
SELECT
h.invoiceid as hinvoiceid,
h.pid as hpid,
h.cqty as hcqty,
h.tqty as htqty,
c.id as cid,
c.price_struct,
p.part as part,
p.descr as descr,
p.sell1_china,
p.sell2_china,
p.sell3_china,
p.sell4_china,
p.sell1_taiwan,
p.sell2_taiwan,
p.sell3_taiwan,
p.sell4_taiwan,
sum(i.cqty) as icqty,
sum(i.tqty) as itqty
FROM
invoice_h as h,customers as c,products as p
LEFT JOIN invoice_data as i ON
i.invoiceid='$_GET[documen
WHERE
p.part=h.pid AND
c.id=h.cid AND
h.invoiceid='$_GET[documen
h.cid='$_GET[cid]'
GROUP BY 1,2,3,4,5,i.cqty,i.tqty
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did IT!!!!!!!!
Thank you so much for stickin with me!
Thank you so much for stickin with me!
original_invoice.qty1 - invoice_data.qty1 as qty1_diff,
original_invoice.qty2 - invoice_data.qty2 as qty2_diff,
In PHP, you can subtract like this:
$qty1_diff = $row['qty1'] - $row['iqty1'];
$qty2_diff = $row['qty2'] - $row['iqty2'];