MasterKill
04-02-2010, 04:32 PM
I'm trying to create an advanced rank system on my clansite. But I can't figure out if this is possible with 1 query:
I would nee something like:
SELECT users.*, ranks.* FROM users LEFT JOIN ranks ON ? WHERE users.id = '1' LIMIT 1
? = where ranks.primair is the highest value. But this has to be done by looking at what ranks are linked to users.id 1
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(50) NOT NULL default '',
`realname` varchar(50) NOT NULL default '',
`password` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=54 ;
CREATE TABLE IF NOT EXISTS `user_rank` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`rank_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
CREATE TABLE IF NOT EXISTS `ranks` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(30) NOT NULL default '',
`color` varchar(6) NOT NULL default '',
`image` varchar(50) NOT NULL default '',
`primair` int(11) NOT NULL default '999',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
So for example if I want to load all primair info for user $1;
and that should return me something like
$user = mysql_fetch_assoc(mysql_query($query));
print_r($user);
{
id => 1
name => masterkill
title => administrator
color => D00000
// and some more obviously
}
So I want to select a user by id, and load the rank info where the stats.primair is the highest value, but It must be linked to the profile!
Help would be appreciated.
I would nee something like:
SELECT users.*, ranks.* FROM users LEFT JOIN ranks ON ? WHERE users.id = '1' LIMIT 1
? = where ranks.primair is the highest value. But this has to be done by looking at what ranks are linked to users.id 1
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(50) NOT NULL default '',
`realname` varchar(50) NOT NULL default '',
`password` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=54 ;
CREATE TABLE IF NOT EXISTS `user_rank` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`rank_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
CREATE TABLE IF NOT EXISTS `ranks` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(30) NOT NULL default '',
`color` varchar(6) NOT NULL default '',
`image` varchar(50) NOT NULL default '',
`primair` int(11) NOT NULL default '999',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
So for example if I want to load all primair info for user $1;
and that should return me something like
$user = mysql_fetch_assoc(mysql_query($query));
print_r($user);
{
id => 1
name => masterkill
title => administrator
color => D00000
// and some more obviously
}
So I want to select a user by id, and load the rank info where the stats.primair is the highest value, but It must be linked to the profile!
Help would be appreciated.