How to find appropriate data from the first table and put it in the suitable position in the second table?

General PHP Help
#21

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…

1 Like
#22

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

#23

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…

1 Like
#24

Thanks a lot for your support. You are really unique and perfect.

#25

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!

1 Like
#26

Thank you very much.
I will test it as soon as possible and inform you.
Very best regards

#27

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

#28

Well, my friend, I attempted to make sense of this. But, I found the problem finally. Your tables are not logical. They do not mix together. I will attempt to explain. You have two separate worlds in your tables. The production side and the material side. You do not have any fields in your production side to match your material side. Therefore, you can’t match them up. You would need to add the material_name in your production tables to be able to mix them together. Even if you created a table result to pass to the second query, you can not match them up in any way. The two data query systems just do not match. The first query creates 5 rows of data and the second creates 8 rows. And, the data does not work together. In your production data table, you will need to put in some reference to the other tables.

Does this make sense? It’s like mixing apples with pears. You can slice them up any way you wish, but, they are not the same thing…

1 Like
#29

Thanks for your explanation. Yes, got it.
But in BOM table, we have materials name and products name.
In the second query, I want to know the consumption of materials based on their formulation. It can be provided by the second query.
But, user(production planner)need to have online inventory for each material($net) to order the materials which do not exist enough in warehouse.
For example, if we need to have 2000 kg milk based on the second query, but the inventory for milk is 1000 kg , the user should understand it very easily to order at least 1000 kg for supply chain.
Please assume that, we do not have the first query ($net). and we can get $net via web service by another application(Warehouse app).
In this situation, how to add $net so that each value must be set with material_name in the second table? In other words, how to put $net objects in front of material_name of the second table so that those must be matched.
Do you have any idea for this? I mean if $net comes by a web service from another app(we cannot reach database) is it possible to match $net objects with the second table?
I would be very appreciated if you could help me with this.
Thanks in advance.
very best regards.

#30

Been out of town on a fishing trip. I will look at this later tonight and see what I can fix up for you.
One possible was would be to create the first query as a view or as a temp table and then use it and
delete it after the second query. But, have to work for a couple hours or more and then will see what I come up with. Sorry for the delay…

1 Like
#31

Well, so you do not really want a query that creates the combined results. I was working on that and then read your last notes again. I think things are confused. What do you mean by a web service that is not able to get to the database. If you use a web service that can reach a website, they can get any data out of the database using a post.

Most all “web service” systems use XML. XML is just a way to format data and transfer it to another app, server, device or just about anything that works with computer or websites. So, you can create a query, take the results and create an XML file and use JSON to transfer it. So, i think I need you to explain the web service you are talking about. Is that a canned program? Do you have access to it?

You mentioned the production-planner keeps an online inventory. Therefore all of that data is 100% available for accesses. You just need to set up a simple PHP page to push data to any web app that needs the data. There are some security issues to include in the design.

If you have an online inventory, you can create a very small PHP script to post to. You do NOT need a webpage attached to PHP code, therefore, you can call it from anywhere and access databases on the server. Then, the web app can post a query to it and the PHP script can return the data.

Is that what you are looking for?

Someplace along this thread I thought you wanted to combine the two queries. That is what you asked for near the top. I was not clear that you have one on one system and another one elsewhere.

1 Like
#32

Dear Ernie, Thanks for getting back back to me.
Fortunately, now the problem has been solved. (I got the inventory by a query where material_name = material_name)
Again, thank you very much
And if you can find some free time, can you please visit this topic ?


Thanks in advance!

#33

Hee! Glad you solved it. I will look at the other topic. I think I was not clear on what you needed.
Probably a language issue… Always nice to solve a programming puzzle. Glad you did!

1 Like
#34

Thank you very much, You are amazing.
Yes, probably it is rooted in language issue.
To be honest, I am integrating my software with a BPMS solution, and this BPMS helps me to use low code. That is perfect.

Sponsor our Newsletter | Privacy Policy | Terms of Service