problems modding forum notification hack...

This forum is now closed as part of retiring phpBB2.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

This forum is now closed due to phpBB2.0 being retired.
Post Reply
asinshesq
Registered User
Posts: 6266
Joined: Sun Feb 22, 2004 9:34 pm
Location: NYC
Name: Alan

problems modding forum notification hack...

Post by asinshesq »

I'm trying to mod a forum notification mod that I got from the phpbb hacks site. I've got everything working the way I want except that I need to create a simple table with columns called forum_id, user_id and notify_status and populate it with entries where
forum_id is anything that appears in the phpbb_forums table with a 1 in the 'forum_notify' column (a new column the basic mod adds) and, for each of those forum_ids, add all user_ids where the user has a group_id of 2 or 7 (in the regular phpbb_user_group table).

When I create this new phpbb_forums_watch table and try this:

$sql = "INSERT INTO phpbb_forums_watch (forum_id, user_id)
SELECT f.forum_id, u.user_id FROM phpbb_forums AS f, phpbb_user_group AS u
WHERE f.forum_notify = '1' and u.group_id = '2'";

that perfectly populates the table for all of the correct forum_ids and for all users in group_id 2.

But if I try to add an 'or' concept to the statement I get nowhere.

Specifically, if I try:

$sql = "INSERT INTO phpbb_forums_watch (forum_id, user_id)
SELECT f.forum_id, u.user_id FROM phpbb_forums AS f, phpbb_user_group AS u
WHERE f.forum_notify = '1' and u.group_id = '2' or '7'";

it doesn't work at all.

When that didn't work I tried to generalize and I created another table called phpbb_groups_to_notify with the group_ids I am interested in. I then tried this:

$sql = "INSERT INTO phpbb_forums_watch (forum_id, user_id)
SELECT f.forum_id, u.user_id FROM phpbb_forums AS f, phpbb_user_group AS u
WHERE f.forum_notify = '1' and u.group_id = any (select group_id from phpbb_groups_to_notify)";

and that didn't even get close to working.

I guess I'm missing something fundamental. Any ideas?
Graham
Former Team Member
Posts: 8462
Joined: Tue Mar 19, 2002 7:11 pm
Location: UK
Contact:

Post by Graham »

I would suggest that you split that into 2 queries. One to get the data you want to insert and then another to insert it. Trying to do it the way you are makes it very difficult to debug problems and potentailly DB dependant.
"So Long, and Thanks for All the Fish"

phpBB Useful Links: Knowledge Base | Userguide | Forum Search | MOD Database | Styles Database
My Links: Blog!
asinshesq
Registered User
Posts: 6266
Joined: Sun Feb 22, 2004 9:34 pm
Location: NYC
Name: Alan

Post by asinshesq »

Graham wrote: I would suggest that you split that into 2 queries. One to get the data you want to insert and then another to insert it. Trying to do it the way you are makes it very difficult to debug problems and potentailly DB dependant.


Thanks for the tip. So when I split into two queries, I would say something like this?:

Code: Select all

$sql = SELECT f.forum_id, u.user_id FROM phpbb_forums AS f, phpbb_user_group AS u 
WHERE f.forum_notify = '1' and u.group_id = '2' or '7'"; 

$sql = "INSERT INTO phpbb_forums_watch (forum_id, user_id)";


Does the Insert Into command automatically know that I want to insert what I just selected in the immediately prior querry?

And how about the syntax of the where clause in my select query -- is it ok to have an 'or' in there, or am I better off using the any command for the where clause like this:

Code: Select all

WHERE f.forum_notify = '1' and u.group_id = any (select * from phpbb_groups_to_notify)"; 
I prefer to use an 'any' command if I can figure out how to make it work, since then I would be able to generalize the script by putting in the groups_to_notify table whatever group_ids I want included from time to time.

Thanks again.
asinshesq
Registered User
Posts: 6266
Joined: Sun Feb 22, 2004 9:34 pm
Location: NYC
Name: Alan

Post by asinshesq »

Just to finish this topic off (in case anyone else has similar questions), these questions and their answers are explored and answered at http://forums.devshed.com/t128778/s.html
Post Reply

Return to “[2.0.x] MOD Writers Discussion”