How to populate UNION ALL query data in html table

Question

how can I populate data in html table with both side header fetched with PHP and MYSQL.I am not getting the output display, As I want to get, like in the desired output image.

$query = $db->prepare("SELECT player, score FROM cricket_table WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
UNION ALL
SELECT player, score FROM football_table WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'");
$query->execute; 
echo '<table><tr><th>Player Name</th><th>Cricket</th><th>Football</th></tr>';
while($row = $query->fetch(PDO:: FETCH_ASSOC) ) {
echo '<tr>
        <td>'.$row['player'].'</td>
        <td>'.$row['score'].'</td>
     </tr>';
}
echo '</table>';

This is the desired output

This is the current output

Aside from your supplied code being invalid, what problems are you having? — miken32, Mar 02 '20 at 20:06
I am not getting the output display, As I want to get, like in the desired output image above. — Eniol, Mar 03 '20 at 02:42
If you have same columns in different tables, why don't use just one table and flag? for example in WordPress they have wp_posts table and post_type where is stored the type of post (eg.: products, attachment, posts, etc.) you can do same where in your flag (eg.: sport_type) you can store the values like cricket, football etc. then you can use LEFT JOIN — Gabriele Carbonai, Mar 03 '20 at 11:55
UNION ALL is not what you are looking for because return the result like you are quering just one table. You have to use JOIN with foreign key and that can be player_id: SELECT a.*, c.*, f.* FROM players AS a LEFT JOIN cricket_table AS c ON a.player_id = c.player_id LEFT JOIN football_table AS f ON a.player_id = f.player_id — Gabriele Carbonai, Mar 03 '20 at 12:43
Please provide real sample data, or make http://sqlfiddle.com/ — user3783243, Mar 03 '20 at 12:43
@GabrieleCarbonai I tried this method. it returns empty result, and by some date ranges it only returns 0s for every table. And by submitting 4-5 times mysql server stops working. and by ending mysql process giving this error Warning: Uncaught PDOException: PDO::__construct(): MySQL server has gone away in — Eniol, Mar 03 '20 at 13:16
it is return empty because you query is wrong. Do you have foreign key? can you share your query? don not use php for querying, but something like phpmyadmin — Gabriele Carbonai, Mar 03 '20 at 13:26
@Eniol `This is a private paste. If you created this paste, please login to view it.` Please put data/code in the question. — user3783243, Mar 03 '20 at 17:04
Could you do the following: run `SHOW CREATE TABLE opd; SELECT * FROM opd LIMIT 10; SHOW CREATE TABLE xray; SELECT * FROM xray LIMIT 10;` and then [edit your question](https://stackoverflow.com/posts/60473673/edit) to include the results you get from all 4 queries? Thanks — FanoFN, Mar 04 '20 at 07:58
I don't see any reason at all to be calling `UNION`. Please provide dbfiddle demo with a sufficient number of rows to replicate your scenario. @eniol — mickmackusa, Mar 08 '20 at 07:32
You should have a separate table containing `playerId`, `playerName` and any other player related meta data. The cricket table and the football table should not contain names; they should only reference `playerId`s. Once the database is properly structured, you SELECT some data FROM `players` LEFT JOIN `cricket` USING `playerId` LEFT JOIN `football` USING `playerId`. This way you can easily show players with 0 values in both the `runs` column and the `goals` column. — mickmackusa, Mar 08 '20 at 08:30

score 2 · Accepted Answer · answered Mar 04 '20 at 16:14

You can use UNION ALL and sum the values you need by each column:

This is your SQL. Note it will run both queries with union all and then sum each value. We will use the "0" (zero) trick to ignore the "wrong" table:

SELECT x.player, SUM(x.cricket_score) as cricket_score, SUM(x.football_score) as football_score FROM (
    SELECT player, SUM(score) as cricket_score, 0 as football_score
      FROM cricket_table
     WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
     GROUP BY player
     UNION ALL
    SELECT player, 0 as cricket_score, SUM(score) as football_score
      FROM football_table 
     WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
     GROUP BY player
) x
GROUP BY x.player

And applying to the code:

$query = $db->prepare("    SELECT x.player, SUM(x.cricket_score) as cricket_score, SUM(x.football_score) as football_score FROM (
    SELECT player, SUM(score) as cricket_score, 0 as football_score
      FROM cricket_table
     WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
     GROUP BY player
     UNION ALL
    SELECT player, 0 as cricket_score, SUM(score) as football_score
      FROM football_table 
     WHERE dateby BETWEEN '2020-01-01' AND '2020-03-01'
     GROUP BY player
) x
GROUP BY x.player");
$query->execute();
echo '<table><tr><th>Player Name</th><th>Cricket</th><th>Football</th></tr>';
while($row = $query->fetch(PDO:: FETCH_ASSOC) ) {
    echo '<tr>
        <td>'.$row['player'].'</td>
        <td>'.$row['cricket_score'].'</td>
        <td>'.$row['football_score'].'</td>
     </tr>';
}
echo '</table>';

With this SQL you will get the values already set by cricket or football.

it returns cricket data for both cricket and football. like if the cricket score is 44 it shows 44 for the football as well. — Eniol, Mar 04 '20 at 17:27
The source of data is different for each column. Are you sure you are using the exactly code, and/or the tables have different data? I don't see how it could happen as you described. — Felippe Duarte, Mar 04 '20 at 18:53
Yes i am using the exact method and all the tables have different data from each other. — Eniol, Mar 05 '20 at 02:20
It's working exactly as expected: http://phpfiddle.org/lite/code/dngv-qjgc .. are you sure you are using the same code? If you have more tables you need to give different names to the columns and use the 0 (zero) trick in all of them. — Felippe Duarte, Mar 05 '20 at 04:42
That depends on how your data is stored. You can try add the time part to make sure it will get intraday hours, like "2020-05-03 00:00:00" and "2020-05-03 23:59:59" — Felippe Duarte, Mar 05 '20 at 13:52

score 0 · Answer 2 · answered Mar 03 '20 at 22:05

0

SELECT c.player, c.score AS cr_score, f.score AS fb_score FROM cricket_table c
INNER JOIN football_table f ON f.player=c.player AND (f.dateby BETWEEN '2020-01-01' AND '2020-03-01')
WHERE c.dateby BETWEEN '2020-01-01' AND '2020-03-01'

try outputting the table now,

while($row = $query->fetch(PDO:: FETCH_ASSOC) ) {
echo '<tr>
        <td>'.$row['player'].'</td>
        <td>'.$row['cr_score'].'</td>
        <td>'.$row['fb_score'].'</td>
     </tr>';
}

Not in a position to test it, but you get the general idea, should work

answered Mar 03 '20 at 22:05

You could use case and nulls if players don't necessarily have a score for each sport, or whatever, but that's the simplistic version ^ – Mar 03 '20 at 22:07
I tried this method. it returns wrong result, And by submitting large date range like between 2 years. mysql server stops working. and by ending mysql process giving this error Warning: Uncaught PDOException: PDO::__construct(): MySQL server has gone away in – Eniol Mar 04 '20 at 04:43
with union all it returns correct result but not populating it in html table correctly. – Eniol Mar 04 '20 at 04:45

How to populate UNION ALL query data in html table

2 Answers2