PHP mySQL NEED HELP!

gtops1

Hi Lads

Im new to mySQL and am having a bit of trouble trying to find the right sql select statement. My table contains username and clubsocs. clubsocs contains a set of numbers which correspond to check box options checked by the user:


username clubsocs

John Smith 13, 64, 27, 48


Mary Smith 11, 23, 56, 19


Just wondering if it is possible to select username and one of the corresponding clubsocs numbers as required so like select John Smith and option 3..which would pick John Smith and 27...

If anyone has any ideas on how to do this or knows if this is even possible id be very thankful..

Webmonkey

Why don't you read the whole column in as it not much extra work and then extract the element using PHP instead?

[php]
$selectOption = 3;
$returnedData; //Your data set from MySQL select in php.
$options = $returnedData;
$options = explode(', ', $options);
$selectedOne = $options[$selectOption];
[/php]

But what are you trying to achieve. This design looks like it doesn't adhere to first normal form, whatever about the rest. You should have atomic values in each column. You are grouping data in a single one which makes it very difficult and less efficient and less maintainable to select data. What if you must update the clubsocs for John Smith, are you going to have to read back out the string and then make changes?

You should probably have a clubsocs table, then a foreign key to this from your users table. But since you have multiple users at different clubsocs and vica versa, then you need a join table.

Users Table
id (pri) | name

---

1 | John Smith
2 | Mary something

Club socs
id (pri) | name

---

13 | club1
64 | club2
27 | club3
48 | club4

users_clubsocs table
userID (concat prikey) | clubID (concat prikey)

---

1 | 13
1 | 64
1 | 27
1 | 48

I don't like the way you are designing the DB, since this scenario should be avoidable. Am I making sense or have I mis interpreted what you saying.

gtops1

Cheers again webmonkey..Yeah im starting to think my tables are all wrong but unfortunately i think it may be too late to change them. I tried using the explode sql method but think i may be structuring it wrong..i would like to display the current user, say for example john smiths 3rd choice of content which has a cont_id of 12 which in turn display club 3s content..
////////////////////////////////////////////////////////////////////////////////////////////

$selectOption = 3;
$q=mysql_query("select username, cont_id, clubsocs,dtl from content,users

where content.cont_id=users.clubsocs and username='$session->username' order by users.clubsocs ");
$options = $q;
$options = explode(', ', $options);
$selectedOne = $options[$selectOption];


////////////////////////////////////////////////////////////////////////////////////////////


the user table

username clubsocs

John Smith 13, 64, 12, 48

Mary Smith 11, 23, 56, 16


the add_clubsocs table which corresponds to the checkboxes


cont_id clubsocs

10 club1
11 club2
12 club3
13 club4


the content table which stores each clubsocs content

cont_id dtl

10 club1 content
11 club2 content
12 club3 content
13 club4 content