|
|
Posted by Daz on 10/24/06 10:37
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.
[Back to original message]
|