1

I have this data in student table:

stu_id  stu_name   stu_exam   stu_exam_year  stu_mark  stu_grade  stu_est_mark  stu_est_grade
111     Alice      First      2015           80        A          
111     Alice      Mid        2015           75        B
111     Alice      Final      2015           65        B

222     Frankline  First      2015           75        B
222     Frankline  Mid        2015           60        B
222     Frankline  Final      2015           50        C

i want to output the data in a table form as below where estimate grade = total marks from each exam / total marks of full mark * 100

No    ID    Name       First-term Exam     Mid-term Exam       Final Exam         Estimated
                       Mark   |  Grade     Mark   |  Grade   Mark   |  Grade    Mark   |  Grade
1     111   Alice      80         A        75        B       65         B       73      B
2     222   Frankline  75         B        60        B       50         C       62      B

I try to use this SELECT statement

<?php
$no = 0;
query = "SELECT * FROM student GROUP BY stu_id;"

$result_set =  mysqli_query($con, $query);

while( $row = mysqli_fetch_array($result_set)){

$no++;
?>

And apply some condition at the as below

<td><?php echo $no; ?></td>                        
<td><?php echo $row['stu_name']; ?></td>

<!--This is for First-term -->
<?php if(($row['stu_exam'] == "First"){ ?>
<td><center><?php echo $row['stu_mark']; ?></center></td>
<td><center><?php echo $row['stu_grade']; ?></center></td>
<?php }
else {?>
<td><center></center></td>
<td><center></center></td>
<?php } ?>

<!--This is for Mid-term -->
<?php if(($row['stu_exam'] == "Mid"){ ?>
<td><center><?php echo $row['stu_mark']; ?></center></td>
<td><center><?php echo $row['stu_grade']; ?></center></td>
<?php }
else {?>
<td><center></center></td>
<td><center></center></td>
<?php } ?>

<!--This is for Final -->
<?php if(($row['stu_exam'] == "Final"){ ?>
<td><center><?php echo $row['stu_mark']; ?></center></td>
<td><center><?php echo $row['stu_grade']; ?></center></td>
<?php }
else {?>
<td><center></center></td>
<td><center></center></td>
<?php } ?>

But the query just read the first list of the row in the table of each stu_id only and output it as below

No    ID    Name       First-term Exam     Mid-term Exam       Final Exam         Estimated
                       Mark   |  Grade     Mark   |  Grade   Mark   |  Grade    Mark   |  Grade
1     111   Alice      80         A        
2     222   Frankline  75         B

why? and i want to calculate the estimate mark(average) where sum of all marks of each exam divide by total marks of all taken exams. how to generate this values and update the generated value into the student table at the stu_est_mark and stu_est_grade attributes.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Yun
  • 19
  • 3
  • Group by collapses all your rows. This might be useful https://stackoverflow.com/questions/33351330/php-mysqli-group-by – Chhitij Dec 03 '17 at 11:38
  • @Chhitij but didn't i should GROUP BY it? because i need to collapse 3 rows to become 1 right? i'm sorry if i misunderstood the basic.. i'm a novice in this programming world.. – Yun Dec 03 '17 at 13:06
  • Group By just collapses the rows into one. I think what you are referring to is joining and grouping – Chhitij Dec 03 '17 at 13:14
  • in my example above.. how should i group and join them? – Yun Dec 03 '17 at 13:16
  • I have edited my answer. I had made an error. Sorry for that. Change the first variable name to $q1 instead of $q2. It will work – Chhitij Dec 03 '17 at 13:21

3 Answers3

2

One thing that can be done is to get all the data from the database and then classify the marks as per student id within your php code. Something like:

$q1=mysqli_query($con,"SELECT DISTINCT(stud_id) FROM student");
while($row=mysqli_fetch_assoc($q1))
{
   $q2=mysqli_query($con,"SELECT * FROM student where stud_id=".$row['stud_id']);
   while($r=mysqli_fetch_assoc($q2))
   {
       //your code
   }
}
Chhitij
  • 111
  • 7
  • it results in only read 1 student and all her exam results. it shows 3 rows of Alice with her exam results in the respective column. – Yun Dec 03 '17 at 12:55
  • are you sure you have used the braces of the loops correctly? Because it is unexpected – Chhitij Dec 03 '17 at 13:06
  • ok now it output all results but in 6 rows.. 3 Alice, 3 Frankline. How to make it just 2 rows? 1 row for Alice with 3 exam results at the respective column and 1 row for Frankline with 3 exam results at the respective column. – Yun Dec 03 '17 at 13:25
  • that depends on your html. If you end the row and start a new one, only then the row will change. Make sure you use at the right place – Chhitij Dec 03 '17 at 13:33
  • Yikes iterated queries do not perform well. – mickmackusa Dec 27 '19 at 00:20
0
query = "SELECT * FROM student GROUP BY stu_id;"

I'm a but rusty with my SQL but Group By might be the cause

try it just as 

query = "SELECT * FROM student

if that doesn't work you might want to aggregate the 3 entries per student into one line using a join command in the query

Justin Bland
  • 43
  • 1
  • 12
  • it results in a table with 6 rows. 3 Alice and 3 Frankline with respective exam marks – Yun Dec 03 '17 at 12:38
  • how to aggregate the 3 entries per student into one line using a join command in the query? – Yun Dec 03 '17 at 12:40
  • ok thats good, at least its reading all the data and doing what its meant to, now we just have to get it on 1 line and not 3, are you using MySQL or MSSQL ? – Justin Bland Dec 03 '17 at 14:07
  • after some quick research using pivot might help you here, i haven't used this before so I'm not sure if it is helpful but ill add the link so you can have a look through https://stackoverflow.com/questions/7674786/mysql-pivot-table – Justin Bland Dec 03 '17 at 14:09
  • I'm using MySQL – Yun Dec 03 '17 at 15:51
-1

Try using 

if (row['stu_exam']=='Mid')

== does not work as expected with string. So, try using === for strings.

Also try using elseif. That's why elif is created.

Ashutosh Chapagain
  • 930
  • 9
  • 15