This must be such a hugely used algorithm, yet I cannot find any info
about best practices etc.

The user is shown some info, and may update it.
I need to UPDATE the info in the database, or if it's not in there,
INSERT it.

So first I tried an UPDATE, and if that failed, an INSERT. This
obviously didn't work since an update affecting 0 rows is still a valid
update.

Then I read up about mysql_affected_rows() which seemed just the job
until I read it doesn't count an UPDATE if the data hasn't changed (and
thus doesn't get updated).

There are several workarounds for this, including adding extra dummy
fields to the table that always get updated, but this seems like a huge
waste of space just to see whether something is updated.

Of course I could always do a SELECT first to see if the stuff is
there, but this seems inefficient, and makes the update code look a
mess (and thus prone to errors)

Can anyone point me in the direction of an elegant solution? Surely
this problem has been hashed to death by now? :?

Thanks

lister wrote:
> This must be such a hugely used algorithm, yet I cannot find any info
> about best practices etc.
>
> The user is shown some info, and may update it.
> I need to UPDATE the info in the database, or if it's not in there,
> INSERT it.
>
> So first I tried an UPDATE, and if that failed, an INSERT. This
> obviously didn't work since an update affecting 0 rows is still a
> valid update.
>
> Then I read up about mysql_affected_rows() which seemed just the job
> until I read it doesn't count an UPDATE if the data hasn't changed
> (and thus doesn't get updated).
>
> There are several workarounds for this, including adding extra dummy
> fields to the table that always get updated, but this seems like a
> huge waste of space just to see whether something is updated.
>
> Of course I could always do a SELECT first to see if the stuff is
> there, but this seems inefficient, and makes the update code look a
> mess (and thus prone to errors)
>
> Can anyone point me in the direction of an elegant solution? Surely
> this problem has been hashed to death by now? :?
>
> Thanks

Look at INSERT ... ON DUPLICATE KEY UPDATE
and REPLACE INTO ...

http://dev.mysql.com/doc/refman/5.0/en/insert.html
and
http://dev.mysql.com/doc/refman/5.0/en/replace.html