Dear Ernie,
Again thank you for your support.
I’m nearly sure, I did not manage to explain whatever I wanted. Hence, I have to draw my requirement from the first(maybe you have a better solution)
There are two different queris(you can see them in the first post of the topic).
First query returns remainder of materials. (This is for warehouse)
Second query returns the consumption of materials based on their recipe(BOM).
So,I want to compare consumption material(second query) with remainder($net) from the first query.
For instance, now there are 2000kg sugar in warehouse. (This number is reported by the first query). And we need to produce 1500 kg sugar. (This(1500) is reported by the second query).
But, user must compare Inventory(2000) with sugar requirement(1500).
I want to that 2000 ( first query) is put in the second table and in front of the same material(sugar).
And the point is that 2 queries are right. But I dont prefer to use left join technic. Because probably I will get the $net from other application via API.
As a result, I can guess I need to get value from the first Query in an array. But I dont know how to do it.
I was wondering if you could support me with this.
Very best regards,
Thank you for the description. I understand better now. I have to leave for a few hours soon.
When I get back I will create a new query for you. Just short on time, lots of work and have to leave
for at least 6 hours… Talk to you later! Sorry for the delay, just very busy today! …
I finally got some free time and was looking at this last query problem. I noticed it was throwing out an error because of a table named “production_plan”. I have tables bom and production_data, but, no production_plan. Was that supposed to be _data or if there is a third table, let me know.
If I change it to _data, then it runs and gives me results.
Dear Ernie,
Thank you very much for your kind reply,
Well, production_plan is different from production_data. However, there is no error in my both queries.
As I said, I can guess I need to get value($net) from the first Query in an array and put it in the second table based on the material_name. I don’t know how to do it?
If you want to have the production_plan, I can send it here.
Best Regards
Well, I can not run queries against it without some of the data. I would not need the entire table.
If you want to private-message it to me, I will add it to the database and do some testing for you.
Also, my private messages do not keep the ones I sent to you. Can you post the fixed first query
from early on where I combined the code all into the query? That would help me too. Thanks.
Thank you very much for your consideration.
I think I lost one of your previous message about my query problem.
Anyway, would you please read again the 7th reply by me. (explanation about the whole story of my goal).
To be honest the first query is not important for me.Even it is better not to show for clients. But, I do not know how to get ($net).
My issue is this. " I want to have update inventory($net) in order to compare it with its consumption formulation ".
The second query returns materials consumption formulations.but, it can not return the update inventory($net). So, I think I can get that by the first query.
The problem is that, I don’t know how to populate $net objects so that its objects find corresponding materials. For example, the value for milk should be put in front of milk. the salt should be put in front of salt.
As I said, I prefer not to use join Technic. because, maybe I will get the $net via rest API.
And, my data is fake and they are just for testing.
Here is the code:
REATE TABLE production_plan (
id int(11) NOT NULL,
product_code text NOT NULL,
product_name text NOT NULL,
product_entrance text NOT NULL,
unit_entrance text NOT NULL,
product_quantity text NOT NULL,
unit text NOT NULL,
others text NOT NULL,
product_line text NOT NULL,
week_day text NOT NULL,
pdate text NOT NULL,
shift text NOT NULL,
start_time text NOT NULL,
finish_time text NOT NULL,
duration text NOT NULL,
description text NOT NULL,
username text NOT NULL,
first_date text NOT NULL,
editor text NOT NULL,
edit_date text NOT NULL,
bom_name text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
–
– Dumping data for table production_plan
INSERT INTO production_plan (id, product_code, product_name, product_entrance, unit_entrance, product_quantity, unit, others, product_line, week_day, pdate, shift, start_time, finish_time, duration, description, username, first_date, editor, edit_date, bom_name) VALUES
(1, ‘533’, ‘milk’, ‘150’, ‘ton’, ‘150000’, ‘bottle’, ‘-’, ‘sidel’, ‘’, ‘2020/3/21’, ‘2’, ‘16:30’, ‘22:30’, ‘06:00:00’, ‘’, ‘’, ‘’, ‘’, ‘2020/07/03’, ‘1’),
(2, ‘251’, ‘doogh’, ‘150’, ‘ton’, ‘85000’, ‘bottle’, ‘-’, ‘serak’, ‘’, ‘2020/3/21’, ‘2’, ‘17:30’, ‘22:30’, ‘05:00:00’, ‘’, ‘’, ‘’, ‘’, ‘2020/07/05’, ‘default’),
(3, ‘251’, ‘doogh’, ‘’, ‘’, ‘136000’, ‘bottle’, ‘-’, ‘serak’, ‘’, ‘2020/3/21’, ‘3’, ‘22:30’, ‘06:30’, ‘08:00:00’, ‘’, ‘’, ‘’, ‘’, ‘2020/07/05’, ‘default’),
(4, ‘582’, ‘milk’, ‘’, ‘’, ‘144000’, ‘bottle’, ‘-’, ‘sidel’, ‘’, ‘2020/3/22’, ‘1’, ‘06:30’, ‘12:30’, ‘06:00:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),
(5, ‘581’, ‘milk’, ‘’, ‘’, ‘144000’, ‘bottle’, ‘-’, ‘sidel’, ‘’, ‘2020/3/22’, ‘2’, ‘14:30’, ‘20:30’, ‘06:00:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),
(6, ‘441’, ‘soft-drink’, ‘’, ‘’, ‘88000’, ‘bottle’, ‘-’, ‘N2’, ‘’, ‘2020/3/22’, ‘2’, ‘18:30’, ‘22:30’, ‘04:00:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),
(7, ‘441’, ‘soft-drink’, ‘’, ‘’, ‘176000’, ‘bottle’, ‘-’, ‘N2’, ‘’, ‘2020/3/22’, ‘3’, ‘22:30’, ‘06:30’, ‘08:00:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),
(8, ‘254’, ‘doogh’, ‘’, ‘’, ‘68000’, ‘bottle’, ‘-’, ‘serak’, ‘’, ‘2020/3/22’, ‘3’, ‘22:30’, ‘02:30’, ‘04:00:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),
(9, ‘252’, ‘doogh’, ‘’, ‘’, ‘68000’, ‘bottle’, ‘-’, ‘serak’, ‘’, ‘2020/3/22’, ‘3’, ‘02:30’, ‘06:30’, ‘04:00:00’, ‘’, ‘’, ‘’, ‘’, ‘2020/07/05’, ‘default’),
(10, ‘251’, ‘doogh’, ‘’, ‘’, ‘136000’, ‘bottle’, ‘-’, ‘serak’, ‘’, ‘2020/3/22’, ‘1’, ‘06:30’, ‘14:30’, ‘08:00:00’, ‘’, ‘’, ‘’, ‘’, ‘2020/07/05’, ‘default’),
(11, ‘251’, ‘doogh’, ‘’, ‘’, ‘51000’, ‘bottle’, ‘-’, ‘serak’, ‘’, ‘2020/3/22’, ‘2’, ‘14:03’, ‘17:30’, ‘03:27:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),
(12, ‘123’, ‘soft-drink’, ‘’, ‘’, ‘60000’, ‘bottle’, ‘-’, ‘Cheshme’, ‘’, ‘2020/3/22’, ‘2’, ‘16:30’, ‘22:30’, ‘06:00:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),
(13, ‘123’, ‘soft-drink’, ‘’, ‘’, ‘60000’, ‘bottle’, ‘-’, ‘Cheshme’, ‘’, ‘2020/3/22’, ‘3’, ‘22:30’, ‘04:30’, ‘06:00:00’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’),
(14, ‘-’, ‘-’, ‘’, ‘’, ‘-’, ‘-’, ‘CIP’, ‘sidel’, ‘’, ‘2020/5/23’, ‘1’, ‘14:30’, ‘18:30’, ‘04:00:00’, ‘’, ‘’, ‘2020/06/17’, ‘’, ‘’, ‘’),
(15, ‘-’, ‘-’, ‘’, ‘’, ‘-’, ‘-’, ‘CIP’, ‘sidel’, ‘’, ‘2020/5/23’, ‘1’, ‘14:30’, ‘18:30’, ‘04:00:00’, ‘’, ‘’, ‘2020/06/17’, ‘’, ‘2020/06/17’, ‘’),
(16, ‘101’, ‘soft-drink’, ‘’, ‘’, ‘31000’, ‘bottle’, ‘-’, ‘N1’, ‘’, ‘2020/7/4’, ‘1’, ‘06:30’, ‘18:30’, ‘12:00:00’, ‘’, ‘’, ‘2020/07/03’, ‘’, ‘’, ‘1’),
(17, ‘121’, ‘soft-drink’, ‘’, ‘’, ‘200000’, ‘bottle’, ‘-’, ‘Cheshme’, ‘’, ‘2020/7/25’, ‘1’, ‘07:30’, ‘14:30’, ‘07:00:00’, ‘’, ‘’, ‘2020/07/25’, ‘’, ‘’, ‘’);
Very best regards.
Well, with that data in place, I ran your last posted query now and it works. I get this as a result:
| FINAL | MATERIAL_NAME | UNIT | MATERIAL_TYPE | |
|---|---|---|---|---|
| 1785000 | hashtgiah | gram | consumable | |
| 13940 | label | gram | packaging | |
| 591150000 | milk | gram | consumable | |
| 26775000 | nana | gram | consumable | |
| 425000 | preform | unit | packaging | |
| 150000 | priform | unit | packaging | |
| 26775000 | salt | gram | consumable | |
| 76500000 | water | gram | consumable |
Now, I see you used some sort of Global’s in your code for $db and $row1. Can you explain these to me?
Is this code because you have more than one function and need to use the data inside a function?
I am unclear why you need to use globals at all.
Thank you,
World should be happy because of you. You present your valuable time for developing knowledge. I really adore you.
yes. Global is used for that reason.
db is the same my connection to database which exists in another php.file.
and in this table(you posted here), I want to have $net i in front of material_name at the new column (Inventory). It should be corresponding with material_name.
How can I do that?
Kind Regards
Well, I would put it right into the query. Since you did it in an earlier query, you can just add that too.
I will play with it and post a solution for you.
And, you are very welcome. I like to help people. I am not the best at queries and join’s, but, think this is
a simple one. More in a bit…
I went back to your first query. You gave me the following tables:
bom
production_data
production_plan
stock_information
But, in the first query it uses a table called material. I can’t run a query against that as I do not have it.
Didn’t I send you an updated version of the first two, I think during a private message? I do not have those. So, either send me the materials table here or in a private message or I can just guess how it
might work.
Dear Enrie,
thanks again
here is the material table code:
CREATE TABLE materials (
id int(11) NOT NULL,
material_code text NOT NULL,
material_name text NOT NULL,
material_group text NOT NULL,
material_packing text NOT NULL,
order_point text NOT NULL,
beginning_inventory text NOT NULL,
company text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
–
– Dumping data for table materials
–
INSERT INTO materials (id, material_code, material_name, material_group, material_packing, order_point, beginning_inventory, company) VALUES
(3, ‘1’, ‘milk’, ‘consumable’, ‘kg’, ‘1000’, ‘10’, ‘’),
(4, ‘2’, ‘sugar’, ‘consumable’, ‘kg’, ‘5’, ‘220’, ‘’),
(5, ‘3’, ‘nana’, ‘consumable’, ‘kg’, ‘200’, ‘850’, ‘’),
(6, ‘10’, ‘label’, ‘packing’, ‘unit’, ‘1000’, ‘10’, ‘’),
(7, ‘4’, ‘hashtgiah’, ‘consumable’, ‘kg’, ‘100’, ‘350’, ‘’),
(8, ‘5’, ‘salt’, ‘consumable’, ‘kg’, ‘3000’, ‘5000’, ‘’);
Regards
Thanks. I will get a new query figured out for you this morning…
Well, my morning and most of my afternoon got very busy. Had some time now for you.
I took your first query and altered it to create the NET amount you required. I had to do some odd
select code in it to make it work. Here is the query:
SELECT M.BEGINNING_INVENTORY, S.MATERIAL_CODE, S.MATERIAL_NAME, SUM(S.IMPORTATION_QUANTITY) AS SUM_IMPORTATION, SUM(S.EXPORTATION_QUANTITY) AS SUM_EXPORTATION, (M.BEGINNING_INVENTORY + SUM(S.IMPORTATION_QUANTITY) - SUM(S.EXPORTATION_QUANTITY)) AS NET FROM STOCK_INFORMATION AS S LEFT JOIN MATERIALS AS M ON M.MATERIAL_CODE = S.MATERIAL_CODE WHERE S.MATERIAL_NAME != '' GROUP BY M.MATERIAL_NAME
As you see, I removed the unneeded AS and added in the calc for the NET total. The results was this:
| BEGINNING_INVENTORY | MATERIAL_CODE | MATERIAL_NAME | SUM_IMPORTATION | SUM_EXPORTATION | NET | |
|---|---|---|---|---|---|---|
| 350 | 4 | hashtgiah | 0 | 260.00 | 90 | |
| 10 | 1 | milk | 1025 | 870.00 | 165 | |
| 850 | 3 | nana | 450 | 650.00 | 650 | |
| 5000 | 5 | salt | 0 | 200.00 | 4800 | |
| 220 | 2 | sugar | 5700 | 3200.00 | 2720 |
I think that is what you wanted… Next, I took the second query and attempted to merge it with the first.
From your posts, you just want to add in the NET from the first query into this second query. Correct?
I basically, just added in only the needed parts of the first query to create the NET again. You version was
messy. I cleaned it up. Here is the number 2 query cleaned up without the NET in it.
SELECT SUM(RESULT) AS FINAL, MATERIAL_NAME, UNIT, MATERIAL_TYPE FROM( SELECT P.PRODUCT_NAME, SUM((B.ACTUAL_QUANTITY * P.PRODUCT_QUANTITY)) AS RESULT, B.MATERIAL_NAME, B.ACTUAL_QUANTITY, B.PRODUCT_CODE, B.UNIT, B.MATERIAL_TYPE, B.BOM_NAME FROM BOM AS B LEFT JOIN PRODUCTION_PLAN AS P ON P.PRODUCT_CODE = B.PRODUCT_CODE AND P.BOM_NAME = B.BOM_NAME WHERE MATERIAL_NAME !='' GROUP BY MATERIAL_NAME, PRODUCT_CODE ) AS RESULT GROUP BY MATERIAL_NAME
Now, here is the same version with the NET added in. I just added another JOIN with the previous query
in place. Here it is:
Well, it seems I am not that good with queries… I was able to get the query to see ALL of the data, but,
the JOIN’s were not working as they should. Either they pulled in all the data and did not sum it correct
or they got the wrong group-by lists. I have had a lot of work coming in. But, will have time this weekend
to try it again for you. Sorry for the delays…
Thank you very much for your support. You are a very good person in both moral and knowledge aspects. Thanks
Dear Ernie, I think in this case working with queries and join is difficult.
Is it possible to get values of $net in an array with php code and call those in the second table?
Thanks in advance
I am sorry, I have been out of town. I should have let you know. I have time today and tomorrow to solve this for you. I will write again shortly. Thanks for your patience…
Thanks a lot for your support. You are really unique and perfect.
After doing further research for you, I found this to be complicated, but, also in some ways very easy.
I started with your first query from the original post. One by one, I added in the other joins. Then, I was
able to trim down all of the extra code you were using. This extra code was confusing me and I started
again from scratch and it appears to work now. Please manually verify the resulting numbers and let me
know it is working correctly. Hope it is now!
SELECT M.beginning_inventory, S.material_code, S.material_name, SUM(S.importation_quantity) AS sum_importation, SUM(S.exportation_quantity) AS sum_exportation, SUM(B.actual_quantity * P.product_quantity) AS result, (M.beginning_inventory + SUM(S.importation_quantity) - SUM(S.exportation_quantity)) AS net FROM stock_information AS S LEFT JOIN materials AS M ON M.material_code = S.material_code LEFT JOIN bom AS B ON S.material_name = B.material_name LEFT JOIN production_plan AS P ON P.product_code = B.product_code AND P.bom_name = B.bom_name WHERE S.material_name != '' GROUP BY M.material_name
And this results is given:
| beginning_inventory | material_code | material_name | sum_importation | sum_exportation | result | net | |
|---|---|---|---|---|---|---|---|
| 350 | 4 | hashtgiah | 0 | 260.00 | 3570000 | 90 | |
| 10 | 1 | milk | 6150 | 5220.00 | 5911500000 | 940 | |
| 850 | 3 | nana | 1800 | 2600.00 | 107100000 | 50 | |
| 5000 | 5 | salt | 0 | 600.00 | 26775000 | 4400 | |
| 220 | 2 | sugar | 5700 | 3200.00 | NULL | 2720 |
Sorry it took so long for me to get enough free time to work on it. Hope this solves it for you!
Thank you very much.
I will test it as soon as possible and inform you.
Very best regards
Dear Ernie, thanks for taking the time
To be honest your query returns wrong values in some cases. For example, sum_importation for nana is not 1800. It must be 450. Hence, $net is wrong, too.
Also, result is false for materials.
Another problem is that,in this way we lose some of materials of table 2.
As I said, I think the only solution is that, we get $net from the first query and then save it as an array. Then call $net in the second table by a loop or something like that.
I’m sorry for taking your time.
Kind Regards