How can we associate these two data tables? please help me. The first table is a department table, as shown in.
Field name field type
ID bigint
Department name dpName varchar (20)
DpUpID varchar (20)
The second table is the product warehouse receiving table as follows:
Field name field type
ID bigint
Warehouse receiving Department pai_depment varchar (20)
Warehouse receiving time pai_time varchar (20)
Device type pai_type varchar (20)
Device parameter pai_confg text
Purchase quantity pai_numb varchar (20)
Purchase Unit price pai_price varchar (20)
Whether to pass into_check varchar (10)
Warehouse receiving clerk _name varchar (20)
Remarks pai_noto varchar (100)
Now we need to count the products of level 2 departments under a certain department. if so, how can we write SQL statements and use PHP to implement them ..
Reply to discussion (solution)
Department table
Product warehouse receiving table
I wrote this first.
Include ("../conn. php ");
$ Query_1 = "select * from tbl_department where dpUpID = '0 '";
$ Query_info_1 = mysql_query ($ query_1 );
While ($ row_1 = mysql_fetch_array ($ query_info_1 )){
$ Row_1 [dpUpID];
$ Tmp_1 [] = $ row_1 [dpName]; // level 1
Echo $ row_1 [dpName]."
";
Print_r ($ tmp_1 );
Echo"
";
$ Query_2 = "select * from tbl_department where dpUpID = '$ row_1 [ID]'";
$ Query_info_2 = mysql_query ($ query_2 );
While ($ row_2 = mysql_fetch_array ($ query_info_2 )){
$ Tmp_2 [] = $ row_2 [dpName]; // 2
$ Tmp_2ID [] = $ row_2 [ID];
$ Pai_dpid = implode (",", $ tmp_2ID );
Echo "level 2". $ row_2 [dpName]."
";
Print_r ($ tmp_2 );
Echo"
";
$ Query_3 = "select * from tbl_department where dpUpID = '$ row_2 [ID]'";
$ Query_info_3 = mysql_query ($ query_3 );
While ($ row_3 = mysql_fetch_array ($ query_info_3 )){
$ Tmp_3 [] = $ row_3 [dpName]; // 3
Echo "level 3". $ row_3 [dpName]."
";
Print_r ($ tmp_3 );
Echo"
";
$ Query_4 = "select * from tbl_department where dpUpID = '$ row_3 [ID]'";
$ Query_info_4 = mysql_query ($ query_4 );
While ($ row_4 = mysql_fetch_array ($ query_info_4 )){
$ Tmp_4 [] = $ row_4 [dpName]; // 4
Echo "level 4". $ row_4 [dpName]."
";
Print_r ($ tmp_4 );
Echo"
";
}
}
}
}
The running result is:
Runan building
Array ([0] => Runan Building)
Level 2 Information Department
Array ([0] => Information Department)
Level 3 ssss
Array ([0] => ssss)
Level 4 hhh
Array ([0] => hhh)
Level 2 Finance Department
Array ([0] => Information Department [1] => Finance Department)
Level 2 administrative personnel department
Array ([0] => Information Department [1] => Finance Department [2] => Administrative Personnel Department)
Level 2 R & D department
Array ([0] => Information Department [1] => Finance Department [2] => Administrative Personnel Department [3] => R & D department)
Gemeng Tower
Array ([0] => Runan building [1] => Jinmeng Building)
Level 2 Finance Department
Array ([0] => Information Department [1] => Finance Department [2] => Administrative Personnel Department [3] => R & D Department [4] => Finance Department)
Level 2 Information Department
Array ([0] => Information Department [1] => Finance Department [2] => Administrative Personnel Department [3] => R & D Department [4] => Finance Department [5] => Information Department)
Hengfeng building
Array ([0] => Runan building [1] => Jinmeng building [2] => Hengfeng Building)
The results of the existing departments are as follows:
I need to make statistics
Number of Products under all departments of XXX building
The pai_dpid field in the product warehouse receiving table is the same as the ID field in the department table ....
Select * from department table a inner join product warehouse receiving Table B on a. ID = B. pai_depment where a. dpUpID = 1
Select * from department table a inner join product warehouse receiving Table B on a. ID = B. pai_depment where a. dpUpID = 1
Error message,
$ Query_count = "select * from tbl_department a inner join tbl_into B on a. ID = B. pai_dpid where a. dpUpID = 0"
$ SQL _count = mysql_query ($ query_count );
$ Result = mysql_result ($ SQL _count, 0 );
Echo $ result;
Is this wrong ???
What's wrong? Post error message