Results 1 to 3 of 3

Thread: MySQL query help

  1. #1
    Join Date
    May 2007
    Location
    Netherlands, Amersfoort
    Posts
    2,704
    Mentioned
    1 Post(s)
    Quoted
    0 Post(s)

    Default MySQL query help

    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:

    Code:
    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
    PHP Code:
    CREATE TABLE IF NOT EXISTS `users` (
      `
    idint(11NOT NULL auto_increment,
      `
    usernamevarchar(50NOT NULL default '',
      `
    realnamevarchar(50NOT NULL default '',
      `
    passwordvarchar(50NOT NULL default '',
      
    PRIMARY KEY  (`id`)
    TYPE=MyISAM AUTO_INCREMENT=54 ;

    CREATE TABLE IF NOT EXISTS `user_rank` (
      `
    idint(11NOT NULL auto_increment,
      `
    user_idint(11NOT NULL default '0',
      `
    rank_idint(11NOT NULL default '0',
      
    PRIMARY KEY  (`id`)
    TYPE=MyISAM AUTO_INCREMENT=;

    CREATE TABLE IF NOT EXISTS `ranks` (
      `
    idint(11NOT NULL auto_increment,
      `
    titlevarchar(30NOT NULL default '',
      `
    colorvarchar(6NOT NULL default '',
      `
    imagevarchar(50NOT NULL default '',
      `
    primairint(11NOT NULL default '999',
      
    PRIMARY KEY  (`id`)
    TYPE=MyISAM AUTO_INCREMENT=
    So for example if I want to load all primair info for user $1;

    and that should return me something like

    PHP Code:
    $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.

  2. #2
    Join Date
    Sep 2006
    Posts
    6,093
    Mentioned
    77 Post(s)
    Quoted
    43 Post(s)

    Default

    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

  3. #3
    Join Date
    May 2007
    Location
    Netherlands, Amersfoort
    Posts
    2,704
    Mentioned
    1 Post(s)
    Quoted
    0 Post(s)

    Default

    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 just thanks!
    Last edited by MasterKill; 04-03-2010 at 07:49 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •