Best way to approach user specific settings in this case?

[Jade Elizabeth]

So for my works section that I am building into SMF there's a field called id_trigger. That field will hold any possible triggers, say "5, 14, 3" and I want to use this to consider whether or not the work should be displayed to the viewer (a trigger is something that will trigger an emotional response that is unfavourable for the user, such as terrorism, rape, child abuse/abduction might trigger PTSD in some people, this is very important for us to consider).

I am wondering how and where to store what a user has selected they're okay with seeing. My triggers database looks like (small sample):


   
       
   
       
       
       
       
       

id_trigger	trigger_name	trigger_desc
1	Abuse (women/children/animals)	Abuse directed towards women, animals and children whether physical, emotional, or sexual. Does not include verbal unless it is the theme of the piece.
2	Drug/Alcohol Use	Any use of drugs or alcohol. Including prescription drugs and drug/alcohol effects such as getting high, withdrawals, drunkenness or actions performed while under the influence of either.
3	Gore/Violence	Graphic or extreme gore or violence.
4	Domestic Violence	Violence occurring in domestic situations.
5	Terrorism	Terrorism including organised, lone assailants. This involves anything meant to cause fear including shootings (school, other), bombings, and hijackings, kidnappings/hostages, and other terrorist activity.

I don't think it would be a good idea to put it in the settings table unless I could have it with commas, so say userid 1 doesn't want to see 1, 3, 5 sort of thing. I'm wondering if I should put it into settings or my own table, and how to approach it in the query? I need to keep in mind that guests will only see 0 for is_adult and id_trigger, where as members will see a piece ONLY if the triggers DO NOT match any one of the triggers they don't want to see. So if they don't want to see 1, 3, 5 and the piece has 1, 2, 4 they will not see it because it has trigger 1 in the list.

I was thinking maybe I could do it like this? How should I approach this?

Code Select Expand

SELECT
w.id_work,
w.id_type,
w.id_member,
w.work_title,
w.work_cap,
w.id_series,
w.id_triggers,
w.is_adult,
w.poster_time,
u.filetype,
u.location,
m.real_name
FROM
smf_works_uploads as u
LEFT JOIN
smf_works_works AS w ON (w.id_work = u.id_work)
LEFT JOIN
smf_members AS m ON (m.id_member = w.id_member)
WHERE
{query_see_adult}
AND
{query_see_triggers}
ORDER BY
w.id_work DESC

[kelvincool]

I think it should be in it's own table, the settings table should be used for global settings not user specific settings, imagine how big that table will get if every user gets a row.

You could get away with having an additional column in members table. That will complicate your query of course. The alternative is to make it load your new column into $user_info and then you can simply use an NOT IN clause in your sql.

[Arantor]

Then of course there is the themes table which also holds user preferences and custom field values, which get autoloaded for a user.

[kelvincool]

Cool didn't know about that table, that could come in handy, seems like that's the better way to go then instead of a new table.

[Arantor]

Yup, it's where you can store an arbitrary amount of rows per member and optionally per theme as well should you want to do so.

[Jade Elizabeth]

Then of course there is the themes table which also holds user preferences and custom field values, which get autoloaded for a user.

That's what I meant by settings ha ha. Sorry!

How can I use this table for it and check the setting? I want to be able to do it on the query level .

[Arantor]

Sorry I mentioned it.

[Jade Elizabeth]

Huh?

[Arantor]

The point is that you shouldn't do it at the query level.

[Jade Elizabeth]

Why? I'd like to do it like this...

Code Select Expand

SELECT
w.id_work,
w.id_type,
w.id_member,
w.work_title,
w.work_cap,
w.id_series,
w.id_triggers,
w.is_adult,
w.poster_time,
u.filetype,
u.location,
m.real_name
FROM
smf_works_uploads as u
LEFT JOIN
smf_works_works AS w ON (w.id_work = u.id_work)
LEFT JOIN
smf_members AS m ON (m.id_member = w.id_member)
WHERE
{query_see_adult}
AND
{query_see_triggers}
ORDER BY
w.id_work DESC

What's wrong with that? SMF does it for what boards you can see so I thought it would be fine.

[Arantor]

1. It's a performance bottleneck.
2. It's limited and doesn't scale beyond maybe 80 items.
3. There are other reasons you don't do what you think you're trying to do but you've already made it clear that you're not actually that interested in learning how these things actually work, just in getting a solution that 'works'.

[Jade Elizabeth]

2. It's going to hold probably 20 or 30 items.
3. I am asking why it's not okay because I want to learn, I haven't learned why it's not okay or why it's a bottleneck or anything yet.