All geek questions in one place

Is it so bad when using MySQL queries in PHP?

I need to update many rows as per user request. This is a product site.

I could...

  • Delete all old rows for this product, and then loop through building a new INSERT query. However, this will result in the loss of all data if INSERT fails.
  • Perform UPDATE through each loop. This loop currently iterates over 8 elements, but in the future it can get up to 15. This much UPDATE does not seem like a good idea.
  • Change the database schema and add the auto_increment identifier to the rows. Then do SELECT first, get all the old row identifiers in the variable, do one INSERT , and then a DELETE WHERE IN SET .

What is the usual practice here?

thanks

+4
source share
3 answers

Just do the updates. In a transaction if you need to. 15 updates is a peanut, unless you do it on every access on the page or something like that.

You do not want to delete / reinsert rows to avoid extra queries. And you won’t be able to if you want to have a foreign key that refers to an updated table.

Almost certainly premature optimization.

+2
source

15 UPDATE really not much. If you talked about 15 hundred, then you might have to think a little more about your design ...

+2
source

The best practice is probably to use stored procedures.
http://dev.mysql.com/doc/refman/5.5/en/stored-routines.html
This will be only one PHP side command. The rest will be implemented on the MySQL server.

You can also try running multiple queries at the same time using mysqli :: multi-query.
http://php.net/manual/en/mysqli.multi-query.php

0
source

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

More articles:


All Articles