#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0

    Select [left Join] [1$ reward]


    Hi there! I'm kinda new here, I and need some help. (yeah, bad start)

    How can I transform the following code:

    Code:
    $i = 0;
    $data0 = $DB->query("SELECT * FROM ".ACCOUNT_DATABASE.".refferals WHERE inviter = '".$_SESSION['user_data']['login']."'");
    $row0 = $DB->fetch($data0);
    $invited = $row0['invited'];
    $data1 = $DB->query("SELECT * FROM ".ACCOUNT_DATABASE.".account WHERE login = '".$invited."'");
    $row1 = $DB->fetch($data1);
    $id = $row1['id'];
    $data2 = $DB->query("SELECT * FROM ".PLAYER_DATABASE.".player_index WHERE id = '".$id."'");
    $row2 = $DB->fetch($data2);
    $pid1 = $row2['pid1'];
    $data = $DB->query("SELECT * FROM ".PLAYER_DATABASE.".player WHERE id = '".$pid1."'");
    into a singe variable $sql using LEFT JOIN?

    Like this:

    Code:
    $sql = "SELECT id, name, level, win, ladder_point, master, empire, rang
            FROM (
                SELECT id, name, level, win, ladder_point, master, empire, @num := @num +1 AS rang
                FROM (
                    SELECT player.id, guild.name, guild.level, guild.win, guild.ladder_point, guild.master, player_index.empire, @num :=0
                    FROM ".PLAYER_DATABASE.".guild
                    LEFT JOIN ".PLAYER_DATABASE.".player ON guild.master = player.id
                    LEFT JOIN ".PLAYER_DATABASE.".player_index ON player_index.id = player.account_id
                    WHERE player.name NOT LIKE '[%]%'
                    ORDER BY guild.ladder_point DESC , guild.level DESC 
                ) AS t1
            ) AS t2
            $where LIMIT $limit_start, $limit_end";
    I haven't used LEFT JOIN before, but now I'm forced to do it.

    PS: 1$ LR for who solves this.
    Last edited by SticKyWoX; December 14th, 2012 at 05:32 AM. Reason: reward
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    SELECT p.id
         , g.name
         , g.level
         , g.win
         , g.ladder_point
         , g.master
         , pi.empire
         , @num :=0
      FROM guild g
      LEFT 
      JOIN player p
        ON p.id = g.master 
       AND p.name NOT LIKE '[%]%'
      LEFT 
      JOIN player_index pi
        ON pi.id = p.account_id
     ORDER 
        BY g.ladder_point DESC 
         , g.level DESC
    If that helps, please consider donating your dollar to some deserving local charity, perhaps one for hard-up DBAs.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    @ cafelatte:

    His example code seems to be unrelated to his actual question. So it looks like you're not gonna be rich.



    @ SticKyWoX:

    Have you thought about trying it yourself first? Joins are no rocket science, just look for some tutorial to see how it works.

    By the way, why are you using multiple databases? That smells of bad design ...
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    You haven't understand. I need to transform the first code. The second code was just an example, it's not the same algorithm and hasn't any problem. The second one works fine. The first code needs to be transformed using LEFT JOIN.


    Jacques1: Of course I tried, but it seems that I've did some mistakes in the transformed code.

    It's a game database, that's why I use multiple databases.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    But the second code is wrong. Your LEFT JOIN is in effect rendered as an INNER JOIN. Hence my amendment.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by SticKyWoX
    Jacques1: Of course I tried, but it seems that I've did some mistakes in the transformed code.
    Then please show us what you've tried.



    Originally Posted by SticKyWoX
    It's a game database, that's why I use multiple databases.
    So? All related data is supposed to go in one database, possibly divided into different schemas (wait, I think MySQL doesn't have that).
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Notwithstanding Jacques1's remarks, this should point you in the right direction:

    Code:
    SELECT * 
      FROM refferals r
      LEFT
      JOIN account a
        ON a.login = r.invited
      LEFT
      JOIN player_index pi
        ON pi.id = a.id
      LEFT
      JOIN etc
     WHERE r.inviter = '$login';
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    That's my ****ty algorithm before reading your posts:

    Code:
    $sql = "SELECT id, name, level, playtime, inviter, invited, active, rang
            FROM (
                SELECT id, name, level, playtime, inviter, invited, active, @num := @num +1 AS rang
                FROM (
                    SELECT refferals.inviter, refferals.inviter, account.login, @num :=0
                    FROM ".ACCOUNT_DATABASE.".refferals
                    LEFT JOIN ".PLAYER_DATABASE.".player_index ON player_index.id = player.account_id
    				LEFT JOIN ".PLAYER_DATABASE.".player ON player.id = player_index.id
                    WHERE inviter =  '".$_SESSION['user_data']['login']."'
                ) AS t1
            ) AS t2";
    Now I'm trying to follow your instructions. "I'll be back."

    EDIT:

    Code:
    $sql = "SELECT * 
      FROM account.refferals r
      LEFT
      JOIN account.account a
        ON a.login = r.invited
      LEFT
      JOIN player.player_index pi
        ON pi.id = a.id
      LEFT
      JOIN player.player p
        ON p.id = pi.pid1
     WHERE r.inviter = '".$_SESSION['user_data']['login']."';";

    ^The code above isn't working. I look like a stupid, don't I? Thank you for your help, I'm sure that you've explained to me what I need to do but I'm very stressed and I learn very hard.

    2nd code result ( ran in Navicat ):
    Code:
    [Err] 1267 - Illegal mix of collations (big5_chinese_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Sorry for dp (not double penetration, you pervert!).

    Solved.
    Thank you very very much! All the best, SticKyWoX

IMN logo majestic logo threadwatch logo seochat tools logo