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

Jeff North wrote:
> 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.
Wow! I am most impressed. I had no idea that I could do that with
MySQL. I will be looking into this in more detail over the next few
days.

Many thanks for your input.

Daz.

[Back to original message]