Web lists-archives.com

Re: [PHP] Database Insert/Update/Delete




On 10/09/2015 08:53 PM, Aziz Saleh wrote:
On Fri, Oct 9, 2015 at 9:12 PM, Stephen <stephen-d@xxxxxxxxxx> wrote:

On 15-10-08 04:45 PM, Ashley Sheridan wrote:

Hi All,

This is a bit of an odd one, and while it's largely an SQL problem, it
will have PHP logic because SQL just doesn't have quite the syntax.

I'm sure this is a standard problem that has a standard solution, but as
of yet, I've not come across said solution.

I need to update a series of data in a DB. Some of it may exist already,
and some may need to be removed.

Now the typical for updating (but not removing) data and adding new is
the familiar:

INSERT ... ON DUPLICATE KEY UPDATE ...

syntax, but what about those bits which might need removing?

What's the typical solution that any of you use? I normally just empty
all rows of data sharing the common id for the set of data and then
insert new rows, but that seems wasteful to me, and less clean than it
ought to be.

A very common solution is to not actually delete.

Rather have a 'status' column that is set to active or deleted. Then a
delete becomes just another form up update.


--
Stephen


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


How is your table structured?

There are multiple solutions you can do, your business rules should dictate
how you should be doing it:

1) Remove all records and re-insert. This can be slow but if other
processes are relying on those rows, it might prove troublesome.
2) Add an updated_at field, set it for records you are adding and at the
end of the run, delete any that are relating to your ID's that are < such
time.
3) Keep track of the ID's that have been added and have a query at the end
that removes those NOT IN.

Personally and from my experience, dealing with tables that have millions
of records, solution #2 is the fastest. There can be other solutions you
might implement that might suit your needs. Additionally, insure you have
the correct indexes set for your queries to be fast enough.

If you take any approach that involves multiple queries, make sure you put them in a transaction. Not only does that help with data integrity and race conditions, it can be a little faster as well (as MySQL will only write the changes to disk once).

My usual default in this case is to DELETE all the old records, then use a multi-insert statement to add the new records, and wrap all of that in a transaction. Drupal has used that for its router rebuild process for years now (~500 records being wiped/rebuilt) and it's worked well enough, although I wouldn't do that during every request.

--Larry Garfield

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php