Retrive two table data from database and display and Insert into a new table using php

Question

I have two table named input and out_materials. These are the fields and data of both table

1st table : input

Materials_code | Materials_name | unit | in_qty

m219 , mat1 , pcs , 215

2nd table : out_materials

Materials_code | Materials_name | unit | out_qty

m219 , mat1 , pcs , 15

3rd table : total

Materials_code | Materials_name | unit | in_qty| out_qty | Qty

m219 , mat1 , pcs , 215 ,15 ,200

Note: There i try to fetch data from 1st table and 2nd table and this fetch data insert into 3rd table.My code is given below but it dosen`t work.

$res=mysqli_query($con, "SELECT i.* , o.* FROM input i,out_materials o WHERE i.Materials_name=o.Materials_name");

                                    while($row=mysqli_fetch_array($res)){

                                     $inm_code = $row['Materials_code'];
                                     $inm_name = $row['Materials_name'];
                                     $inunit = $row['Unit'];
                                     $inqty = $row['Input_qty'];
                                     $outm_code = $row['Materials_code'];
                                     $outm_name = $row['Materials_name'];
                                     $outunit = $row['Unit'];
                                     $outqty = $row['Out_qty'];}

                                $sql2=mysqli_query($con, "INSERT INTO total (Materials_code,Materials_name,Unit,Input_qty,Out_qty) VALUES('$inm_code','$inm_name','$inunit','$inqty','$outqty')");

Welcome to Stack Overflow. Please [edit] your question to include a [mcve], which can be tested by others. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query for SQL related questions. Also check https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php on how to use prepared statements. And after that explain in detail what "doesn't work". Also check https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-1-to-be-mysqli-result-or-how-to-get-mysql for MySQL errors. — Progman, May 19 '19 at 09:21
what do you mean it doesn't work.. have you tried seeing whether you are getting data `[$res]` from database before inserting? — danish-khan-I, May 19 '19 at 10:03
@danish-khan-I it doesn't work means when i run this code in my new table insert only 1 row data. in my database have more data which are not inserted.only one row data insert which is present in last. — Fazlul Hoque Sawrav, May 20 '19 at 04:17
That's the symptom...but you need to dig deeper to find the cause. Learn how to debug your code — ADyson, May 20 '19 at 05:31

score 1 · Accepted Answer · answered May 20 '19 at 04:18

$res=mysqli_query($con, "SELECT i.* , o.* FROM input i,out_materials o WHERE i.Materials_name=o.Materials_name");

                                    while($row=mysqli_fetch_array($res)){

                                     $inm_code = $row['Materials_code'];
                                     $inm_name = $row['Materials_name'];
                                     $inunit = $row['Unit'];
                                     $inqty = $row['Input_qty'];
                                     $outm_code = $row['Materials_code'];
                                     $outm_name = $row['Materials_name'];
                                     $outunit = $row['Unit'];
                                     $outqty = $row['Out_qty'];

                                $sql2=mysqli_query($con, "INSERT INTO total (Materials_code,Materials_name,Unit,Input_qty,Out_qty) VALUES('$inm_code','$inm_name','$inunit','$inqty','$outqty')");
} //notice

your $sql2 was outside the while loop that is why its only inserting the last row.

thank you @danish-khan-I. I'm new in php that's why i didn't understand. Thank you again — Fazlul Hoque Sawrav, May 20 '19 at 05:31

Retrive two table data from database and display and Insert into a new table using php

1 Answers1