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;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);
...Thanks, Ben