A Question of design.

Hello all,

my question is more regarding advice on a script design. I have about
3600 entries in my database, the user submits a list, which is then
checked against those in the database to confirm whether or not they
already own a particular item. If they do, then it's not added to the
user table, whereas if it is, then it _is_ added to the user table.
However, if the item is not in the database, the user is advised of
this. So basically, I need to figure out a quick way to compare the
users submited items (probably 50 to 700 items), with those in an array
that I have created using the items from the database.

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.

I would appreciate it if anyone could tell me which of the 2 is likely
to be faster, or even if there is an even better way altogether. I need
to find the quickest way, as I don't want to over work the server or
for the processing to cause a server timeout.

All the best.

Daz.

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.

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

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.

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.

Yup, or the shorter REPLACE INTO which does exactly the same. And in that
case it can be done in one query, like:
REPLACE INTO tabel (fields...)
VALUES
(val1.1,val1.2,val1.3,val1.4),
(val2.1,val2.2,val2.3,val2.4),
etc....

People should use unique identifiers more...
--
Rik Wasmus

Following on from Daz's message. . .
>Hello all,
>
>my question is more regarding advice on a script design. I have about
>3600 entries in my database, the user submits a list, which is then
>checked against those in the database to confirm whether or not they
>already own a particular item. If they do, then it's not added to the
>user table, whereas if it is, then it _is_ added to the user table.
>However, if the item is not in the database, the user is advised of
>this. So basically, I need to figure out a quick way to compare the
>users submited items (probably 50 to 700 items), with those in an array
>that I have created using the items from the database.
As I read this you are simply trying to decide which items in list U are
not in list D (U=user's list D=database list).

Two methods spring to mind.
1 - (Possibly not suitable for PHP)
You set up two arrays of bits with the position in the array being the
'ID'.
So if the U list has items 3,4 and 6 the array looks like 00011010000...
and similarly with the D list and now you can AND (etc) to give set
operations.


2 - (Probably better for PHP)
Sort both lists
Set two pointers to start (lowest) of both lists (call them pU and pD)
repeat until end of both lists reached
Compare the pointed to items
if D[pD] == U[pU] then "U already has this D". Bump both pointers
if D[pD] < U[pU] then "U doesn't have this D". Bump pD.
if D[pD] >U[pU] then "This U isn't in D". Bump pU.

With any luck your D list should be pre-sorted as a result of the DB
query.

For speed you may want to bulk your updates by doing the logic and all
of the 'what goes in which category' first.

--
PETER FOX Not the same since the porcelain business went down the pan
peterfox@eminent.demon.co.uk.not.this.bit.no.html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.demon.co.uk>

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.

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
==================

Rik 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.
>
> Yup, or the shorter REPLACE INTO which does exactly the same. And in that
> case it can be done in one query, like:
> REPLACE INTO tabel (fields...)
> VALUES
> (val1.1,val1.2,val1.3,val1.4),
> (val2.1,val2.2,val2.3,val2.4),
> etc....
>
> People should use unique identifiers more...
> --
> Rik Wasmus
Rik,

That's very useful to know. Thanks for your input. However, I am not
sure if I can get a list of rows that have been REPLACEd (Items that
the user already owns), and items that aren't valid in the items
reference table. The items added must be in the main reference table
(The table with 3600 items). Each of these has a unique ID, and if it
exists, it's added to the user table in the format in the post below.

Many thanks.

Daz.

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.