All geek questions in one place

MySQL UPDATE vs. INSERT and DELETE

I am working on a web application project, and there is a rather large html form that needs to have its data stored in a table. The form and the insert are already done, but my client wants to be able to load the saved data back into the HTML form and be able to change it, again, this is not a problem, but I came across a question, when are you going to do the update, it would be advisable to just save insert request and then delete the old line if it was editing?

Basically, what already happens when the form is submitted, all the data is put into the table using INSERT, I also have a flag called edit, which contains the identifier of the primary key, if the data is for the field being updated. I can handle the update function in two ways:

a) Create an actual update request with all fields / datasets and use if / else to decide whether to run the update or insert the request.

b) Paste each time, but add one row to DELETE WHERE row = editID after a successful paste.

Since deleting will only happen if INSERT was successful, I do not risk deleting the data without inserting, thereby losing the data, but since INSERT / DELETE are two queries, it would be less efficient than just using if / else to solve Should I run insert or update?

There is a second table that uses the auto-increment id as a foreign key, but this table needs to be updated every time the form is submitted, so if I delete a row in table A, I will also delete the related rows from table b. This seems like bad programming practice, so I'm leaning towards option a) anyway, but it's very tempting to just use the one line option. DELETE will look like this. Is this really bad programming practice? Beyond the conventions, are there any reasons why this is "never do it!". type of code?

if ($insertFormResults) { $formId = mysql_insert_id(); echo "Your form was saved successfully."; if(isset($_POST['edit'])){ $query = "DELETE FROM registerForm WHERE id='$_POST[edit]'"; $result = mysql_query($query); } }
+4
source share
3 answers

While the INSERT / DELETE parameter will work fine, I would recommend against it:

  • If you do not bind INSERT / DELETE into a single transaction, or even better encapsulate the INSERT / DELETE procedures in which you perform the theoretical risk of duplicate accumulation. If you are using SP or a transaction, you are simply rewriting the UPDATE statement, which is obviously inefficient and, in addition, will allow you to rise to a few WTF raised eyebrows later by someone supporting your code.
  • Although this does not seem like a question in your case, you are potentially affecting referential integrity if you need it. In addition, you lose the pretty useful ability to easily retrieve records in the order they were created.
  • Probably not a very small application, but you end up with a fragmented database rather quickly which will slow down the data search.
+3
source

Updating is only one return trip to the server, which is more efficient. If you have a reason that it is possible to get bad data, always use UPDATE by default.

0
source

It seems to me that performing the deletion is pointless, if you run the update in MySql, it will only update the record, if the other, that what is already saved, there is some reason why you will need to do the deletion instead, Usually I use the case (switch) to catch update / delete calls from the user,

 <?php switch (action) { case "delete" : block of coding; if the condition equals value1; break; case "edit" : block of coding; if the condition equals value2; break; } ?>
0
source

Source: https://habr.com/ru/post/1337833/

More articles:


All Articles