A Question of design.

[Daz]

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.

[Chung Leong]

Re: A Question of design.

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.

[Daz]

Re: A Question of design.


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

[Chung Leong]

Re: A Question of design.

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.

[Rik]

Re: A Question of design.

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

[Peter Fox]

Re: A Question of design.

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@eminen t.demon.co.uk.n ot.this.bit.no. html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.dem on.co.uk>

[Daz]

Re: A Question of design.


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.

[Jerry Stuckle]

Re: A Question of design.

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@attgl obal.net
=============== ===

[Daz]

Re: A Question of design.


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.

[Daz]

Re: A Question of design.


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.

[Daz]

Re: A Question of design.


Peter Fox wrote:

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).

That's only part of it. I also need to check if the user has a
corresponding row for each item they submit. If not, I add the
corresponding row pending a check to ensure that the item is in fact a
valid item in the database.

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.

I am not sure if that would work, as the user submits a list of items
by name. If it's a valid item in the database, a user row is added.
column 1 continaing the users uinique ID, which can occur more than
once in the table, (once for each item). The ID of the item is
currently worked out whilst using array_search() on the database items.
If it's in the array, the item ID is returned and a row is added for
the user, for that item. If it doesn't exist, no key is returned, and
the user is advised that it's not a valid item.

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.

That would probably work with item IDs, however, it wouldn't work using
the item names which is what the user submits.

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

Yes, the results are sorted, but my the item name.

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

At present:
A list of items is derived from the user submited list as the form of
an array.
A query is dynamically created, so that only the items in the users
list are pulled from the database (as well as whether they own the item
or not).
The users list is then iterated through, and compared to the items
pully from the database using array_search().
If the item is there, then it's valid, so I check if the user has the
item. If they don't have it, I add it to another array from which the
INSERT query is made from.
If they do have it, then a counter is incremented which counts how many
items are valid, but are already owned by the user.
If array_search() returns 'false', then the item is not valid. Another
array is added to, which is iterated through and displayed to the user
to let them know which items aren't in the main database reference
table.

Hope this makes sense.

[Daz]

Re: A Question of design.


Daz wrote:

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.

I think that there is a lot of confusion here. Some people are not
getting the right idea. Probably because my attampt to explain failed.

=============== =============== =========

Here is a small snippet from my main database reference table. They are
virtual books, and each book has an id. The last column is not needed
for this operation:

+---------+--------------------------------+------------+
| book_id | book_name | is_retired |
+---------+--------------------------------+------------+
| 2 | 13 Banlow Street | 1 |
| 299 | Baseball Fans | 1 |
| 471 | Cherry Blossoms | 0 |
| 665 | Down by the River | 1 |
| 1181 | I will always Remember | 0 |
| 1339 | Kimbler: The Beginning | 0 |
| 1433 | Let the Game Begin | 0 |

And so on... There are just over 3600 entries at present.

=============== =============== =========

Here is a small extraction from the users table, which shows which
books the user owns.

+-----+---------+
| uid | book_id |
+-----+---------+
| 3 | 3194 |
| 2 | 2947 |
| 3 | 2091 |
| 3 | 307 |
| 3 | 1434 |
| 4 | 3278 |
| 3 | 1288 |
| 2 | 3239 |
| 3 | 2467 |
| 1 | 991 |
+-----+---------+

Remember. Neither of these columns contain unique values.

=============== =============== ==========

Finally, here is an example of my users table:

+---------+-------------+
| user_id | username |
+---------+-------------+
| 1 | Anonymous |
| 2 | user_1 |
| 3 | user_2 |
+---------+-------------+

=============== =============== ==========

As you can see. I didn't want to add more than 3600 columns to the
users book table, as this would mean I get a lot of NULLs which isn't
very efficient and also bring with it a few more down sides.

The only other option I have, is to create a table for each book. That
would mean more than 3600 tables, and I would have to search through
3600 tables to get the results I am looking for. This is totaly
unefficient for what I need to acheive, however, the I _would_ be able
to run an index on the user ID. :P

I hope that my explanation as to what I am trying to do, and why I have
done what I have so far, is addequate. It has taken me a long time to
get the database like this (as this was one of my first ever
databases), and it has been reborn several times after I discovered how
database normalization works, along with it's benfits.

Best wishes.

Daz

[Jerry Stuckle]

Re: A Question of design.

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
>>>>>statemen t from within a SELECT statement. Nor can you execute several
>>>>>statements , such as multiple UPDATE statements or several INSERT
>>>>>statemen ts 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@attgl obal.net
=============== ===

[Daz]

Re: A Question of design.

So, validate the names. Then use Chung's process for updating your

table with the ones which are valid.

Hi Jerry.

Thanks for that.

This brings be back to my original question.

Is the best way to validate the names, by having the db names in one
array, and the users books in another, and iterating through the users
array using in_array() to check if it's a valid book in the db array.
Or would it be better to put all of the database items into a comma
separated string, and then iterate through the user array using
preg_match() or maybe even strtr().

Perhaps the difference is negligable. But that is what I'd like to find
out. If it's something that no-one knows the answer to, I am happy to
set up a test, but I didn't see any point in 'reinventing the wheel' so
to speak, if someone already knew the answer.

Many thanks for your input.

Daz.

[Chung Leong]

Re: A Question of design.

Daz napisal(a):

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.

I'm not terribly familiar with MySQL. I think it supports multi-column
unique constraint. So in your case, you're force the uid + item_id
combination to be unique.