CODEWITHSUNDEEP
phpmysqlsqlsumwamp
Kreng Kongkeng
Kreng Kongkeng

Reputation: 227

calculation in php mysql

I want to do a calculation on percentage of student attendance of each students, and it will display each attendance percentage that belongs to each of the students. Below is my code:

<tbody>
    <?php
        $data = "SELECT SUM(studAtt_endTime - studAtt_startTime) FROM studentAttendance";   
        $result = $conn->query($data)
        or die ("Error: ".mysqli_error($conn)); //look if theres any error
            while($ser2=mysqli_fetch_array($result)) {
    ?>

    <?php
        $counter = 1;
        $data = "SELECT * FROM student INNER JOIN studentAttendance ON student.stud_matric=studentAttendance.student_stud_matric
                            INNER JOIN course ON course.course_code=studentAttendance.course_course_code
                            WHERE course_course_code LIKE '%$_GET[course]%'
                            GROUP BY student_stud_matric";
                $result = $conn->query($data)
                or die ("Error: ".mysqli_error($conn)); //look if theres any error      
                    while($ser=mysqli_fetch_array($result)) {
    ?>
                <tr>
                <td><center><?php echo $counter; 
                            $counter++; ?></center></td>
                <td><?php echo $ser["stud_matric"];?></td>
                <td><?php echo $ser["stud_name"];?></td>
                <td><?php
                        $a = $ser["course_contacthour"];
                        $b = "14";
                        $tch = ($a * 2 ) * $b; // tch= total contact hour in 14 week
                        echo number_format($ser2["SUM(studAtt_endTime - studAtt_startTime)"] / $tch * 100, 2);?></td>
                </tr>
                                    <?php
                                    }
                                    ?>
                                    <?php
                                    }
                                    ?>
                                </tbody>

The problem is, this code will sum up all the students attendance percentage and it will show the same percentage for every students, instead of percentage on the student itself.

Upvotes: 1

Views: 1631

Answers (2)

Professor Abronsius
Professor Abronsius

Reputation: 33823

Rather than edit the above / below and confuse things, I'll post some other stuff here. Without seeing the schema I am not sure if the following SQL is correct in that I do not know if the columns studatt_endtime and studatt_starttime are in the studentAttendance table or the student table so you might need to change the prefix used:

/* Now the `sum` is pertinent to the student whereas before it was not */
$data = "select *,
        sum( a.`studatt_endtime` - a.`studatt_starttime` ) as 'sum'
        from `student` s
        inner join `studentattendance` a on s.`stud_matric`=a.`student_stud_matric`
        inner join `course` c on c.`course_code`=a.`course_course_code`
        where a.`course_course_code` like '%".$course."%'
        group by a.`student_stud_matric`;";

Also, I do not know what you are trying to calculate really and maths was never a strong point for me but the calculation seems a little ambiguous in that there is no adherance to the BODMAS method of calculation, where BODMAS stands for Brackets, Order, Division, Multiplication, Addition, Subtraction - see here for details so the calculation could be interprested in different ways like:

$tot=number_format( $ser->sum / ( $tch * 100 ), 2 );
$alt_tot=number_format( ( $ser->sum / $tch ) * 100, 2 );

Upvotes: 0

Professor Abronsius
Professor Abronsius

Reputation: 33823

<html>
    <head>
        <title></title>
    </head>
    <body>
        <table>
            <tbody>
                <?php

                $counter = 1;

                /* Some rudimentary filtering at the very least, better use prepared statements or PDO */
                $course = addslashes( filter_input( INPUT_GET,'course',FILTER_SANITIZE_STRING ) );

                /* I think you can combine the two sql queries and have only 1 loop */
                $data = "select *,
                            ( select sum( `studatt_endtime` - `studatt_starttime` ) from `studentattendance` ) as 'sum'
                            from `student` s
                            inner join `studentattendance` a on s.`stud_matric`=a.`student_stud_matric`
                            inner join `course` c on c.`course_code`=a.`course_course_code`
                            where a.`course_course_code` like '%".$course."%'
                            group by a.`student_stud_matric`;";

                /* Query the db - do not reveal too much information if there is a problem */   
                $result = $conn->query( $data ) or die ("Error: Something went wrong...");
                if( $result ){

                    /* Loop through recordset */
                    while( $ser=$result->fetch_object() ) {

                        /* Do your calculations */
                        $a = $ser->course_contacthour;
                        $b = "14";
                        $tch = ( $a * 2 ) * $b;
                        $tot=number_format( $ser->sum / $tch * 100, 2);

                        /* write the output */
                        echo '
                        <tr>
                            <td>
                                <center>'.$counter.'</center>
                            </td>
                            <td>'.$ser->stud_matric.'</td>
                            <td>'.$ser->stud_name.'</td>
                            <td>'.$tot.'</td>
                        </tr>';

                        $counter++;
                    }
                }
                @mysqli_free_result( $result );
            ?>
            </tbody>
        </table>
    </body>
</html>

Upvotes: 1

Related Questions