0

I have three tables this is my codes:

<table border='1'>
        <tr>
        <th>Subject</th>
        <th>Mark1</th>
        <th>Mark2</th>
        <th>Mark3</th>
        <th>Sum Marks</th>
        </tr>

<?php
    $query2 = "SELECT  students_info.s_name, subjects.subject, marks.*
            FROM  subjects
            RIGHT JOIN  (students_info
            RIGHT JOIN  marks  ON students_info.s_id = marks.s_id
                )  ON subjects.sub_id = marks.sub_id
            WHERE  students_info.s_id = '".$s_id."'";

    $result2=mysqli_query($link,$query2) OR die(mysqli_error($link));   

        while ($row=mysqli_fetch_array($result2))
        {
 ?>
        <tr>
            <td><?php echo $row['subject'];?></td>
            <td><?php echo $row['n1'];?></td>
            <td><?php echo $row['n2'];?></td>
            <td><?php echo $row['n3'];?></td>
            <td><?php echo $row['n1']+$row['n2']+$row['n3'];?></td>
        </tr>
<?php
        }
?>
        </table>
 <?php
}
?>

Output my codes:

enter image description here

I want when each mark form(mark1,mark2,mark3) for every subject less than 49 the sum operation not work in the cell sum marks appear empty value or empty cell, Please review my code and give me a help, to do work this code.

1 Answer 1

1

Could add them up in SQL by reverting to 0 for NULL columns like:

SELECT students_info.s_name, subjects.subject, marks.*, 
       COALESCE(marks.n1, 0) + COALESCE(marks.n2, 0) +COALESCE(marks.n3, 0) as  sum_marks
FROM ....
6
  • Thanks for your answer @danblack, i used your code but the same output appear, i want to when each mark less than(<49) the cell Sum Marks must be empty for this row
    – Talib
    Commented Aug 31, 2018 at 20:45
  • I don't quite get the criteria however use: IF( expr < 49, 0, val).
    – danblack
    Commented Sep 2, 2018 at 22:35
  • I used this criteria, but this way save only makr3 :( .
    – Talib
    Commented Sep 3, 2018 at 20:02
  • 1
    IF( marks.n1 < 49 AND marks.n2 < 49 AND marks.n3 < 49, NULL, COALESCE(marks.n1, 0) + COALESCE(marks.n2, 0) +COALESCE(marks.n3, 0))
    – danblack
    Commented Sep 3, 2018 at 21:22
  • Thanks @danblack, that's worked for me, but if the name subjects on the header name students on the left and marks on the center how this code work for sum?
    – Talib
    Commented Sep 4, 2018 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.