Page 1 of 1
Joining columns from one table to another
Posted: Sun Jul 19, 2009 10:46 am
by ben.artiss
Hi everyone,
I've had a look at left/right/inner joins before and they proved quite useful (however confusing), so I've reduced my user's table to contain fewer fields with the intent of joining name-value pairs from a different table but I'm completely stumped on where or how to join the data. The 'users' table contains the usual 'user_id','username','email' etc. for registering/logging in, and I have added a new 'user_data' table with the following structure:
Code: Select all
CREATE TABLE `user_data` (
`umeta_id` int(8) NOT NULL auto_increment,
`user_id` int(8) NOT NULL,
`name` varchar(255) NOT NULL,
`value` varchar(255) NOT NULL default '0',
PRIMARY KEY (`umeta_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
So, say I have a user where user_id=1 and a row in user_data with `user_id`=1,`name`='forename' and `value`='Ben'. Is there a way of doing the following in a single SQL statement using join to get the same effect?
Code: Select all
...
// First select the user
$sql = "SELECT * FROM `users` WHERE `user_id`='".$userid."' LIMIT 1";
$res = mysql_query($sql);
$data = mysql_fetch_assoc($res);
// Then select the user_data
$sql = "SELECT `name`,`value` FROM `user_data` WHERE `user_id`='".$userid."'";
$res = mysql_query($res);
while ($row = mysql_fetch_assoc($res)) {
$data[$row['name']] = $row['value'];
}
// Data now contains all user data from two tables
print_r($data);
...
Any help would be appreciated!
Thanks, Ben
Re: Joining columns from one table to another
Posted: Sun Jul 19, 2009 11:57 am
by Christopher
Code: Select all
$sql = "SELECT users.*,`user_data.name`,`user_data.value` FROM `users` JOIN `user_data` ON `user.id`=`user_data.user_id` WHERE `users.user_id`='$userid' LIMIT 1";
Re: Joining columns from one table to another
Posted: Sun Jul 19, 2009 12:21 pm
by califdon
Ben, I'd like to offer a word of advice about table structures. In relational databases, table structures should not be arbitrary, based on what may appear to you as convenient. There are well-established, strict rules that govern what fields need to be in what tables. Splitting a table because of the number of fields is asking for trouble. I suggest that you would benefit from reading about relational table design and data normalization. It is rather theoretical and perhaps dull, but once you have a firm understanding of how relational databases operate, it will repay you for your effort, believe me. Some suggested readings, to start:
http://www.geekgirls.com/databases_from_scratch_3.htm
http://www.blueclaw-db.com/tabledesignaccess/
Re: Joining columns from one table to another
Posted: Sun Jul 19, 2009 2:22 pm
by Darhazer
arborint wrote:Code: Select all
$sql = "SELECT users.*,`user_data.name`,`user_data.value` FROM `users` JOIN `user_data` ON `user.id`=`user_data.user_id` WHERE `users.user_id`='$userid' LIMIT 1";
Actually the user_data contains multiple rows for one user, so this do not work
What he asks for, as I'm understanding it, and which seems impossible, is to have:
user_data
-----------------------
user_id | name | value
-----------------------
1 | one | 1
1 | two | 2
and the resulset:
one | two
----------
1 | 2
Re: Joining columns from one table to another
Posted: Sun Jul 19, 2009 2:41 pm
by califdon
I couldn't really follow what he is doing, but if your interpretation is correct, of course he doesn't even have a valid relational table. I wouldn't even try to repair that.
Re: Joining columns from one table to another
Posted: Tue Jul 21, 2009 7:17 am
by ben.artiss
Hi, thanks for the replies.
Soon after posting I indeed discovered only one row getting joined, and thank you also for pointing out it is impossible to do with multiple rows. I do understand which fields are important and must be kept together, hence removing the unnecessary ones and adding them as preferences rather than requirements, but thanks califdon for the links to some docs.
Just to run this by you in case you think I'm taking the wrong approach (or missing some vital fields), here are the user tables:
Code: Select all
CREATE TABLE `users` (
`user_id` int(8) NOT NULL auto_increment,
`group` enum('admin','moderator','member') NOT NULL,
`date_created` datetime NOT NULL,
`date_modified` datetime default NULL,
`date_activated` datetime default NULL,
`activation_code` varchar(255) default NULL,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`email` varchar(255) default NULL,
PRIMARY KEY (`user_id`)
)
CREATE TABLE `user_data` (
`udata_id` int(8) NOT NULL auto_increment,
`user_id` int(8) NOT NULL,
`name` varchar(255) NOT NULL,
`value` varchar(255) NOT NULL default '0',
PRIMARY KEY (`udata_id`)
)
Thanks again for the replies.
Regards, Ben
Re: Joining columns from one table to another
Posted: Tue Jul 21, 2009 12:40 pm
by califdon
I don't see why you are splitting the user data between two tables. Unless I've just missed something in your explanation, ALL of your fields are descriptors for the entity USER. If that is true, you are crippling yourself by not having them all in the same table. I hate to be redundant, but this is so crucial to making a database work properly: the design of tables is a rigorous and well-defined task and references to "preferences" is inappropriate to that task. There's a right way that adheres to the rules established by Dr. E. F. Codd back around 1970, and all the other wrong ways. There's very little room for anything in between. (I know I'll get flamed for that statement, but that's OK, I'm flame retardant.)
Re: Joining columns from one table to another
Posted: Wed Jul 22, 2009 7:16 am
by ben.artiss
OK thanks for the advice, I'm just going by the wordpress database simply because they use a `user_meta` table in the same fashion (and in fact have less columns in their `users` table than me!

). Nonetheless, thanks for clarifying the initial question.
Regards, Ben
Re: Joining columns from one table to another
Posted: Wed Jul 22, 2009 2:02 pm
by califdon
If you were talking about a table with 300 fields in it, some people might raise an eyebrow, but for anything short of that, trust me, the number of fields has nothing to do with anything! Get that out of your head! There is essentially no difference in performance. In fact, splitting a properly normalized table into 2 tables will slow down an application, for sure! Again, there are well-established rules for how to determine what tables are required and what fields belong in each table and they have nothing whatsoever to do with how many fields there are, they have to do with entities and attributes. There's a lot of good reading material available online. You would find it worthwhile to look for them and read them.