Automate update in Ajax without having to input in a textfield

Question

I am trying to automate the "scheduled" column. Right now I have set it where it displays "YES" if 1 and "NO" if 0. Currently I have a modal popup where I could enter 1 or 0, then the scheduled turns to "YES" or "NO". How do I get the $_POST to automatically check if the column is 1 or 0 and then change it to the other one if I click on "Update" in the modal?

GOAL:

Click the "YES" or "NO" >> Modal to confirm >> Update to the other.

The database is already retrieved via connection.php and is being outputted using the variable $table.

I am not sure where to place the checking for the "scheduled" value if it is in the modal or in "connection.php".

I have also tried:

$scheduled = mysqli_query($connection , "SELECT scheduled FROM user WHERE id ='$id'");

if ($scheduled = 1){
    $changesched = 0;
} else if ($scheduled = 0) {
    $changesched = 1;
}

$result  = mysqli_query($connection , "UPDATE user SET scheduled = '$changesched' WHERE id='$id'");

This did not work though, tried to add a variable to the 1 and 0 before the SQL UPDATE.

Any help would be appreciated and would like to see and example even if it is in pseudocode.

Database Name: ajax_test

Table Name: user

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.3.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">
  <table class="table">
    <thead>
      <tr>
        <th>Email</th>
        <th>Scheduled</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="email"><?php echo $row['email']; ?></td>

                <td data-target="scheduled">

                  <?php
                    if ($row['scheduled'] == 1) {
                   ?>

                   <a href="#" data-role="update" data-id="<?php echo $row['id'] ;?>">YES</a>

                  <?php
                    } else if ($row['scheduled'] == 0) {
                    ?>
                   <a href="#" data-role="update" data-id="<?php echo $row['id'] ;?>">NO</a>
                  <?php
                    }
                   ?>
                </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>
          </div>
          <div class="modal-body">
            <div class="form-group">
              <label>UPDATE SCHEDULED?</label>
              <input type="text" id="scheduled" 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>

</body>

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

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

            $('#scheduled').val(scheduled);
            $('#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 scheduled = $('#scheduled').val();
          var email =   $('#email').val();

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

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

connection.php

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

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

    $id = $_POST['id'];
    $scheduled = $_POST['scheduled'];

    $result  = mysqli_query($connection , "UPDATE user SET scheduled = '$scheduled' WHERE id='$id'");
}
?>

ArtisticPhoenix · Answer 1 · 2019-03-13T10:38:31.750

You can simply select it that way from the DB

SELECT if(scheduled , 'YES', 'NO') AS scheduled_enum, scheduled FROM user WHERE id = ?

Now you have a new "fake" field named scheduled_enum that you can easily access as any other field in your PHP. I added the normal scheduled in there so you have both.

Instead of getting back

  ['scheduled'=>0]

Now you should get

  ['scheduled_enum'=>'NO', 'scheduled'=>0]

And so on... By doing this you can reduce the steps you need to take in PHP which reduces code complexity, and could improve performance. For example normally you would have to pull each row out, check the value of scheduled, change it, store it in a new array. With this you could just do fetch all in some cases as the data is in the format you want.

  SELECT if(foo, 'YES', 'NO') AS foobar FROM table
  $data = mysqli_fetch_all($result, MYSQLI_ASSOC); //returns:[[foobar=>YES],[foobar=>NO], ...]

  //vs
  SELECT foo FROM table //returns:[[foo=>1],[foo=>0], ...]

  $data = [];
  while($row=(mysqli_fetch_assoc($result))){
     //shortest
     //  $row['foo'] = $row['foo'] ? 'YES' : 'NO';
     if($row['foo']){
         $row['foo'] = 'YES';
     }else{
         $row['foo'] = 'NO';
     } 
     $data[] = $row;
  }
  //returns:[[foo=>YES],[foo=>NO], ...]

To explain how it works, you can alias any field name and create a imaginary column. This is most often used when counting or using a function in the fields part of a select as the DB will include the function in the results

   SELECT COUNT(id) FROM table // returns:[COUNT(id) => 2]
    //vs
   SELECT COUNT(id) AS total FROM table // returns:[total => 2]

   //you can even do this - useful if you need some static string to each row
   SELECT 'bar' AS foo FROM table // returns:[foo => 'bar']

There are to many "hidden" questions scattered in there for me to answer them all. I will cover the ones most obvious to me.

You can also fix this on the client side in jQuery or any JavaSript really

     $('td a').click(function(e){
          var el = $(this);
          var scheduled= el.text() ? 'YES' : 'NO';

          $.post('connection.php', {
              id : el.data('id'),
              scheduled : scheduled
               //other stuff here ?
          }, function(data){
                //do something after request ?
                el.text(scheduled);
          });
    });

All you need for you link i this:

  <a href="#" data-role="update" data-id="<?php echo $row['id'] ;?>"><?php echo $row['scheduled'] ? 'YES' : 'NO';?></a>

Instead of repeating the whole link you can do the condition inline.

Biggest (or most obvious for a user perspective) issue right now is

In your Ajax call to connection.php, I'll show you what happens if the $('#scheduled').val()='YES' in index.php

  $('#save').click(function(){
      var id  = $('#userId').val();
      var scheduled = $('#scheduled').val();  //YES
      var email =   $('#email').val();

      $.ajax({
          url      : 'connection.php',
          method   : 'post',
          data     : {
              scheduled:scheduled,   //YES - fine so far
              id:id
           },
          success  : function(response){
                        // now update user record in table
                         $('#'+id)
                              .children('td[data-target=scheduled]')
                              .text(scheduled); //YES - you never change this
                         $('#myModal').modal('toggle');

                     }
      });
   });

Change it to something like this

  $('#save').click(function(){
      var id  = $('#userId').val();
      var scheduled = $('#scheduled').val(); //YES
      var email =   $('#email').val();

      $.ajax({
          url      : 'connection.php',
          method   : 'post',
          data     : {
                scheduled: scheduled, //YES
                id: id
          },
          success  : function(response){
                        // actually check that the update worked
                         //will make this an integer that is equal to the number of modified rows
                         //so if it's 1 pass this
                         if(response.success==1){
                             //flip this
                             scheduled= 'YES' == scheduled ? 'NO' : 'YES'; 

                             $('#'+id)
                               .children('td[data-target=scheduled]')
                               .text(scheduled); //NO -- that's more like it.

                             $('#myModal').modal('toggle');
                          }else{
                              //some error happened
                          }
                     }
      });
   });

You should check that the database update actually worked above I used response.success. So we need to make a small change to the target of the AJAX request connection.php. The easiest way to do this is to return the number of rows modified by the update, 0 for none and as your using the row id we should only modify 1 row at a time so we get 1 back for that.

<?php

 //use empty instead of isset, empty covers what isset does, plus any thing "falsy"
 //in this case we want to cover these '' empty strings, 0 zero as int, '0' as string
 if(!empty($_POST['id'])){
    //don't bother connecting if we don't have an id
    $connection =   mysqli_connect('localhost' , 'root' ,'' ,'ajax_test');

    $id = $_POST['id'];
    $scheduled = $_POST['scheduled'];

    mysqli_query($connection , "UPDATE user SET scheduled = '$scheduled' WHERE id='$id'");
   //return the number of rows updated
    $res = mysqli_affected_rows($connection);
 }else{
     $res = 0;
 }
 echo json_encode(['success'=>$res]);

 -- remove the ending ?>

I would remove the ending ?> tag, it's actually optional in a file that contains only PHP. Further something like this ?>. can ruin your JSON return because it will be like this {"success" : 1}. with an extra . on the end because of some accidental content outside of the PHP tags.

Lastly prepare your queries, to prevent SQLInjection

What is SQL injection?

There are plenty of resources on SO, and the web about this topic. Because of the length of this post there is no point In me rehashing that here.

Hope it helps!

im sorry im not sure how your answer could turn 1 to 0 and 0 to 1, im a bit confused — Button Press, Mar 12 '19 at 06:00
`There are to many "multiple" questions scattered in there for me to answer them all right now.` this line alone `I could enter 1 or 0, then the scheduled turns to "YES" or "NO". How do I get the $_POST to automatically check if the column is 1 or 0 and then change it to the other one if I click on "Update" in the modal?` were talking AJAX, JS for changing the text, Handling the REquest for POST etc.. — ArtisticPhoenix, Mar 12 '19 at 07:32
i do not get how there are "multiple" questions. I am just asking for a change of value on click from 1 to 0 or 0 to 1. I could already change by manually adding a 1 or a 0 in the textfield, then it changes. Looking for a way to change the value on click without having to use a textfield. — , Mar 13 '19 at 08:59
not sure how this "fake" field fits in. is it a variable or a column in the database table? — , Mar 13 '19 at 09:08
@JeremiahCanuto - see my update, in `index.php` where you do this `.text(scheduled);` you never actually change that, so it just changes it to the same value. — ArtisticPhoenix, Mar 13 '19 at 10:41

Automate update in Ajax without having to input in a textfield

1 Answers1