Joining Tables

[Pioden]

Hi folks,

 

I'm having a bit of a 'mare this morning and would very much appreciate a little help. I have two tables: users and options.

 

Options is a little like this:

Value        Option

  1            Veg

  2            meat

  3            Ice cream

 

etc etc

 

The users table is pretty simple in data terms

 

Username        Email        Option 1  Option 2  Option 3

Fred              fred@            1            2          3

 

The values of Option 1 - 3 correspond with their chosen options. I'm trying to write a query which lets me list my users and their chosen options - but with the option values replaced with the appropriate words.

 

Simple? I'm sure it is but my brain can't figure it today. Been turning in LEFT JOIN circles for over an hour !!

[gizmola]

Yes, this is what happens when you don't have normalized table design.  In users, your Option1, 2 and 3 columns are a repeating group.  The proper design involves a many to many resolver table between Options and users, because that's what the real relationship is:  Users >---

 

There are definately queries that you will find difficult if not impossible to do with that structure, in particular -- find all the users who have one particular option set, or worse, have 2 options in some combination.

 

With that said, here is how you go about accomplishing what you asked for with a structure similar to what you described.

 

mysql> select u.*, o1.options as option1, o2.options as option2, o3.options as option3 
    -> FROM Users u, Options o1, Options o2, Options o3
    -> WHERE o1.val = u.Option1 AND o2.val = u.Option2 AND o3.val = u.Option3;
+----------+---------------+---------+---------+---------+-----------+-----------+---------+
| Username | Email         | Option1 | Option2 | Option3 | option1   | option2   | option3 |
+----------+---------------+---------+---------+---------+-----------+-----------+---------+
| Bob      | bob@there.com |       3 |       1 |       5 | Ice Cream | Veg       | Bread   | 
| Fred     | fred@here.com |       2 |       4 |       6 | Meat      | Pop Tarts | Candy   | 
+----------+---------------+---------+---------+---------+-----------+-----------+---------+
2 rows in set (0.00 sec)

mysql> select * from Options;
+------+-----------+
| val  | options   |
+------+-----------+
|    1 | Veg       | 
|    2 | Meat      | 
|    3 | Ice Cream | 
|    4 | Pop Tarts | 
|    5 | Bread     | 
|    6 | Candy     | 
+------+-----------+
6 rows in set (0.00 sec)

mysql> select * from Users;
+----------+---------------+---------+---------+---------+
| Username | Email         | Option1 | Option2 | Option3 |
+----------+---------------+---------+---------+---------+
| Bob      | bob@there.com |       3 |       1 |       5 | 
| Fred     | fred@here.com |       2 |       4 |       6 | 
+----------+---------------+---------+---------+---------+
2 rows in set (0.38 sec)

 

 

[Pioden]

Thanks for the reply Gizmola. OK so my table design seems to be flawed - can someone suggest how I should reconfigure the tables to make this work properly? Now is the time to do it before any serious data gets involved!

 

The important thing is that users can choose three out of the ten options listed in table 'Options'.

 

 

[Pioden]

Bump! Any suggestions?

[Maq]

There is a method in creating a scalable easy to maintain database.  It's called Database Normalization.

[Pioden]

Why did you bother Tim? What the hell was the point of that reply? Abso-f'in- useless. With cretins like you around we may as well all answer RTFM to every question and close the forum. A smart Alec reply from ... ach, what's the point? You're probably too arrogant to understand.

 

The point of a forum like this is that people can ask questions to others - and hopefully get some sensible answers. I made a mistake with my design and could have done with a little help. Eejit.

 

Have a nice day.

[Mchl]

Are you feeling OK? You get a link to a tutorial explaining what you should do and it makes you unhappy? Would you expect someone to post the contents of this tutorial here? To do all the work for you? Don't be surprised if next time all you get is a link to www.google.com

[Maq]

Why did you bother Tim? What the hell was the point of that reply? Abso-f'in- useless. With cretins like you around we may as well all answer read the manual to every question and close the forum. A smart Alec reply from ... ach, what's the point? You're probably too arrogant to understand.

 

The point of a forum like this is that people can ask questions to others - and hopefully get some sensible answers. I made a mistake with my design and could have done with a little help. Eejit.

 

Have a nice day.

 

I was implying that you should normalize your tables before you have a debacle on your hands.  There's no point in writing a query when you don't even know how to design a proper database.  A bad design will only lead to larger problems in the future.  But you know all this already, right?  So if you want to rig up your script to compensate for your poor design then, by all means, let's go right ahead, I love hacking up other people's code.

[Pioden]

Are you feeling OK?

No. I am completely fed up with seeing answers like this on 'self-help' forums.

 

You get a link to a tutorial explaining what you should do and it makes you unhappy?

Do you go around posting www.php.net as the answer to every PHP question? But you can get most of the answers you need by looking there ... Do you get my point?

 

I am familiar with database design - having taught myself over a period of years from books and on-line articles. What I did this morning was a mistake - but what turned out to be an interesting one. Many to many relationships are difficult to manage and sometimes unavoidable - that is the situation I have. I either have to devise a simpler way of handling the data or introduce an extra table to handle the many to many relationship ... Hardly 'total beginner' stuff. Your reply was exactly as helpful as giving me a link to Google - no difference.

 

[Mchl]

 

Do you go around posting www.php.net as the answer to every PHP question? But you can get most of the answers you need by looking there ... Do you get my point?

 

If you review my posts you'll see that's very often what I do. We even have special bbcode tag for that.

 

I am familiar with database design - having taught myself over a period of years from books and on-line articles. What I did this morning was a mistake - but what turned out to be an interesting one. Many to many relationships are difficult to manage and sometimes unavoidable - that is the situation I have. I either have to devise a simpler way of handling the data or introduce an extra table to handle the many to many relationship ... Hardly 'total beginner' stuff. Your reply was exactly as helpful as giving me a link to Google - no difference.

 

Well then. Hear my advice. Go with many-to-many relationship. It is not that difficult to manage, and it works well.

[gizmola]

Ok, so I understand your frustration, but in fefense of Mchl, he's posted 4k times here, and many of the posts have offered in depth help.  I think he was trying to answer your question.  No harm no foul --- let's try and bring this back to a constructive conclusion.

 

What you need is pretty simple -- a table between Users and Options.  I will typically call a table like that UserOptions or something along those lines.  That table needs only the keys to the tables it sits between:

 

In your case, it appears that the primary key of Options is the "val" column and the primary key of users is username. 

 

userOptions

----------

username 

val

 

Both columns should be part of the Primary key.

 

I did cover this topic fairly well some years ago in a tutorial series i wrote for phpFreaks, but sadly that went into the bitbucket.  There is a .pdf version of the series, and I have some code and at least one way to handle the UI, so you can get an idea of the SQL issues.  You can skip to part 2 & 3 if you want, as that's where i talk about many to many resolution and the physical tables and SQL joins needed to make that stuff work.  The series in .pdf and the code is all available here -> http://www.gizmola.com/blog/index.php?serendipity[subpage]=downloads&thiscat=6&file=20

 

Now in terms of only allowing 3 options, that is something that you will have to handle in code, because the many to many has no such limitation.  For example, one really simple way is to simply have the UI handle this as 3 seperate drop down list boxes.  Or, you can go for a more complicated UI widget as described.  Probably the simplest code, is that when any change is made through the UI, you delete all the options for that user and create the new ones based on the UI.  Technically it will not be possible due to Primary key uniqueness, for a person to have 2 options the same, using this scheme, so that is also something you probably want to check in your validation code.  Another option is to write a mysql trigger that enforces your 3 or less rule.  I'd probably opt for doing the checking in a validation routine personally.