Update and delete at the same time?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Update and delete at the same time?

Post 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
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Re: Update and delete at the same time?

Post 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.
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Re: Update and delete at the same time?

Post 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()); 
        }
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Update and delete at the same time?

Post by VladSun »

Update and delete at the same time?
Use transactions :)
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
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Re: Update and delete at the same time?

Post 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';";
Addos
Forum Contributor
Posts: 305
Joined: Mon Jan 17, 2005 4:13 pm

Re: Update and delete at the same time?

Post by Addos »

Thanks again. Appreciate the help here and was a good learning curve for me.
:wink:
ctmaster
Forum Newbie
Posts: 7
Joined: Wed Jul 08, 2009 10:45 pm

Re: Update and delete at the same time?

Post by ctmaster »

Just use Database's transactions.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Update and delete at the same time?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Update and delete at the same time?

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Update and delete at the same time?

Post 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.
Post Reply