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

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.

I can't use any unique keys on my table, as each user can have 'up to'
3600 items, and a row is added for each item the user has, in the user
table. For example:

+-----+---------+
| uid | item_id |
+-----+---------+
| 3 | 1 |
| 3 | 3 |
| 3 | 5 |
| 3 | 6 |
| 3 | 7 |
| 3 | 9 |
| 3 | 12 |
| 3 | 13 |
| 3 | 15 |
| 3 | 16 |
+-----+---------+

If a row doesn't exist, then a user doesn't own the item.

[Back to original message]