Solved

adding/subtracting rows from multiple tables

Posted on 2009-03-30
16
2,862 Views
Last Modified: 2013-12-12
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_id,qty

table 2: (back orders)
id,user_id,invoice_id,product_id,qty,

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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_24272171.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";

?>

Open in new window

Capture.JPG
0
Comment
Question by:krv123
  • 8
  • 8
16 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
Subtraction is done with the dash character. You can do it in the SQL or in the PHP code. I don't know which of the tables in your query is "table 1" and "table 2", there is no "order" or "back orders" table there, but I guess it must be the "invoice" and "original_invoice" tables. There is no "qty" columns in your query, but there are "qty1", "qty2", "iqty1" and "iqty2", so I am guessing those are the columns you are referring to. In the SQL, you can subtract like this:

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'];
0
 
LVL 1

Author Comment

by:krv123
Comment Utility
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.
0
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
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?
0
 
LVL 1

Author Comment

by:krv123
Comment Utility
I am pulling and comparing data from 2 tables.
table1: (order table or original_invoice)
id, user_id,order_id,product_id,qty

table 2: (back orders or invoice_data)
id,user_id,order_id,invoice_id,product_id,qty,

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=invoice_data.pid.
Thank you for your help..I have been stuck on this for 3 days now.
0
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
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='$_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

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_invoice.id, they are both called "id":

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.
0
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
>> 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?
0
 
LVL 1

Author Comment

by:krv123
Comment Utility
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
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
Comment Utility
>> that needs to be sub selected from original_invoice table?

I think you need a group by query. This will allow you to sum the quantities for multiple rows in the invoice_data table for one single original_invoice row. Something like this:

select ...
from products as p,customer as c,original_invoice as o
left join  invoice_data as i on ...
where ...
group by ...

Left join is used because there could be ordered products with no invoice_data record, i.e. delivered quantity = 0.

I'm not sure about which fields to use for connecting the tables, though. Both tables have orderid? The combination uid/orderid/pid is connecting the tables? I am guessing a bit here, but maybe you can spot the mistakes and fill out the blanks...

The "group by 1,2,3..." simply means to group by the 11 first columns. It is easier to write than "group by o.id, o.invoiceid, o.invoiced,...". Rememeber to modify it if you change the number of columns in the query.

Put in a real uid for $uid and a real invoiceid for $invoiceid and run it in phpmyadmin first, to see if you get correct results.
select o.id as oid,

  o.invoiceid,

  o.invoiced,

  o.pid as pid,

  o.qty1 as qty1,

  o.qty2 as qty2,

  c.id as cid,

  p.part as part,

  p.descr as descr,

  p.price1 as price1,

  p.price2 as price1,

  sum(i.qty1) as iqty1,

  sum(i.qty2) as iqty2,

from products as p,customer as c,original_invoice as o

left join invoice_data as i on 

  i.uid=o.uid and 

  i.pid=o.pid and 

  i.orderid=o.orderid

where 

  p.part=o.pid and

  c.id=o.uid and

  o.invoiceid=$invoiceid and

  o.uid=$uid 

group by 1,2,3,4,5,6,7,8,9,10,11

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:krv123
Comment Utility
I will try this out and get back to you. Thank you for all your help.
0
 
LVL 1

Author Comment

by:krv123
Comment Utility
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?
0
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
>> 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.
0
 
LVL 1

Author Comment

by:krv123
Comment Utility
>>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
0
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
Wouldn't that result in a negative number? Are you still using the group by query?

Please show your query.
0
 
LVL 1

Author Comment

by:krv123
Comment Utility
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[document]'
WHERE
p.part=h.pid AND
c.id=h.cid AND
h.invoiceid='$_GET[document]' AND
h.cid='$_GET[cid]'
GROUP BY 1,2,3,4,5,i.cqty,i.tqty
0
 
LVL 39

Assisted Solution

by:Roger Baklund
Roger Baklund earned 500 total points
Comment Utility
>> But do you understand what i trying to do with the subtraction?

Nope. :)

You can get the difference by adding these columns to the query:

sum(i.cqty) - h.cqty as china_diff,
sum(i.tqty) - h.tqty as taiwan_diff

But as I said, wouldn't that be negative numbers? I would think it should be the other way around:

h.cqty - sum(i.cqty) as china_diff,
h.tqty - sum(i.tqty) as taiwan_diff

You can not have i.cqty,i.tqty in the GROUP BY clause. You should have every column EXCEPT those columns that are used with aggregate functions (i.e. the sum() function) in the GROUP BY clause:

GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

The FROM clause is wrong, the last table mentioned is the table connected with the left join. It should be:

FROM customers as c,products as p,invoice_h as h

The ON clause for the LEFT JOIN seems wrong, it must contain all columns necessary to join this table to the last table in the list of table in the FROM clause (invoice_h). I don't know which columns you have in invoice_h, but at least "pid" should be specified.

LEFT JOIN invoice_data as i ON
  i.invoiceid=h.invoiceid AND
  i.pid=h.pid
0
 
LVL 1

Author Comment

by:krv123
Comment Utility
That did IT!!!!!!!!
Thank you so much for stickin with me!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now