PDA

View Full Version : MySQL query help



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.

nielsie95
04-02-2010, 06:43 PM
SELECT users.*, ranks.* FROM users LEFT JOIN user_rank ON users.id = user_rank.user_id LEFT JOIN ranks ON user_rank.rank_id = ranks.id WHERE users.id = '1' ORDER BY ranks.primair DESC LIMIT 1

MasterKill
04-02-2010, 07:29 PM
Omg great, worked like a charm!

I hope I can do those myself in the future, thanks for the quick help man!

This may be closed now ;)

EDIT: oh I only had to remove the DESC since the lowest value should be the primair one, you couldn't know :p just thanks!