Posted by Daz on 10/24/06 10:54

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.

[Back to original message]