|
|
Posted by Daz on 10/24/06 11:18
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
Navigation:
[Reply to this message]
|