http://dev.mysql.com/doc/mysql/en/insert-select.html
The other "stuff" is just regular queries. Are you stuck on how you would write the query?
To add members from a group that are not already tagged by a person, you want to left join the membertagset table with the members table and test for a NULL tag id. For example:
SELECT members.member_id FROM members LEFT JOIN membertagset ON members.member_id= membertagset.member_id WHERE membertagset_id IS NULL AND other filter(s)
UPDATEs and DELETE support joins on other tables, so anything you can do in a SELECT you can also do in an UPDATE or DELETE and only affect the table(s) you specify (untagging).
On May 9, 2005, at 2:32 PM, Mike Zornek wrote:
I'm implementing a tagging system in an online db app I've built. The idea
is that users of the system can "tag" other members and then do a search to
find all members who are tagged. To implement this I've created a table:
membertagset
+-----------------+------------------+-----+---------+----------------+ | Field | Type | Key | Default | Extra | +-----------------+------------------+-----+---------+----------------+ | membertagset_id | int(10) unsigned | PRI | NULL | auto_increment | | member_id | int(10) unsigned | MUL | 0 | | | taggedmember_id | int(10) unsigned | | 0 | | +-----------------+------------------+-----+---------+----------------+
member_id is the user of the system taggedmember_id is the person they have tagged.
Only members who are tagged are represented in the table as a row.
Doing singleton updates (toggling a member's tagged state for user X is
pretty simple and fast). However I need to offer the ability for user to tag
all members within a search result. My PHP will hand off a list of the
members in the current result list as $found_member = "1,2,3,4,5";
I'm wondering if there is anyway I can construct a single SQL query that
would do a SELECT to find all of the members who are not tagged by the
current user and do inserts to the membertagset table to make sure those who
aren't end up tagged.
In a similar fashion I also need to offer the ability for them to untagg all
members found. So I'd need to do a select based on the found ID and delete
all rows that were returned.
I'm doing this now in PHP using multiple SQL calls but when I get into the
1,000 of members it's taking longer that I can accept. Any help is
appreciated. Thanks!
~ Mike Zornek ----- Shameless promotion Personal site: <http://MikeZornek.com> Philly Apple Devs: <http://PHAD.org> ADHOC / MacHack (July 27-31): <http://www.adhocconference.com>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
