Update sql database by clicking checkbox without submit button using ajax

Question

I am searching for a way to update my sql database without refreshing the page by selecting the checkbox. I am trying to achieve the id to be passed to the dolead.php when the user selects the checkbox and than check whether the id is higher than 0 it must pass the value 1 otherwise the value 0. Afther that check i need the database to be updated with the where clause the posted id. Below my code, what am i doing wrong? i get no errors in my logs and i have the error logging stated as followed: mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

PHP the user is seeing to select the switch box to trigger a select and the jquery to trigger the post.

<head>

<script src="jquery-3.2.1.min.js"></script>
<link rel="stylesheet" href="switch.css" type="text/css">


</head>

<body>

    <table id="myTable" align='left' class="deftable">
    <thead>
    <tr>
    <th style="width:10px !important;">!!</th>
    <th>Name</th>
    <th>Notes</th>
    <th>Category</th>
    </tr>
    </thead>
    <tbody>
    
    <?  //Selecteer accounts.
    
    $stmt = $mysqli->prepare("SELECT * FROM account WHERE purpose = 'Crew4' AND level <= ? AND betaald = 'yes' 
    AND group_name = ? AND secret = 'no' ");
    $stmt->bind_param("is", $status,$groupname);
    $stmt->execute();
    $result = $stmt->get_result(); //only works when nd_mysli is set on the server!

    while ($row = $result->fetch_assoc()) {
  ?>  
    <tr>
       <td class="footer"> 
  <label class="switch">
  <input type="checkbox" <?php if($row['do_lead']=='1')  {
                 echo 'checked="checked"';
 } ?> value="<?php echo filter_var($row['id'], FILTER_VALIDATE_INT); ?>" />
  <div class="slider round">
    <span class="on">ON</span>
    <span class="off">OFF</span>
  </div>
</label></td>
    <td><?= htmlspecialchars($row['name']) ?></td>
    <td><?= htmlspecialchars($row['notes']) ?></td>
    <td><?= htmlspecialchars($row['purpose']) ?></td>
 
    
   

    </tr>
    <? } ?>
    </table>


</body>

<script>

$(document).ready(function()

{

var x=$("input[type=checkbox]").length;

$('input[type=checkbox]').on('change', function(i)

{

if($(this).is(':checked'))

{

x-=1;

if(x==0)

{

var val = [];

$(':checkbox:checked').each(function(i)

{

val[i] = $(this).val();

});

var jsonString = JSON.stringify(val);

$.ajax(

{

type: "POST",

url: "dolead.php",

data: {checkbox_value:jsonString},

cache: false,

success: function(data)

{

/* alert if post is success */

}

});

}

}

else

{

x+=1;

}

});

});

</script>

</html>

The dolead.php to retrieve the post value and update the database;

$data = json_decode(stripslashes($_POST['checkbox_value']));

foreach($data as $d){

    if($d > 0){$leadupdate = 1;}
    if($d == ''){$leadupdate = 0;}
        
    $stmt48 = $mysqli->prepare("UPDATE account SET do_lead = ? WHERE id = ? ");
    $stmt48->bind_param("ii", $leadupdate,$d);
    $stmt48->execute();
    $stmt48->close();       

}


?>

PS: the connection file is included but not shown here..

What about JS errors? Have you checked the Developer Toolbar console? — El_Vanja
– El_Vanja, Commented Nov 28, 2020 at 0:07
What about the Network tab? Have you determined that the AJAX call takes place? What do you get as the response? — El_Vanja
– El_Vanja, Commented Nov 28, 2020 at 0:12
It looks like the call is not taking place.. What is wrong with the script..?? — newtocss
– newtocss, Commented Nov 28, 2020 at 0:26

KUMAR · Accepted Answer · 2020-11-28 01:38:19Z

1

You could add a class to the input and set the value dynamically:-

View:-

<input class="active" type="checkbox" value="$row['do_lead']" >

jQuery/AJAX Call:-

    <script type="text/javascript" src="https://code.jquery.com/jquery-3.4.1.min.js"></script>

<script type="text/javascript">
$(document).ready(function(e){ 
    $("input.active").click(function() {
    // store the values from the form checkbox, then send via ajax below
    var check_active = $(this).is(':checked') ? 1 : 0;
    var check_id = $(this).attr('value');
    
        $.ajax({
            type: "POST",
            url: "dolead.php",
            data: {id: check_id, active: check_active}
            success: function(response){
                alert('Data Updated Successfully');
    
            }
        });
    return true;
    });
});
</script>

PHP:-

// CLIENT INFORMATION
$active = mysqli_real_escape_string($_POST['active']);
$id = mysqli_real_escape_string($_POST['id']);

//update query.
//execute query.

Note:- for more info regarding click()

https://api.jquery.com/click

edited Nov 28, 2020 at 1:38

answered Nov 28, 2020 at 0:49

KUMAR

2,0952 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

newtocss Over a year ago

Thanks for your reaction! When trying above solution i get the following error in my console log. Uncaught SyntaxError: Unexpected identifier. it seems there is womething wrong with the succes code. ` data: {id: check_id, active: check_active} success: function(response){ alert('Data Updated Successfully'); }`

newtocss Over a year ago

Update. There was a , missing. The code executes but it does not pass any values? Maybe because the table generates multiple checkboxes with the same class? I get no errors just empty values.

KUMAR Over a year ago

@newtocss can you get check_active & check_id on chechkbox click??

newtocss Over a year ago

Sorry i dont know what you are asking. Since the post is empty i guess nog? In the consol i can see that row id is filled and i see that the script you presented is sending the 1 or 0 value, if i inderstand correctly as i am new to ajax..

newtocss Over a year ago

Hi Kumar. Many thanks your sollution worked after a few changes. The values where empty because of the real escape string so it seemed. When i removed those the update happend and the values where passed!

Collectives™ on Stack Overflow

Update sql database by clicking checkbox without submit button using ajax

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related