MySQL multipule options?

Not sure how to describe but here we go!

[Knowles]

I need to make a database the will have multipule options. I am thinking Join may be what I need but I am not sure yet. Here is what I am thinking:

id¦option1¦option2¦option3¦option4
1¦this¦that¦theother¦thensome
2¦that¦this¦theother¦thensome
3¦thensome¦theother¦that¦this
4¦this¦¦¦
5¦that¦¦¦
I would need to call these like this:

All
Only that
Only this
Only theother
Only thensome

Calling them is not a major issue. I need to figure out how to add them to the database, I do not want it to add "that" twice and I am not sure how to check to see if it needs to go into option1-4 ei: if option1 is already taken put in option2.

Any ideas on this?

[Knowles]

Just thought I had would I be better off forcing the options to be a value that way I dont have this issue?

[vincevincevince]

i stared at that for 10 mins... and i can't figure out what you're trying to do/ask, sorry!

can you clarify the situation?

[Knowles]

Its for a registration, you can register for up to 4 things. I am trying to figure out how to input the data into them. Rather I should enter them into a forced situation where as option1 can only be option1 instead of any of the 4. Sorry for it not making much sense thats part of the problem I am not in a complete understanding of how I want to set it up. I know what I want to do but cant seem to figure out what I mean.

[vincevincevince]

:)

use checkboxes, one for each option

then have three tables:
users (userid, name, etc etc etc )
options (optionid, optiontext)
subscriptions (userid, optionid)

users has the user data, with a unique userid

options is the list of possible options, each with unique id

subscriptions will have up to 4 entries per user :-) showing the user'sid and the option'sid

then you can query for a user's info:
WHERE users.userid=options.userid
or for subscribers to an option:
WHERE options.optionid=3

:D
how's that sound?

[vincevincevince]

---------for the form
$sql="SELECT optionid,optiontext FROM options WHERE 1 ORDER BY optiontext asc";
$dh=mysql_query($sql);
while ($r=mysql_fetch_row($dh))
{
echo "<span style=\"width: 48%; font-size: 12px;\"><INPUT TYPE=\"checkbox\" NAME=\"$r[0]\">$r[ 1]</span>";
}
-----------for the inserting (requires $userid to be set)
$sql="SELECT optionid FROM options WHERE 1 ORDER BY optiontext asc";
$dh=mysql_query($sql);
while ($r=mysql_fetch_row($dh))
{
if ($_REQUEST[$r[0]]) mysql_query("INSERT INTO subscriptions (userid, optionid) VALUES ($userid, $r[0]);");
}

Any help?

<edited to correct code>... help still buggy! where i put $r[ 1] above - take out that space... WW code is messing up

[edited by: vincevincevince at 4:22 pm (utc) on June 29, 2003]

[Knowles]

That sounds like what I will end up doing, well at least very close. Thanks man!