Updating table without refreshing page

Question

I'm using this page for AJAX practice; http://developer-zone.000webhostapp.com/2017/09/update-data-using-jquery-ajax-php-and-mysql

The problem is I cannot create an Add button, or basically an adding function.

May I ask for your assistance as to what am I missing here?

I tried to follow the same flow based on the video, but no luck.

This is the index.php

 <?php
  include 'connection.php';
 ?>

<!DOCTYPE html>
<html lang="en">
<head>

  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">

<br/>
<br/>
<br/>
<br/>
  <table class="table">
    <thead>
      <tr>
        <th>Firstname</th>
        <th>Lastname</th>
        <th>Email</th>
            <th>Email</th>
        <th>Action</th>
      </tr>
    </thead>
    <tbody>
      <?php
          $table  = mysqli_query($connection ,'SELECT * FROM user');
          while($row  = mysqli_fetch_array($table)){ ?>
              <tr id="<?php echo $row['id']; ?>">
                <td data-target="firstName"><?php echo $row['firstName']; ?></td>
                <td data-target="lastName"><?php echo $row['lastName']; ?></td>
                <td data-target="email"><?php echo $row['email']; ?></td>
                <td><a href="#" data-role="update" data-id="<?php echo $row['id'] ;?>">Update</a></td>
                <td><a href="#" data-role="addnew" data-id="<?php echo $row['id'] ;?>">Add</a></td>
              </tr>
         <?php }
       ?>

    </tbody>
  </table>


</div>

    <!-- Modal -->
    <div id="myModal" class="modal fade" role="dialog">
      <div class="modal-dialog">

        <!-- Modal content-->
        <div class="modal-content">
          <div class="modal-header">
            <button type="button" class="close" data-dismiss="modal">&times;</button>
            <h4 class="modal-title">Modal Header</h4>
          </div>
          <div class="modal-body">
              <div class="form-group">
                <label>First Name</label>
                <input type="text" id="firstName" class="form-control">
              </div>
              <div class="form-group">
                <label>Last Name</label>
                <input type="text" id="lastName" class="form-control">
              </div>

               <div class="form-group">
                <label>Email</label>
                <input type="text" id="email" class="form-control">
              </div>
                <input type="hidden" id="userId" class="form-control">


          </div>
          <div class="modal-footer">
            <a href="#" id="save" class="btn btn-primary pull-right">Update</a>
            <button type="button" class="btn btn-default pull-left" data-dismiss="modal">Close</button>
          </div>
        </div>

      </div>
    </div>

       <div id="addnewModal" class="modal fade" role="dialog">
      <div class="modal-dialog">

        <!-- Modal content-->
        <div class="modal-content">
          <div class="modal-header">
            <button type="button" class="close" data-dismiss="modal">&times;</button>
            <h4 class="modal-title">Modal Header ADDNEW</h4>
          </div>
          <div class="modal-body">
              <div class="form-group">
                <label>First Name</label>
                <input type="text" id="firstName" class="form-control">
              </div>
              <div class="form-group">
                <label>Last Name</label>
                <input type="text" id="lastName" class="form-control">
              </div>

               <div class="form-group">
                <label>Email</label>
                <input type="text" id="email" class="form-control">
              </div>
                <input type="hidden" id="userId" class="form-control">


          </div>
          <div class="modal-footer">
            <a href="#" id="add" class="btn btn-primary pull-right">Add</a>
            <button type="button" class="btn btn-default pull-left" data-dismiss="modal">Close</button>
          </div>
        </div>

      </div>
    </div>

</body>

<script>
  $(document).ready(function(){

    //  append values in input fields
      $(document).on('click','a[data-role=update]',function(){
            var id  = $(this).data('id');
            var firstName  = $('#'+id).children('td[data-target=firstName]').text();
            var lastName  = $('#'+id).children('td[data-target=lastName]').text();
            var email  = $('#'+id).children('td[data-target=email]').text();

            $('#firstName').val(firstName);
            $('#lastName').val(lastName);
            $('#email').val(email);
            $('#userId').val(id);
            $('#myModal').modal('toggle');
      });
      // now create event to get data from fields and update in database 

       $('#save').click(function(){
          var id  = $('#userId').val(); 
         var firstName =  $('#firstName').val();
          var lastName =  $('#lastName').val();
          var email =   $('#email').val();

          $.ajax({
              url      : 'connection.php',
              method   : 'post', 
              data     : {firstName : firstName , lastName: lastName , email : email , id: id},
              success  : function(response){
                            // now update user record in table 
                             $('#'+id).children('td[data-target=firstName]').text(firstName);
                             $('#'+id).children('td[data-target=lastName]').text(lastName);
                             $('#'+id).children('td[data-target=email]').text(email);
                             $('#myModal').modal('toggle'); 

                         }
          });
       });

          $(document).on('click','a[data-role=addnew]',function(){
            var id  = $(this).data('id');
            var firstName  = $('#'+id).children('td[data-target=firstName]').text();
            var lastName  = $('#'+id).children('td[data-target=lastName]').text();
            var email  = $('#'+id).children('td[data-target=email]').text();

            $('#firstName').val(firstName);
            $('#lastName').val(lastName);
            $('#email').val(email);
            $('#userId').val(id);
            $('#addnewModal').modal('toggle');
      });
      // now create event to get data from fields and update in database 

       $('#add').click(function(){
          var id  = $('#userId').val(); 
         var firstName =  $('#firstName').val();
          var lastName =  $('#lastName').val();
          var email =   $('#email').val();

          $.ajax({
              url      : 'connection2.php',
              method   : 'post', 
              data     : {firstName : firstName , lastName: lastName , email : email , id: id},
              success  : function(response){
                            // now addnew user record in table 
                             $('#'+id).children('td[data-target=firstName]').text(firstName);
                             $('#'+id).children('td[data-target=lastName]').text(lastName);
                             $('#'+id).children('td[data-target=email]').text(email);
                             $('#addnewModal').modal('toggle'); 

                         }
          });
       });
  });
</script>
</html>

This is the connection.php

<?php
$connection =   mysqli_connect('localhost' , 'root' ,'' ,'db_test');




if(isset($_POST['email'])){

    $firstName = $_POST['firstName'];
    $lastName = $_POST['lastName'];
    $email = $_POST['email'];
    $id = $_POST['id'];

    //  query to update data 

    $result  = mysqli_query($connection , "UPDATE user SET firstName='$firstName' , lastName='$lastName' , email = '$email' WHERE id='$id'");
    if($result){
        echo 'data updated';
    }

}
?>

This is the connection2.php

<?php
$connection =   mysqli_connect('localhost' , 'root' ,'' ,'db_test');




if(isset($_POST['email'])){

    $firstName = $_POST['firstName'];
    $lastName = $_POST['lastName'];
    $email = $_POST['email'];
    $id = $_POST['id']+1;

    //  query to update data 

    $result  = mysqli_query($connection , "INSERT INTO user (id,firstName, lastName, email)VALUES ('$id','$firstName', '$lastName', '$email')");
    if($result){
        echo 'data added';
    }

}
?>

**Warning!** You are _wide open_ for SQL injection attacks! You should use parameterized [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of using completely unescaped user data directly in your queries like that. — M. Eriksson, Aug 28 '19 at 06:10
@MagnusEriksson Yeah, I'm just using this for AJAX practice, i'm not going to implement this for my project :) — pjustindaryll, Aug 28 '19 at 06:11
Why are you doing `$id = $_POST['id']+1;`? Just set the `id` column in the database as an auto incremented primary key and you can completely remove the column from the insert-statement. — M. Eriksson, Aug 28 '19 at 06:12
It might be for learning purposes, but since you don't escape the data at all, the queries can break depending on the contents. If any of the values would, for example, contain a `'`, the query would break so that just adds to potential issues. — M. Eriksson, Aug 28 '19 at 06:13
@MagnusEriksson thank you for the tip. I never thought of it like that. — pjustindaryll, Aug 28 '19 at 06:18
as much I have understood, you want to update your table without page refresh, then you should go for `setTimeout() Method` of `javascript` please check this link https://www.w3schools.com/jsref/met_win_settimeout.asp — , Aug 28 '19 at 06:18
@dheerajkumar - I might be missing something but how would `setTimeout()` solve the OP's issue? What does that have to do with updating the database using ajax? — M. Eriksson, Aug 28 '19 at 06:30
@MagnusEriksson oops I thought he want's to update the `UI` table at some time interval sorry my bad — , Aug 28 '19 at 06:32
I managed to fix it, I can now add data, the problem is i need to refresh it for it to be visible in the table, is there a solution to this? or is there anything that I should change in my **addnew** modal? — pjustindaryll, Aug 28 '19 at 06:35
You can take a look over here: https://stackoverflow.com/questions/18490026/refresh-reload-the-content-in-div-using-jquery-ajax Regards Jordy — Jordy Loeuille, Sep 10 '19 at 12:30

Updating table without refreshing page

0 Answers0