Create extra combined html table from sql database with php

Question

Before marking this post double, read carefully. I have a MySQL base with 3 tables, T1, T2 and T3. Their data are these:

T1:

name  val1  val2    
John    1    2    
Mary    1    2

T2:

name  val3  val4    
John    3    4    
Mark    1    2

T3:

name  val5  val6    
John    5    6    
Mark    3    4    
Mary    4    5

and I want to create the following combined html table:

name  val1  val2  val3  val4  val5  val6    
John   1      2    3      4    5      6    
Mary   1      2    -      -    4      5    
Mark   -      -    1      2    3      4

It seems very complicated to me. Any ideas how to do it?

Arnaud Peralta · Accepted Answer · 2019-03-10 16:20:34Z

1

SELECT TX.NAME,
(SELECT val1 FROM T1
    WHERE T1.NAME = TX.NAME) AS V1,
(SELECT val2 FROM T1
    WHERE T1.NAME = TX.NAME) AS V2,
(SELECT val3 FROM T2
    WHERE T2.NAME = TX.NAME) AS V3,
(SELECT val4 FROM T2
    WHERE T2.NAME = TX.NAME) AS V4,
(SELECT val5 FROM T3
    WHERE T3.NAME = TX.NAME) AS V5,
(SELECT val6 FROM T3
    WHERE T3.NAME = TX.NAME) AS V6
FROM (
SELECT NAME FROM T1
UNION
SELECT NAME FROM T2
UNION
SELECT NAME FROM T3) AS TX

We can do it with one sub-query per different column.

edited Mar 10, 2019 at 16:20

answered Mar 9, 2019 at 18:50

Arnaud Peralta

1,3051 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Stergiou Over a year ago

By executing your command I get: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OUTER JOIN T2 ON T1.name = T2.name ' at line 3 "

Arnaud Peralta Over a year ago

There is no full outer join in mariadb, i will change my answer.

John Stergiou Over a year ago

The new command returns: John | 1 | 2 | | | | Mary | 1 | 2 | | | | John | 3 | 4 | | | | Mark | 1 | 2 | | | | John | 5 | 6 | | | | Mark | 3 | 4 | | | | Mary | 4 | 5 | | | | there sould be 3 lines, not 7.

John Stergiou Over a year ago

Thanks a lot for your answer and for your time! I do appreciate your help!

DinoCoderSaurus · Accepted Answer · 2019-03-09 18:46:49Z

0

It's not that complicated.

If MySQL is your stronger language, write a query that joins the three tables with the desired values in the desired columns (into an array).

If PHP is your stronger language then write three queries that selects the values from each table (into distinct arrays). Then "join" those arrays into one array with the desired columns.

You want the result, in either case, to be an array with one "key" per user that has the 6 values.

You can iterate that result into an html table straightaway.

answered Mar 9, 2019 at 18:46

DinoCoderSaurus

6,5502 gold badges13 silver badges17 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2019-03-10 13:46:27Z

0

Use union all and group by:

select name, sum(val1) as val1, sum(val2) as val2,
       sum(val3) as val3, sum(val4) as val4,
       sum(val5) as val5, sum(val6) as val6
from ((select name, val1, val2, null as val3, null as val4, null as val5, null as val6 from t1
      ) union all
      (select name, null, null, val1, val2, null, null from t2
      )
      (select name, null, null, null, null val1, val2 from t3
      )
     ) t
group by name;

Or, if you know that t3 has all the rows you want, use left join:

select t3.name, t1.val1, t1.val2,
       t2.val1 as val3, t2.val2 as val4,
       t2.val1 as val5, t2.val2 as val6
from t3 left join
     t1
     on t3.name = t1.name left join
     t2
     on t3.name = t2.name;

I don't recommend full join. It is a bit tricky to create the correct query -- and easy to make a mistake that can filter out rows.

edited Mar 10, 2019 at 13:46

answered Mar 9, 2019 at 19:19

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

3 Comments

John Stergiou Over a year ago

If I use all these 6 lines in the grey box and below it, then I get the error: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'coalesce(sum(val4), 0) as val6 from ((select name, val1, val2, null as val3' at line 5 ".

Gordon Linoff Over a year ago

@JohnStergiou . . . There was an typo (an extraneous line of code) that should not have been in the answer.

Gordon Linoff Over a year ago

@JohnStergiou . . . There is no filtering in either query, so I don't see how non-empty tables could not return results . Are you getting an error with the query.

Collectives™ on Stack Overflow

Create extra combined html table from sql database with php

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related