Update and delete at the same time?
Moderator: General Moderators
Update and delete at the same time?
I need to update a table from a multiple select list and I was wondering what is the usually way this is done. I’m puzzled that if the table to be updated originally had say 4 items marked as selected and then there was only two on the update how do I get rid of the other rows that would be left. Can I update and delete at the same time? Obviously I don't want to delete all of the rows and just those that are releted to a unique id.
Thanks for any tips
Thanks for any tips
Re: Update and delete at the same time?
Code: Select all
UPDATE `table` WHERE `id`='$id' AND `need_to_update`=TRUE;
DELETE FROM `table` WHERE `id`='$id' AND `need_to_update`=FALSE;Re: Update and delete at the same time?
Thanks for the pointers.
I ended up running the code below and would appreciate any advise as to whether this has any potential for problems as it’s working perfect but as ‘delete’ is in there it makes me nervous.
I ended up running the code below and would appreciate any advise as to whether this has any potential for problems as it’s working perfect but as ‘delete’ is in there it makes me nervous.
Code: Select all
// DELETE
$get_id = $_GET['customers_id'];
$deleteSQL = sprintf("DELETE FROM blah WHERE idfk ='$get_id'");
mysql_select_db($database_****, $ia_****);
$Result1 = mysql_query($deleteSQL, $ia_****) or die(mysql_error());
// INSERT
for($i=0; $i < count($_POST['active']); $i++) {
($id = $get_id);
$insertSQL = sprintf("INSERT INTO blah (one_idfk,two_idfk)
VALUES ('%s','%s')",
$_POST['active'][$i], $id);
mysql_select_db($database_****, $****);
$Result1 = mysql_query($insertSQL, $****) or die(mysql_error());
}Re: Update and delete at the same time?
Use transactionsUpdate and delete at the same time?
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
There are 10 types of people in this world, those who understand binary and those who don't
Re: Update and delete at the same time?
So all I have to do, for example, isCode: Select all
$get_id = $_GET['customers_id']; $deleteSQL = sprintf("DELETE FROM blah WHERE idfk ='$get_id'");
page.php?customers_id=1'; DELETE FROM blah WHERE idfk > '0
And voila, all you're data is gone.
1) escape everything you put in a query.
2) run validation checks based on what type of data it is.
3) If something is blatantly invalid, stop everything.
Code: Select all
if (!ctype_digit($_GET['customers_id'])) die; //or use your own kill switch
$get_id = mysql_real_escape_string($_GET['customers_id']); //should be overkill with above, but is good practice
//same with:
$active = mysql_real_escape_string($_POST['active'][$i]);=>Code: Select all
$deleteSQL = sprintf("DELETE FROM blah WHERE idfk ='$get_id'");
Code: Select all
$deleteSQL = "DELETE FROM blah WHERE idfk ='$get_id';";Re: Update and delete at the same time?
Thanks again. Appreciate the help here and was a good learning curve for me.

Re: Update and delete at the same time?
Just use Database's transactions.
Re: Update and delete at the same time?
There's no need for transactions for this. Simply delete all the records and insert the selected entries. That's perfectly fine and saves a lot of time.
Re: Update and delete at the same time?
Can't agree with you.astions wrote:There's no need for transactions for this. Simply delete all the records and insert the selected entries. That's perfectly fine and saves a lot of time.
What should Addos do if the INSERTs fail somehow?
PS:
@Addos
Do you really change the DB or it remains the same?mysql_select_db($database_****, $ia_****);
There are 10 types of people in this world, those who understand binary and those who don't
Re: Update and delete at the same time?
The settings would not be saved. My view is that the odds of that happening are so low that it's not worth the extra time to add transactions. Obviously this is implementation specific. If 100% accuracy was a requirement then of course transactions should be used.VladSun wrote:Can't agree with you.astions wrote:There's no need for transactions for this. Simply delete all the records and insert the selected entries. That's perfectly fine and saves a lot of time.
What should Addos do if the INSERTs fail somehow?