Update mysql table with php using ajax

Question

Here I create a table http://jsbin.com/OJAnaji/13/edit and DEMO: http://jsbin.com/OJAnaji/13

So when users click on some row on table automaticly populate input fields with values from table into modal window. Modal window user open when click on button "Edit row". Now I need to know how I can update mysql table with columns: Name,Gender,Age,Donuts eaten.

I create js ajax:

$("#edit").click(function() {
    //in here we can do the ajax after validating the field isn't empty.
    if($("#name").val()!="") {
        $.ajax({
            url: "update.php",
            type: "POST",
            async: true, 
            data: { Name:$("#name").val(), Gender:$("#gender").val(), Age:$("#age").val(), Donuts_eaten:$("#donuts_eaten").val()}, //your form data to post goes here as a json object
            dataType: "html",

            success: function(data) {
                $('#output').html(data); 
                drawVisualization();   
            },  
        });
    } else {
        //notify the user they need to enter data
    }
});

HTML - modal window and button:

<!-- Button trigger modal -->
<button id="edit" class="btn btn-success disabled" type="button" data-toggle="modal" data-target="#myModal">
Edit selected row</button>

<!-- Modal -->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
        <h4 class="modal-title" id="myModalLabel">Add new row</h4>
      </div>
      <div class="modal-body">
        <div class="input-group">
  <span class="input-group-addon">Name</span>
  <input type="text" value="" id="name" class="form-control" placeholder="Type name">
        </div></br>
        <div class="input-group">
  <span class="input-group-addon">Gender</span>
  <input type="text" id="gender" class="form-control" placeholder="Gender?">
</div></br>
        <div class="input-group">
  <span class="input-group-addon">Age</span>
  <input type="text" id="age" class="form-control" placeholder="Number of age">
</div></br>
        <div class="input-group">
  <span class="input-group-addon">Donuts eaten</span>
  <input type="text" id="donuts_eaten" class="form-control" placeholder="Number of donuts eaten">
</div></br>

      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
        <button type="button" class="btn btn-primary">Save changes</button>
      </div>
    </div><!-- /.modal-content -->
  </div><!-- /.modal-dialog -->
</div><!-- /.modal -->

So how I can now update MySql database with php:

so file update.php how must looks like:

<?php
$con = mysql_connect('localhost', 'gmaestro_agro', 'pass') or die('Error connecting to server');

mysql_select_db('gmaestro_agro', $con); 

//HOW I CAN UPDATE MYSQL DATABASE, WHAT I NEED TO ADD HERE?

?>

What have you tried? What examples are you following but are still not working and what errors do you get? — Jonast92
– Jonast92, Commented Feb 3, 2014 at 15:31
//HOW I CAN UPDATE MYSQL DATABASE, WHAT I NEED TO ADD HERE?... How do we possibly know without seeing any of your table structures... You'll have to do something using the $_POST[] arguments you gave it via the AJAX call (Name, Gender, Age, Donuts_eaten) — AeroX
– AeroX, Commented Feb 3, 2014 at 15:33
Table structure: Table name: stat, Columns: Name,Gender,Age,Donus eaten .. — MikiMrki
– MikiMrki, Commented Feb 3, 2014 at 15:37

Scott Fleming · Accepted Answer · 2014-02-03 16:48:37Z

2

You should have a column in the table which is an auto-increment column, such as "id" or like the example below uses "index_id". This should be used when creating your form, and sent along with the $_POST array to reference the row you are updating. This is a simple example, which you can use to get you started.

 $_POST = stripslashes_deep($_POST); # you will want to better filtering for security.
    if(isset($_POST['Name']) && $_POST('Name') !=''){

    $query = "UPDATE stat
              SET Name   ='". $_POST['Name'] . "',
                  Gender ='". $_POST['Gender'] . "',
                  Age    ='". $_POST['Age'] . "',
                  Donuts_eaten  ='" .$_POST['Donuts_eaten'] . "'
             WHERE
                 index_id = '". $_POST['index_id'] . "'";

    $result = mysql_query($query) or die(mysql_error()); 
    exit(json_encode($_POST)); 
    }


    function stripslashes_deep($value)
    {
        $value = is_array($value) ?
            array_map('stripslashes_deep', $value) :
            stripslashes($value);

        return $value;
    }

For your MYSQL table you can run this in your MYSQL PhpMyAdmin:

ALTER TABLE  `stats` ADD  `index_id` INT( 3 ) NOT NULL AUTO_INCREMENT FIRST ,
ADD PRIMARY KEY (  `index_id` )

edited Feb 3, 2014 at 16:48

answered Feb 3, 2014 at 15:59

Scott Fleming

4506 silver badges14 bronze badges

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

1 Comment

Scott Fleming Over a year ago

Yes, it is the best way to be able to reference any row you need to update.

Funk Forty Niner · Accepted Answer · 2016-02-23 21:04:03Z

1

In your update.php, do like this,

$name = $_POST['Name'];
$gender = $_POST['Gender'];
$age = $_POST['Age'];
$donuts = $_POST['Donuts_eaten'];

$query = "UPDATE `your_table_name` SET name ='".$name."', gender ='".$gender."', 
age='".$age."', donuts_eaten ='".$donuts."'  ";

mysql_query($query, $con);

Just a basic to basic structure on what you need to do in update.php its up to you to kick it a notch and you've used POST in your ajax that why its $_POST.

note: Dont use reserved word as your field name in the database.

edited Feb 23, 2016 at 21:04

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Feb 3, 2014 at 15:53

rockStar

1,29612 silver badges22 bronze badges

4 Comments

MikiMrki Over a year ago

yes but hw will query looks like: this is what I want to know

Scott Fleming Over a year ago

FYI: That query rockStar posted will update ALL rows in your table, unfortunately as it does not specify which row, so you'll want to have a WHERE clause and reference an ID.

rockStar Over a year ago

He needs to put that himself to specify which row. I just added what is needed.

Funk Forty Niner Over a year ago

@rockStar The connection comes second when using the mysql_ API. I found this Q&A for another question so I thought I'd fix this for future visitors while I was here ;-) so I edited your answer. However, your answer does not contain a WHERE clause and that will update an entire table without it. Just saying

Collectives™ on Stack Overflow

Update mysql table with php using ajax

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related