CODEWITHSUNDEEP
phpmysqlarrays
B. Christophe
B. Christophe

Reputation: 89

MySQL : one query for join multiple tables and store result in 3arrays

I'm trying to write one query for joining 3 tables without link, I was trying with UNION SQL command like this:

SELECT *
FROM 
    (SELECT 
         a.id AS field_aa, a.column2 AS field_ab, a.column3 AS field_ac
     FROM tableA a
     UNION
     SELECT 
         b.id AS field_ba, b.column2 AS field_bb, b.column3 AS field_bc
     FROM tableB b
     UNION
     SELECT 
         c.id AS field_ca, c.column2 AS field_cb, c.column3 AS field_cc
     FROM tableC c) abc
WHERE 1;

And after i want to fill 3 arrays for each table

while( ($arr = $_opDB->fetch_assoc($result)) != FALSE ) {
        $array_one = array(
            'field_id' => $arr['field_aa'],
            'field_one' => $arr['field_ab'],
            'field_two' => $arr['field_ac'],
        ) ;
        $array_two = array(
            'field_id' => $arr['field_ba'],
            'field_one' => $arr['field_bb'],
            'field_two' => $arr['field_bc'],
        ) ;
        $row_three = array(
            ...
        ) ;
        $global_array['firstSelect'][] = $array_one ;
        $global_array['secondSelect'][] = $array_two ;
        $global_array['thirdSelect'][] = $array_three ;
    }

All my entries are added in $global_array['firstSelect'], others are empty

Upvotes: 0

Views: 626

Answers (1)

KIKO Software
KIKO Software

Reputation: 16741

You could add a field indicating from which table a row is coming:

   SELECT a.id AS field_id, 
          a.column2 AS field_2, 
          a.column3 AS field_3,
          'firstSelect' as select_name
   FROM tableA a
     UNION
   SELECT b.id AS field_id, 
          b.column2 AS field_2, 
          b.column3 AS field_3,
          'secondSelect' as select_name
   FROM tableB b
     UNION
   SELECT c.id AS field_id, 
          c.column2 AS field_2, 
          c.column3 AS field_3,    
          'thirdSelect' as select_name
   FROM tableC c

And here is the PHP to process the result:

while(($row = $_opDB->fetch_assoc($result)) {
    $select = $row['select_name'];
    $global_array[$select][] = ['field_id'  => $row['field_id'],
                                'field_one' => $row['field_2'],
                                'field_two' => $row['field_3']];
}

As CBroe rightly commented: If the tables are indeed identical you should make them into one table.

Upvotes: 1

Related Questions