PHP SQL Multi Table在foreach中的foreach中分层foreach，仅从上面的1级引用

Question

I need to run below in two different modes hence the "if " ( second builds a large csv later on)

Below runs Ok for the single instance but falls over on load time for the second (*) as runs thru on each of the first 7k rows.

I would like to avoid the dreaded

 set_time_limit(0);

I am also picking bits off at each stage as these are used later as well.

So The question is what is the fastest way to achieve below ?

May be one SQL statement and pull of defined values as in

table_c.tree , $row['apple'] 

Table structure

Table A has 7k rows where $row['Code'] can be repeated may be 10 times

Table B has 10k rows where $row['Group'] can be repeated may be 5 times

Table c has 2k rows where $row['Group2'] can be repeated may be 5 times

 if (isset($_POST["go"])) { $where = 'WHERE xxxxx = 1';} else{$where = 'WHERE xxxxx = "'.$selected_val[1].'"';}

$stmt = $conn->prepare(" SELECT *  FROM table_A $where "); $stmt->execute();

    foreach ($stmt as $row) {
                $Code = $row['Code'];

                $stmt1 = $conn->prepare(" SELECT *  FROM table_B WHERE Code = '$Code' "); $stmt1->execute();

                    foreach ( $stmt1 as $row1 ) {
                        if ($row['apple']) {$apple = $row['apple'];} // used to stop running rest all the time and also set $ for use later
                        else {$group1 = $row1['Group'];

                                $stmt2 = $conn->prepare(" SELECT *  FROM table_c WHERE group2 = '$group1' "); $stmt2->execute();                            

                                foreach ( $stmt2 as $row2 ) {$group2 =  $row2['group2'];}
                                    $stmt3 = $conn->prepare(" SELECT *  FROM table_d WHERE group3 = '$group2' "); $stmt3->execute();                            
                                        foreach ( $stmt3 as $row3 ) {

                                            $result [] = $row3['result'] 
                                        }
                                }   
                    }


        /// do things with 
        $result
        $Code
        $group1
        $row2['group2']
        $row['apple']
    }   

The tables bar the first are not mine so I have no control over them.

An alternative may be to load some new fields into "Table_a" but that would be just splitting the time to other functions.

Following on from @yoshiwaan

Am I missing a speed trick here? if i create "my_result_chain" once (as all data can be dumped into at a good speed) at the beginning then just call in the foreach it runs very quick. BUT as there are some tables which do not include all references from the table one level up it returns null rather than the trail as far as possible. This is corrected by using LEFT JOIN but obviously creates multiple rows where filter key is the same etc is there a way to pick which row / field, DISTINCT Grabs only the top level , and there is not unique key to each table As I need to get various data from different tables ? for ref 13 sec v .03sec if created once. Sort of works if I add a key number to each row of the foreach but not a nice solution.

Answer 1

Short winded hint: don't select * if you don't need everything, just select the columns you want.

Long winded version: don't do this in your code, offload the logic into a view in your database instead.

Here's a big example for you (you'll need to scroll down to see it all):

create table table_A
(
    xxxxxx int,
    Code    int
);

create table table_B
(
    Code int,
    `Group` varchar(20),
    apple varchar(20)
);

create table table_C
(
    `Group` varchar(20),
    group2  varchar(20)
);

create table table_D
(
    group3  varchar(20),
    result  varchar(20)
);

insert into table_A
values (1, 1);
insert into table_A
values (2, 2);
insert into table_A
values (3, 3);
insert into table_A
values (4, 4);

insert into table_B
values (1, 'snake', 'yes');
insert into table_B
values (1, 'lizard', '');
insert into table_B
values (2, 'canine', 'yes');
insert into table_B
values (2, 'feline', '');
insert into table_B
values (3, 'smallbird', 'yes');
insert into table_B
values (3, 'bigbird', '');
insert into table_B
values (4, 'bigfish', '');
insert into table_B
values (4, 'smallfish', '');


insert into table_C
values ('snake', 'python');
insert into table_C
values ('lizard', 'adder');
insert into table_C
values ('canine', 'corgi');
insert into table_C
values ('feline', 'lion');
insert into table_C
values ('ursine', 'grizzly');
insert into table_C
values ('smallbird', 'swallow');
insert into table_C
values ('bigbird', 'goose');
insert into table_C
values ('bigfish', 'baraccuda');
insert into table_C
values ('smallfish', 'minnow');
insert into table_C
values ('smallfish', 'herring');

insert into table_D
values ('python', 'big');
insert into table_D
values ('adder', 'scary');
insert into table_D
values ('asp', 'scary');
insert into table_D
values ('corgi', 'funny');
insert into table_D
values ('doberman', 'funny');
insert into table_D
values ('lion', 'evil');
insert into table_D
values ('tabby', 'evil');
insert into table_D
values ('swallow', 'spit');
insert into table_D
values ('goose', 'edible');
insert into table_D
values ('herring', 'red');
insert into table_D
values ('pike', 'weapon');

create view my_result_chain
as
    select a.xxxxxx as filter, a.Code as Code, b.Group as Group1, c.group2 as Group2, d.result as Result
    from
        table_A a
    join (table_B b)
        on (b.Code=a.Code)
    join (table_C c)
        on (c.group=b.group)
    join (table_D d)
        on (d.group3=c.group2)
    where
        b.apple = '';

select * from my_result_chain;

select * from my_result_chain
where filter = 1;

drop view my_result_chain;
drop table table_A;
drop table table_B;
drop table table_C;
drop table table_D;

Now in your code just select using there filter value that you want and you'll get all the results in the results column.