|
|
Posted by Jeff North on 10/24/06 15:12
On 24 Oct 2006 04:18:56 -0700, in comp.lang.php "Daz"
<cutenfuzzy@gmail.com>
<1161688736.544747.168810@e3g2000cwe.googlegroups.com> wrote:
>|
>| 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.
First you need to find which books the user already has selected
SELECT GROUP_CONCAT(book_id SEPARATOR ',') AS GCR
FROM user_table
WHERE uid=3
GROUP BY uid;
save the value of GCR (group_concat result) field to a variable
The above result will produce a comma separated list.
Next if you want to list books that the user HASN'T selected (recently
added books) then you can use:
SELECT *
FROM book_table
WHERE find_in_set(book_id,'".$GCR.'")=0;
If you want to list the books the use HAS selected then use:
SELECT *
FROM book_table
WHERE find_in_set(book_id,'".$GCR.'")>0;
If you want to present the user with the entire list then you can use:
SELECT *, find_in_set(book_id,'".$GCR.'") AS FIS
FROM book_table;
In your code you can check the FIS field for 0 or non-zero. If the FIS
is zero the you could add a check box next to the book title to allow
the user to select it. If the FIS > 0 then just present the book title
as plain text, thus visually indicating to the user that they have
already selected this item.
When the user submits the request then you know only NEW entries have
been selected and can simply use a INSERT INTO statement.
HTH
---------------------------------------------------------------
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Navigation:
[Reply to this message]
|