Adding and updating db row with PHP/AJAX

Question

I've got a bit of a strange situation, Im building a site to keep track of outgoings, on my website you can insert an infinite number of 'outgoings' or 'bills' into my table, this is done using AJAX.

Once inserted, the record then shows up on the page and the user has an option to update, or delete the record.

My problem, is that when the record is inserted and posted back, I don't know what ID the record has fr4om the table so the update function doesn't work correctly. I've tried adding a a hidden field 'random-key' that when the user inserts a record, a random number is inserted and the this is posted back for my update function only I cant seem to get it working.

Has anybody an idea on how best to perform this?

Thanks

My code...

PHP form

 <form id='bill-upd'>
     <input type='hidden' value='".$info['rand']."' name='rand2' id='rand2'>
     <input type='hidden' value='".$info['id']."' name='billid' id='billid'>
     Total <input type='text' id='total' name='total' /><br />
     Bill name<input type='text' id='bill-name' name='bill-name' /><br />
     Bill descriptiion <input type='text' id='bill-description' name='bill-description' /><br />
     Bill colour<input type='text' id='bill-colour' name='bill-colour' />
     <input type='button' value='submit' onClick='updateBill();' />
 </form>

UPDATE PHP PAGE

   $uid = $_SESSION['oauth_id'];
   $id = mysql_real_escape_string($_POST['billid']);       
   $bill = mysql_real_escape_string($_POST['total']);
   $billname = mysql_real_escape_string($_POST['bill-name']);
   $billdescription = mysql_real_escape_string($_POST['bill-description']);
   $billcolour  = mysql_real_escape_string($_POST['bill-colour']);
   $rand = mysql_real_escape_string($_POST['rand2']);



        #update Record
       $query = mysql_query("UPDATE `outgoings` SET id = '$id', user_id = '$uid', bill = '$bill', bill_name = '$billname', bill_description = '$billdescription', bill_colour = '$billcolour', rand = '$rand' WHERE user_id = '$uid' AND rand = '$rand' ") or die(mysql_error());

Shyju · Accepted Answer · 2012-04-21 20:04:27Z

1

While Inserting the new record, Get the newly added id (assuming that you have a unique id for each record) from the table and return that as the part of result you are returning after your ajax call. Then you can use that id for updating the record.

You can use the below script to send the details to be updated to the update page

$("#bill-upd").submit(function(e){
   e.preventDefault();

     $.post("update.php",{ 
                     billid: $("#billid").val()
                     total: $("#total").val();
                     bill-name : $("#bill-name").val();
                     bill-description :  $("#bill-description").val();
                     bill-color: $("#bill-color").val();
                     },
                     function(result){
                        //Update / Show a message to user about the action. 
                        //alert(result);   
                     });    
     });
  });

And in your update.php page, change your query like this

$query = mysql_query("UPDATE `outgoings` user_id = '$uid', bill = '$bill', bill_name = '$billname', bill_description = '$billdescription', bill_colour = '$billcolour', rand = '$rand' WHERE id = '$uid' AND rand = '$id' ") or die(mysql_error());

Assuming ID is the unique id / Primary key in the table which we can use to update a table record.

edited Apr 21, 2012 at 20:04

answered Apr 21, 2012 at 19:54

Shyju

220k106 gold badges421 silver badges499 bronze badges

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

1 Comment

Shyju Over a year ago

@Liam: Use firebug to see what it is

Bailey Parker · Accepted Answer · 2012-04-21 19:58:29Z

1

When you use an INSERT query, you can use mysql_insert_id() to get the value of the AUTO_INCREMENT field created for the inserted row. You can send this back with JSON so Javascript knows the ID of your newly inserted row. However consider if another user (using a different browser) adds a row while another user has the page open, Javascript won't get the new row. Example:

User 1 loads page and sees no rows.
User 1 adds Row #1.
User 2 loads page and sees Row #1.
User 2 adds Row #2 and now sees Rows #1,2
User 1 adds Row #3, but only sees Rows #1,3

And now User 2 will only see rows #1,2

You should probably really be SELECT * from table after every query to make sure you have all rows (including the ones other users may have inserted). You should also consider using PDO.

answered Apr 21, 2012 at 19:58

Bailey Parker

15.9k5 gold badges58 silver badges93 bronze badges

2 Comments

Liam Over a year ago

Im very new to PHP/AJAX so i'm not 100% certain on what you mean, within my AJAX page can I run the select all query and then post data back?

Bailey Parker Over a year ago

Yes. You should be SELECTing all rows periodically because it is possible that other users have inserted rows.

Collectives™ on Stack Overflow

Adding and updating db row with PHP/AJAX

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related