Page 1 of 1
Update and delete at the same time?
Posted: Mon Jul 13, 2009 11:56 am
by Addos
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
Re: Update and delete at the same time?
Posted: Mon Jul 13, 2009 3:54 pm
by Skara
Code: Select all
UPDATE `table` WHERE `id`='$id' AND `need_to_update`=TRUE;
DELETE FROM `table` WHERE `id`='$id' AND `need_to_update`=FALSE;
?? I don't know of a way to do an UPDATE and DELETE in one go. I'm pretty sure it's impossible. You could maybe do it through subqueries, but I don't think MySQL is very good at that. Oracle, maybe.
Re: Update and delete at the same time?
Posted: Tue Jul 14, 2009 3:30 am
by Addos
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.
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?
Posted: Tue Jul 14, 2009 7:50 am
by VladSun
Re: Update and delete at the same time?
Posted: Tue Jul 14, 2009 12:09 pm
by Skara
Code: Select all
$get_id = $_GET['customers_id'];
$deleteSQL = sprintf("DELETE FROM blah WHERE idfk ='$get_id'");
So all I have to do, for example, is
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]);
Also...
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?
Posted: Thu Jul 16, 2009 12:15 am
by Addos
Thanks again. Appreciate the help here and was a good learning curve for me.

Re: Update and delete at the same time?
Posted: Thu Jul 16, 2009 5:07 am
by ctmaster
Just use Database's transactions.
Re: Update and delete at the same time?
Posted: Thu Jul 16, 2009 7:32 pm
by Benjamin
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?
Posted: Fri Jul 17, 2009 1:27 am
by VladSun
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.
Can't agree with you.
What should
Addos do if the INSERTs fail somehow?
PS:
@Addos
mysql_select_db($database_****, $ia_****);
Do you really change the DB or it remains the same?
Re: Update and delete at the same time?
Posted: Fri Jul 17, 2009 2:16 am
by Benjamin
VladSun wrote: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.
Can't agree with you.
What should
Addos do if the INSERTs fail somehow?
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.