Storing yes/no questions and user interests in db

what's the best approach?

[phph]

Hi everyone,

I already posted this topic in Databases forum, but after waiting for reply for two days I decided to repost it here.

I need help choosing the way to store user interests and yes/no type questions into the USERS table of my site. I use MySQL + PHP.

For yes/no questions I was thinking about three methods:

ENUM('y','n')
TINYINT(1) (using 0 for no and 1 for yes)
SET (i got around 15 yes/no questions in all so they will fit into two bytes i guess)

So whats the best approach? I need to have that info retrieved from db when user logins and have it available on every page (maybe store them as SESSION vars), so that I can customize pages accordingly).

As to the interests, I was thinking about using a VARCHAR field and store them like [i1][i2][i5] etc. and check using strpos in PHP for example. Or maybe use the SET datatype? I think it's quite efficient, but will it let me select users that are interested in, say, i4 and i9 no matter what their other interests are? (if any).

All help is greatly appreciated.

[jatar_k]

it is a bit of a difficult question, a lot of it depends on personal preference imo

I use enum, I like enum and it allows my db data to be readable when I am checking things out at the commandline

it also depends on the logic you use, well maybe it doesn't actually because again it would depend on personal preference and style.

I don't think there is a blanket right answer to this.

[phph]

I have to agree it's not a question that has a straightforward answer, but again, I'm mainly interested in optimization, server load etc. Is keeping 40+ vars in the $_SESSION var a good idea when server is handling lots of sessions simultaneously? Also, is it secure enough?

[jatar_k]

if you need all 40 vars on every page, yes I would carry them around

if not, then you need to find the line between unnecessary queries and how much you want in the session. If you are carrying around sessions then I assume these are logged in users. Users expect a wait when they login so you have the advantage that you can do some extra work at that point and save time on the rest of your pages.

>> interested in optimization

you'd have to ask a normalization purist then I think. I don't see an issue with any of the ways you have laid out. They all work fine.

>> secure

you would have to be more specific about secure from whom/what and also what it is you are trying to secure.

[phph]

I ask for security because I'm not very sure how sessions work - are variables transferred to the user at some point, or only kept on server? Is it a good idea to load all user data directly from database (mysql_fetch_array) into $_SESSION for easy access, as that includes user's password (which is hashed, but anyway)? I also use an image verification script that stores the code that has to be entered into a $_SESSION var - is there a way that user can access it, making this verification useless?

All questions are a bit off-topic, but I really need answers and don't want to flood with new threads.

[jatar_k]

>> I'm not very sure how sessions work - are variables transferred to the user at some point, or only kept on server?

session dat is stored in files on the server, the sessionid is stored on the client side, usually in a cookie, this id is used to access the data on the server.

>> Is it a good idea to load all user data directly from database (mysql_fetch_array) into $_SESSION for easy access

not usualyy. As I mentioned above if there are things you need on every page, or at least a majority of pages, then it makes sense to load it into the session. This means you save yourself a db call on every page.

>> as that includes user's password (which is hashed, but anyway)?

you should never, ever need to store the password anywhere but in the db. Get it out of the session. The only time you need it is when they login, you then initialize their session and use portions of the session for continued authentication during their visit.

>> I also use an image verification script that stores the code that has to be entered into a $_SESSION var - is there a way that user can access it, making this verification useless?

no, the user can't get at the session data as it is stored on your server.

remember: sessions can be hijacked, this means someone could take the cookie from an authenticated user and if there session on your server is still active then they could gain access to their account/information.

try this PHP Library [webmasterworld.com] thread for some ideas for authentication
PHP User Authentication and Passwords [webmasterworld.com]

[phph]

Thanks a lot for the fast answers, jatar! I got one final question about sessions AND cookies - as you said, user authenticates with a cookie (without cookie support user's browser would pass the SID in the URL automatically, right?). But the session itself will be gone when the user closes window (or when session timeouts), so is there a simple way to give the user an option like "don't ask for password for 2 weeks" during login? I guess increasing session timeout isn't a good idea (no need to keep all those SESSION vars for two weeks), so maybe store additional info in the same cookie so that session will be automatically restored when user later visits the site (and vars again loaded into the SESSION from DB)?

I'm sorry this thread went off-topic, but I really need to know the answers to these questions. All help is greatly appreciated!

[jatar_k]

>> (without cookie support user's browser would pass the SID in the URL automatically, right?)

sort of, that is an option you can set in php.ini

>> But the session itself will be gone when the user closes window (or when session timeouts)

I think only after garbage collection will it truly be gone

I think the "remember me" options are set with cookies. You just set a cookie with a two week timeout. Whe they hit a page you would have to grab the cookie and then authenticate it.

[phph]

Thanks so much for all this! It really helped a lot.

[whoisgregg]

One note about storing answers to "Yes/No" questions... Don't forget about the user *not* choosing either option. You can validate that they answered or, if this applies, allow a value of "No answer provided."

When I allow the user to NOT answer, I prefer to use

enum('N','Y') default NULL

but that's just my personal preference.

If you want third form normalization (third form means no NULL values), you'd have a table of interests, a table of users, and a joining table that represents what interests each user has expressed a "yes" or "no" towards. But a normalized system like that (while robust) is rarely worth all the extra calls to the DB.