Posted by Jerry Stuckle on 10/24/06 11:29

Daz wrote:
> Jerry Stuckle wrote:
>
>>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.
>
>
> At present, I simply pull up a derived table for the user, and my
> script iterates through the rows, and checkes which items that user
> owns. Rows are added if they aren't in the user table, however, the
> user is advised if the item name they are adding is invalid, and the
> item is not added.
>
> I would be happy to give you an example of all of the tables I am using
> (three in all), if you'd like.
>
> All the best.
>
> Daz.
>

So, validate the names. Then use Chung's process for updating your
table with the ones which are valid.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]