Posted by Daz on 10/24/06 02:13

Chung Leong wrote:
> Daz wrote:
> > I can think of two ways to achieve this. Firstly, I can iterate through
> > all of the users items, and use in_array() to see if they are in the
> > database array of items. I think another method I can use, is very
> > similar, but rather than have an array of database items, I can put
> > them all into a single comma seperated string, and iterate through the
> > array of user items, using regex to check if the item is in the
> > database. There may be another more efficient way to acheive the
> > results I am looking for, but I can't think of anything else.
>
> Typically you would leave the enforcement of unique conditions to the
> database, in order to avoid race conditions. The database can also more
> quickly find an existing record if the column in question has an index.

Hi Chung.

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.

I know you could use INSERT INTO table_name VALUES ('val1','val2'),
('val3','val4')...;
But this query wouldn't work, especially from within a select
statement:
$query = "INSERT INTO table_name VALUES ('val1','val2'); INSERT INTO
table_name VALUES ('val3','val4');";

It's something I wish was fixed, although I am sure there is a
perfectly valid reason for it not to be, as we both know executing
these through phpmyadmin, or through the CLI, it would work fine.

Hopefully you can see where my problem is. Just to recap, it's
essentially how to:
a) Find the items in the users list that are valid (in the database)
and then:
1) Add it if needed
OR
2) Let the user know they already have it.
AND
b) Find the items in the users list tht aren't in the database (if
any), and let them know.

I hope this makes sense.

Many thanks for you.r input.

Daz

[Back to original message]