Add increase/decrease button in every cell of HTML table and update value in db

Question

My application displays student details and their attendance to teacher via table. Teacher can increase/decrease the student attendance with help of button present along side in seperate column. Below is the code

    <table align="center">
  <tr>
    <th>Program</th>
    <th>Branch</th>
    <th>Semester</th>
    <th>Roll No.</th>
    <th>Attendance</th>
    <th>Increase</th>
  </tr>

<?php

$tId=$_SESSION['idf'];

  $result = mysqli_query($con,"SELECT attendance.program, attendance.branch, attendance.semester, attendance.rollno, attendance.attendno from attendance, login_student WHERE login_student.rollno = attendance.rollno AND login_student.branch = attendance.branch AND login_student.program = attendance.program AND login_student.semester = attendance.semester AND attendance.tid='$tId' ORDER BY login_student.branch") or die('Error');

  function increase($roll,$att) {
        $one=1;
        $newatt=$att+$one;
        $q=mysqli_query($con,"UPDATE attendance SET attendno='$newatt' WHERE attendance.rollno='$roll' AND attendance.tid='$tId'") or die('Error');
  }

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

$program = $row['program'];
$branch = $row['branch'];
$semester = $row['semester'];
$roll = $row['rollno'];
$att = $row['attendno'];

echo '<tr><td>'.$program.'</td><td>'.$branch.'</td><td>'.$semester.'</td><td>'.$roll.'</td><td>'.$att.'</td><td><button onclick="increase('.$roll.','.$att.')">+</button></td></tr>';

}
echo '</table></div>';


?>
</table>

For now I am working on increase button which is dislayed in table but doesnot work when clicked. The increase function is not been called when button is clicked. Any help will be appreciated!

It seems like a basic misunderstanding of the differences between client side and server side code. To call that php function you need to send a HTTP request from the client to the server ~ think ajax. — Professor Abronsius, Jan 21 '21 at 14:23
Also, be warned that your code is open for SQL injection - never put variables into a query directly, always use prepared statement — Nico Haase, Jan 22 '21 at 14:26

Professor Abronsius · Accepted Answer · 2021-01-22T14:24:23.903

To achieve the interaction between the user / client and the server there needs to be some means of communication between the two. This is done, generally, using a HTTP request and it makes most sense in cases like this to use AJAX to perform the HTTP request.

What follows is untested but will, I hope, give some clue as to how you can achieve this level of interaction between the client and the backend php script.

Essentially the fetch function sends 3 parameters in the POST body - one could be used to run a different function serverside if a different value is set. The other two parameters are used in the sql update statement.

<?php
    session_start();

    #require '/path/to/DBCONN.PHP'; #<---- should be the real db connection
    
    error_reporting( E_ALL );
    ini_set( 'display_errors', 1 );


    
    if( $_SERVER['REQUEST_METHOD']=='POST' && isset(
        $con,
        $_POST['task'],
        $_POST['rollno'],
        $_POST['attend']
    )){
    
        ob_clean();
        
        if( $_POST['task']=='increase' ){
        
            function increase($con,$r,$a) {
                $sql='UPDATE `attendance` SET `attendno`=`attendno`+1 WHERE `rollno`=? AND `tid`=?';
                $stmt=$con->prepare($sql);
                $stmt->bind_param('ss',$r,$a);
                $res=$stmt->execute();
                $stmt->close();
                return $res;
            }
            $result=increase( $con, $_POST['rollno'],$_POST['attend'] );
            echo $result ? 'ok' : 'bogus';
        }
        
        
        
        if( $_POST['task']=='delete' ){
            /* etc */
        }
        if( $_POST['task']=='add' ){
            /*etc */
        }
        exit();
    }
?>
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title></title>
    </head>
    <body>
    
        <table align="center">
            <tr>
                <th>Program</th>
                <th>Branch</th>
                <th>Semester</th>
                <th>Roll No.</th>
                <th>Attendance</th>
                <th>Increase</th>
            </tr>
            <?php
            
                if( isset( $_SESSION['idf'] ) ){
                    
                    $sql='SELECT a.program, a.branch, a.semester, a.rollno, a.attendno 
                            from attendance a, login_student l
                        WHERE l.rollno = a.rollno
                            AND l.branch = a.branch
                            AND l.program = a.program
                            AND l.semester = a.semester
                            AND a.tid=? 
                        ORDER BY l.branch';
                    
                    $stmt=$con->prepare( $sql );
                    $stmt->bind_param( 's', $_SESSION['idf'] );
                    
                    $res=$stmt->execute();
                    $stmt->bind_result( $program, $branch, $semester, $rollno, $attendno );
                    
                    while( $stmt->fetch() ){
                        printf(
                            '<tr>
                                <td>%1$s</td>
                                <td>%2$s</td>
                                <td>%3$s</td>
                                <td>%4$s</td>
                                <td>%5$s</td>
                                <td><button data-task="increase" data-rollno="%4$s" data-attend="%5$s">+</button></td>
                            </tr>',
                            $program, 
                            $branch, 
                            $semester, 
                            $rollno, 
                            $attendno );
                    }
                }
            ?>
        </table>
        
        
        <script>
            document.querySelectorAll('td button').forEach( bttn=>{
                bttn.addEventListener('click',e=>{
                
                    /* create an empty FormData object and add our own values */
                    let fd=new FormData();
                        fd.append('task',e.target.dataset.task);
                        fd.append('rollno',e.target.dataset.rollno);
                        fd.append('attend',e.target.dataset.attend);
                    
                    /* send a POST request to the PHP endpoint that will perform the update ( same page here ) */
                    fetch( location.href, { method:'post',body:fd } )
                        .then( r=>r.text() )
                        .then( text=>{
                            alert( 'Our survey says: '+text )
                        });
                });
            })
        </script>
    </body>
</html>

Your code clears lot of my doubts but can you tell me what is task? I am new to php programming — Pranati Mittal, Jan 22 '21 at 09:54
`task` is a parameter I defined to specifically identify, on the PHP server, which piece (function) of code to run. If you had multiple buttons - such as `add`,`edit`,`delete``approve` you could very easily re-use the same code to send a different request simply by changing the `dataset` attribute and then crafting the appropriate handler within the php — Professor Abronsius, Jan 22 '21 at 10:09
ok got it! I made necessary changes in the code(like bind_param('ss',$r,$_SESSION['idf'])) where I found faults and it still doesn't do nything when button is clicked. Looks like button is not connected with increase function — Pranati Mittal, Jan 22 '21 at 12:03
`bind_param('ss',$r,$_SESSION['idf']))` would be incorrect - there is only 1 placeholder in the SQL statement hence only need a single `s` — Professor Abronsius, Jan 22 '21 at 12:14
Look in th econsole - what happens when the button is clicked? Are there errors? Do you see a HTTP request( XHR)? — Professor Abronsius, Jan 22 '21 at 12:24
In first php there are 2 placeholders inside increase function. And no HTTP request is getting created — Pranati Mittal, Jan 22 '21 at 13:58
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/227693/discussion-between-professor-abronsius-and-pranati-mittal). — Professor Abronsius, Jan 22 '21 at 14:09

Add increase/decrease button in every cell of HTML table and update value in db

1 Answers1