|
|
Posted by Jerry Stuckle on 10/24/06 10:29
Daz 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.
>
>
> 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.
>
You have a way of uniquely identifying the row, don't you? You have to
have something to determine if it's a duplicate or not.
And that gives you a unique index.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|