Select [left Join] [1$ reward]

Dev Shed Forums Sponsor:

December 14th, 2012, 05:31 AM

SticKyWoX

Registered User

Join Date: Dec 2012

Posts: 4

Time spent in forums: 50 m 16 sec
Reputation 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

December 14th, 2012, 05:51 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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.

December 14th, 2012, 06:21 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,869

Time spent in forums: 1 Month 2 Weeks 1 Day 22 h 57 m 55 sec
Reputation Power: 813

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 ...

December 14th, 2012, 06:22 AM

SticKyWoX

Registered User

Join Date: Dec 2012

Posts: 4

Time spent in forums: 50 m 16 sec
Reputation 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.

December 14th, 2012, 06:37 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

But the second code is wrong. Your LEFT JOIN is in effect rendered as an INNER JOIN. Hence my amendment.

December 14th, 2012, 06:43 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,869

Time spent in forums: 1 Month 2 Weeks 1 Day 22 h 57 m 55 sec
Reputation Power: 813

Quote:

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.

Quote:

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).

December 14th, 2012, 06:47 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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';

December 14th, 2012, 08:18 AM

SticKyWoX

Registered User

Join Date: Dec 2012

Posts: 4

Time spent in forums: 50 m 16 sec
Reputation 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 '='

December 14th, 2012, 08:40 AM

SticKyWoX

Registered User

Join Date: Dec 2012

Posts: 4

Time spent in forums: 50 m 16 sec
Reputation Power: 0

Sorry for dp (not double penetration, you pervert!).

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

Select [left Join] [1$ reward]

Developer Shed Advertisers and Affiliates