CODEWITHSUNDEEP
phpjquerymysqlajax
Jvil
Jvil

Reputation: 22

Update table records with jquery and mysql using php

I need help with updating the selected item from a list populated via php and updated with jquery, here is what I have:

my update.php front-end

 <?php include_once('db.php'); ?>
  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   <html xmlns="http://www.w3.org/1999/xhtml">
   <head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
 <title>Update Collected</title>
 <link rel="stylesheet" href="css/style.css" type="text/css" media="print, projection, screen" />
 <link rel="stylesheet" href="css/bootstrap.css" type="text/css" media="screen" />
 <link rel="stylesheet" href="css/bootstrap-responsive.css" type="text/css" media="screen" />
 </head>

  <body>
  <?php 
  $sql="SELECT * FROM qrnumber";
  $result=mysql_query($sql);


  ?>
 <div class="container-fluid main">

      <div class="row-fluid ">   
         <div class="span12">  
         <span class="success"></span>
    <table cellpadding="0" cellspacing="0" id="tablesorter-demo" class="tablesorter table table-striped">
  <thead>
  <tr>
   <th>id</th><th>Name</th><th>Points</th><th>Collected</th><th>Action</th>
  </tr>
 </thead>
  <?php while($row = mysql_fetch_array($result)) : ?>
  <tr id="<?php echo $row['id']; ?>">
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['points']; ?></td>
<td><?php echo $row['total']; ?></td>
<!-- and so on -->
<td>

   <input id="total" class="required" type="text" name="total">
  <button class="update_btn" rel="<?php echo $row['id']; ?>">update</button>

</td>
 </tr>
  <?php endwhile; ?>
 <?php
  // close connection
  mysql_close();
   ?>
  </table>
  </div>
   </div>
   </div>
   <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>  
    <script type="text/javascript" src="js/jquery.tablesorter.js"></script>
    <script>
    $(document).ready(function(){      

  $(function() {        
    $("#tablesorter-demo").tablesorter({sortList:[[0,0],[2,1]], widgets: ['zebra']});
    $("#options").tablesorter({sortList: [[0,0]], headers: { 
            3:{sorter: false}, 4:{sorter: false}}});
   );   
 $('.update_btn').click(function(){
    $('.success').text("loading...");
     var id = $(this).attr('rel');
     var total = $('#total').val();
    $.post('call.php', {Id:id, Total:total}, function(data) {
        alert(data);
    });
 });

   });
 </script>

 </body>
</html>

This is my process.php file

 <?php 
 include_once('db.php');
 var_dump($_POST);
 if (isset($_POST['collected'])){
$collected =  mysql_real_escape_string(htmlentities($_POST['collected']));
 }
 $id = $_POST['id'][0];
  $total = $_POST['total'];
  echo $id. $total;
   mysql_query("UPDATE qrnumber SET total='$total'
   WHERE id='$id'");

   ?>

The issue is that when I post a number to the input field, it makes connection to my processing php file, but does not update the content, it connects to db and passes the values from update.php to process file(call.php). Then, it sets all of the records to '0', can someone help, please.

Thanks,

jv

Upvotes: 0

Views: 1908

Answers (1)

Marc B
Marc B

Reputation: 360862

Your $_POST is wrong in PHP. PHP only creates an array of values in $_POST/$_GET if the fieldname submitted by the client ends with [] characters. e.g.

will produce the following $_POST array:

$_POST = array(
    'not_an_array' => 'bar'
    'is_an_array' => array (
       0 => 'baz'
       1 => 'qux'
    )
);

Since the Id andTotalyou're submitting in the ajax call don't have[]` in the names, they'll just be plain single values in PHP, e.g.

$id = $_POST['Id'];
$total = $_POST['Total'];

And nod that you're STILL vulnerable to SQL injection attacks, since you're trying to use $id directly in your query without escaping that either. ANY external data going into a query string is an attack vector. You cannot escape only SOME of the values and assume you're safe.

Upvotes: 1

Related Questions