How to use multiple UNION in same table

Question

I am having trouble while fetching data from results table in HTML TABLE form. I have a table results which contains information about students results where marks column having multiple value like SA1, SA2, SA3, Mid_term, Final.
So here in this query i am fetching first_name and marks from results table.
The problem is i am getting all marks under SA1 column but i want SA1 marks under SA1 Column SA2 marks under SA2 Column SA3 marks under SA3 Column.
I tried almost everything but i am unable to figure out what mistake am i doing.

Here is my code

echo '<table class = "w3-table">
<tr> 
<td> Name </td>
<td> SA1 </td>
<td> SA2 </td>
<td> SA3 </td>
<td> Mid-term </td>
<td> Final </td>
</tr>
<tr>
';
$q = "(SELECT first_name, marks  FROM results WHERE class = '11th Arts' AND 
subject = 'Arts' AND exam_type = 'SA1' )
     UNION ALL (SELECT  NULL, marks as m1  
FROM results WHERE class = '11th Arts' AND subject = 'Arts' 
AND exam_type = 'SA2' ) 
     UNION ALL (SELECT  NULL, marks as m2  FROM results
 WHERE class = '11th Arts' AND subject = 'Arts' AND exam_type = 'SA3' )
     UNION ALL (SELECT  NULL, marks as m3  FROM results 
WHERE class = '11th Arts' AND subject = 'Arts' AND exam_type = 'Mid-term')
     UNION ALL (SELECT  NULL, marks as m4  FROM results 
WHERE class = '11th Arts' AND subject = 'Arts' AND exam_type = 'Final')";

$r = mysqli_query($dbc, $q)
or die(mysqli_error($dbc));
while($row = mysqli_fetch_assoc($r))
{
    $name = $row['first_name'];
    $marks = $row['marks'];
    $marks1 = $row['m1'];
    $marks2 = $row['m2'];
    $marks3 = $row['m3'];
    $marks4 = $row['m4'];
    echo '<tr>
         <td>' .$name .'</td>
         <td>'. $marks. '</td>
         <td>'. $marks1. '</td>
         <td>'. $marks2. '</td>
         <td>'. $marks3. '</td>
         <td>'. $marks4. '</td>
         </tr>';
}
echo '</table>';
?>

What you are trying to do is called a pivot, not a union. Have a look at e.g. [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Solarflare, Jul 03 '17 at 09:46
id, username, first_name, last_name, exam_type, subject, date, marks, total_marks, class these are columns — Anny Ealexo, Jul 03 '17 at 10:50

score 0 · Answer 1 · answered Jul 03 '17 at 14:15

0

You can use mySql WHEN CASE to get your desired out put.
Try this

SELECT first_name,
case when exam_type = 'SA1' then marks end as m1,  
case when exam_type = 'SA2' then marks end as m2,
case when exam_type = 'SA3' then marks end as m3, 
case when exam_type = 'Mid-term' then marks end as m4,  
case when exam_type = 'Final' then marks end as m5 
FROM results
WHERE class = '11th Arts' AND 
subject = 'Arts'

You will get out put as

$name = $row['first_name'];
$marks = $row['m1'];
$marks1 = $row['m2'];
$marks2 = $row['m3'];
$marks3 = $row['m4'];
$marks4 = $row['m5'];

I thing it will help you.

answered Jul 03 '17 at 14:15

Bibhudatta Sahoo

4,808
2
27
51

Hi i did tried this one before as well the problem was here that first name will keep repeating and marks columns will be not in right place. – Anny Ealexo Jul 03 '17 at 20:58
According to your DB structure, you are storing user data in multiple row that's why you will get data in this structure but you can manage the data according to desired format. – Bibhudatta Sahoo Jul 04 '17 at 05:49

How to use multiple UNION in same table

1 Answers1