|
|
Posted by Jeff North on 10/24/06 20:48
On 24 Oct 2006 12:19:46 -0700, in comp.lang.php "Daz"
<cutenfuzzy@gmail.com>
<1161717586.796376.212650@m73g2000cwd.googlegroups.com> wrote:
>|
>| 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.
Since you are using a large number of entries the group_concat may
produce a very long field. I suggest you read up on this within the
manual (http://dev.mysql.com/doc/)
"In MySQL, you can get the concatenated values of expression
combinations. You can eliminate duplicate values by using DISTINCT. If
you want to sort values in the result, you should use ORDER BY clause.
To sort in reverse order, add the DESC (descending) keyword to the
name of the column you are sorting by in the ORDER BY clause. The
default is ascending order; this may be specified explicitly using the
ASC keyword. SEPARATOR is followed by the string value that should be
inserted between values of result. The default is a comma (`,'). You
can remove the separator altogether by specifying SEPARATOR ''. You
can set a maximum allowed length with the group_concat_max_len system
variable. The syntax to do this at runtime is as follows, where val is
an unsigned integer:
SET [SESSION | GLOBAL] group_concat_max_len = val;
If a maximum length has been set, the result is truncated to this
maximum length."
---------------------------------------------------------------
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Navigation:
[Reply to this message]
|