Web lists-archives.com

Re: [PHP] Database Insert/Update/Delete




On Fri, 2015-10-09 at 21:53 -0400, 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.

Hi Aziz,

Thanks for that. I was thinking along similar lines myself, I just
wondered if there was some better way that I was completely missing. The
timestamp method would work well for me for keeping older versions of
the content, which is something I plan on doing with this project later
on down the line.

Thanks,
Ash

http://www.ashleysheridan.co.uk




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