Posted by Daz on 10/24/06 10:33

Rik wrote:
> Chung Leong wrote:
> > Daz wrote:
> >> The problem is that when I have a few hundred results to compare.
> >> Should I really query the database that many times? Could I do it
> >> with a single query, and if so, how would I know what items the user
> >> already owns a particular item, and update the database using the
> >> PHP-MySQL layer. To my understanding, you can't execute and UPDATE
> >> or INSERT statement from within a SELECT statement. Nor can you
> >> execute several statements, such as multiple UPDATE statements or
> >> several INSERT statements all in 1.
> >
> > No, that still wouldn't remove the race condition. What you want to do
> > is put a unique constraint on the table, then have your script just
> > perform the INSERT. If it fails, then you know you have a duplicate.
> > MySQL also support the INSERT ... ON DUPLICATE KEY UPDATE syntax I
> > believe.
>
> Yup, or the shorter REPLACE INTO which does exactly the same. And in that
> case it can be done in one query, like:
> REPLACE INTO tabel (fields...)
> VALUES
> (val1.1,val1.2,val1.3,val1.4),
> (val2.1,val2.2,val2.3,val2.4),
> etc....
>
> People should use unique identifiers more...
> --
> Rik Wasmus
Rik,

That's very useful to know. Thanks for your input. However, I am not
sure if I can get a list of rows that have been REPLACEd (Items that
the user already owns), and items that aren't valid in the items
reference table. The items added must be in the main reference table
(The table with 3600 items). Each of these has a unique ID, and if it
exists, it's added to the user table in the format in the post below.

Many thanks.

Daz.

[Back to original message]