Selon W Luke <[EMAIL PROTECTED]>:
> Hi,
>
> Have struggled with this all day, and didn't know where else to ask.
> If it's not appropriate to the list, I apologise - and advice or
> pointers would be brilliant, as my head is now hurting!
>
> So here's the situation:
>
> 3 tables. f_u_groups f_groups and f_images
>
> f_u_groups contains the user's groups with the following columns:
> fuid, fugids, funame
>
> fuid is the primary key (auto incremented), funame is the name of the
> user-group and fuguids contains a comma-separated list of groups (eg
> 12,13,14,15).
>
> This column (fuguids) corresponds to the column gid (primary key) in
> f_groups. In plain English, fuguids contains a list of groups (for
> each user), which are then listed in the table f_groups.
>
> When images are inserted into f_images (I'm using PHP, and mysql's
> "unique" index to prevent duplicates), I need to check against the
> other two tables to make sure that the *group* the image is labelled
> as is "active." And by active, I mean whether a group is listed
> inside *any* of the comma-separated lists in fugids.
>
> To insert images, a loop is performed over the f_groups table - so
> this is where I need the "magic SQL" which I can't work out. Loop
> over each row in the f_groups - check f_groups.gid *inside*
> f_u_groups.fugids.
>
> I thought IN would do it:
>
> SELECT group_name, gid, fugids
> FROM f_u_groups, f_groups
> WHERE gid
> IN (fugids)
>
> But that only cross-referenced the first number in the comma-list
> (fugids) with the groups from f_groups. So it returned 2 group names.
>
> Sorry for the *MASSIVELY* and overtly-complicated message. I hope it
> a) explains my problem and b) someone can decode it!
>
> PS incidentally, I could do this quite easily in PHP...using loops,
> and querying on each number in the lists. But this is obviously
> massively ineffecient - up to 40 queries for just one simple request!
> --
> Will
> -- The Corridor of Uncertainty --
> -- http://www.cricket.mailliw.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Hi Will,
I think your're in a situtation like the example i give. The problem is work
with a join-like between int and varchar. The idea is to split the ugid to a
list of integers.
mysql> select * from groups;
+------+
| gid |
+------+
| 1 |
| 2 |
| 3 |
| 15 |
| 9 |
+------+
5 rows in set (0.00 sec)
mysql> select * from groupsList;
+---------+
| ugid |
+---------+
| 1,2,3,4 |
| 2,5,6 |
| 3,4,5 |
| 7,4,9 |
+---------+
4 rows in set (0.00 sec)
mysql> SELECT *
-> FROM groups
-> WHERE gid
-> IN (select ugid from groupsList);
+------+
| gid |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
You can see that 15 is not found, but 9 also. This is because gid is an int, and
ugid is (certainly like yours) a varchar.
what i thought to di is transforming the list of varchar to an array in which
mysql must look for the gids.
I then write you this query which should give you the idea, and i think the
solution :
mysql> select gid,ugid,FIND_IN_SET(gid,ugid) from groups,groupsList
-> where FIND_IN_SET(gid,ugid) > 0;
+------+---------+-----------------------+
| gid | ugid | FIND_IN_SET(gid,ugid) |
+------+---------+-----------------------+
| 1 | 1,2,3,4 | 1 |
| 2 | 1,2,3,4 | 2 |
| 3 | 1,2,3,4 | 3 |
| 2 | 2,5,6 | 1 |
| 3 | 3,4,5 | 1 |
| 9 | 7,4,9 | 3 |
+------+---------+-----------------------+
6 rows in set (0.00 sec)
Et voilà.
Hope that helps
:o)
Mathias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
