[SOLVED] Join 3 tables

[sp@rky13]

ok so I've looked at some tutorials but they make it really annoying as they use tables with weird data in them that don't help lol. So can someone explain/ link me a good tutorial on joining 3 tables in mysql together?

 

What I would doing is linking: village$world.player = player$world.id AND player$world.ally = tribe$world.id

[sp@rky13]

Anyone able to just link me a tutorial on joining three tables?

[Andy-H]

Theres a tutorial on data joins on PHPfreaks...

 

http://www.phpfreaks.com/tutorial/data-joins-unions

 

[sp@rky13]

I've looked at that though it doesn't seem to help me . So what I need is to join 2 together from 2 tables and then join a different 2 things from the second table to another 3rd table. How can I go this?

[Philip]

I think it'd be best if you posted sample data from each table and how you wanted it joined.

[sp@rky13]

Ok what I need is to do this:

 

I need to find all the villages owned by a tribe for example. To do this I look up the tribe's tag ("tag" is the column name i need to look up in the tribe table). I then store the if of that tribe that's in the tribe table. i then look for all rows in the player table that have that id listed in their ally column. I then store the player's id (in the "id" column) and look it up in the "player" column of the village table. I then find all row's that have that result. I would then export the x and y columns from those rows

 

A whole lot of explaining but is really simple. I am currently using this:

 

<?php
$con = mysql_connect("********","********","***********");
if (!$con)
{
   die('Could not connect: ' . mysql_error());
}

mysql_select_db("*****", $con);

$result = mysql_query("SELECT c.player AS playerid, CONCAT_WS('|',c.x,c.y) AS xy FROM tribe$world a INNER JOIN player$world b ON a.id = b.tribe INNER JOIN village$world c ON b.id = c.player WHERE a.tag = '$vpt_sub' ORDER BY c.player") or die(mysql_error());

while($row = mysql_fetch_array($result))
{
echo $row['xy']." "; 
}
mysql_close($con);
?>

 

though as I don't understand it I would prefer to do it as a more simple join. Also, using a more simple join would allow more flexibility with it

[sp@rky13]

Ok, this is why I didn't postthis in mysql in the first place because no one will respond to it I can get a response in php very quickly whereas in the mysql section I dont get any because hardly anyone looks there. I mean the difference:

 

MYSQL now:

 

sp@rky13 and 12 Guests are viewing this board.

 

and PHP now:

 

sp@rky13, KingOfHeart, sasa, AlexWD, wee493, herghost (+ 1 Hidden) and 49 Guests are viewing this board.

[Zane]

show the schema of your tables.

 

something like this

mysql> explain products;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| type      | int(11)     | NO   |     | NULL    |                |
| imgurl    | varchar(70) | NO   |     | NULL    |                |
| dateadded | datetime    | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

[sp@rky13]

Sorry but huh ????? I don't understand. What would you like to know?

[Zane]

What would you like to know?

What do you tables look like?  What columns do you have?

[Zane]

Ok, this is why I didn't postthis in mysql in the first place because no one will respond to it I can get a response in php very quickly whereas in the mysql section I dont get any because hardly anyone looks there. I mean the difference:

 

MYSQL now:

 

sp@rky13 and 12 Guests are viewing this board.

 

and PHP now:

 

sp@rky13, KingOfHeart, sasa, AlexWD, wee493, herghost (+ 1 Hidden) and 49 Guests are viewing this board.

 

Posting your question in the most popular board isn't going to get your the best and mos accurate answer that you really need.  No matter how long you have to wait for a reply, you're thread will be read sooner or later.  Whether or not it's responded to is dependent upon your wording of the question.  Even if you had posted this in PHP Coding it would have been moved here anyway.. most likely.

 

On a side note: Hannah Montana's popular too.. you could try posting your question in her forums.

[sp@rky13]

Ok lol. Anyway, so my tables are as following:

 

"village" table:

id - the village's id
name - the name given to it
x - the x part of the coordinate (a number)
y - the y part of the coordinate (a number)
player - the player's id
points - the points of the village
rank - ignore that one

"player" table:

id  - id of the player (is the same as the player column in the village table)
name - the name of the player, words
ally - the id of the tribe that the player is in
villages - no. of villages
points - total points of the player
rank - the rank

"tribe" table:

id  - id of the tribe (same as ally column in player)
name - full name of the tribe
tag - shorter name of the tribe (this is what gets searched for)
members - no. of members
villages - no. of villages
points - no. of points of top 40 players
all_points - total points
rank - rank of tribe

There you go. So the links between tables are as follows:

 

The player column in the village table is the same as the id column in the player table.

 

The ally column in the player table is the same as the id column in the tribe table

[kickstart]

Hi

 

In very simple terms:-

 

SELECT *
FROM village a
JOIN player b
ON a.player = b.id
JOIN tribe c
ON b.ally = c.id

 

However that is basically what you already have.

 

All the best

 

Keith

[sp@rky13]

I'll try that. I think I've tried that before though I'll try again

 

A question though. Why do you put village a and player b and tribe c????

[sp@rky13]

Ok I tried this:

 

<?php
$con = mysql_connect("******","****","*********");
if (!$con)
{
   die('Could not connect: ' . mysql_error());
}

mysql_select_db("*******", $con);

$result = ("SELECT * FROM village9
JOIN player9
ON village9.player = player9.id
JOIN tribe9
ON player9.ally = tribe9.id
WHERE tag = 'dns';")
while($row = mysql_fetch_array($result))
{
echo "[village]".$row['x']."|".$row['y']."[/village]"."<br>";
}
mysql_close($con);
?>

 

but I got this error:

 

Parse error: syntax error, unexpected T_WHILE in /home/wwwspark/public_html/tw/query6.php on line 16

[Philip]

Missing semicolon:

 

$result = ("SELECT * FROM village9
JOIN player9
ON village9.player = player9.id
JOIN tribe9
ON player9.ally = tribe9.id
WHERE tag = 'dns';")

[sp@rky13]

Where am i missing the semicolon from?

[Philip]

The line I posted.

[Andy-H]

$result = ("SELECT * FROM village9
JOIN player9
ON village9.player = player9.id
JOIN tribe9
ON player9.ally = tribe9.id
WHERE tag = 'dns';")

 

Also missing the function...

 

$result = mysql_query("SELECT * FROM village9
JOIN player9
ON village9.player = player9.id
JOIN tribe9
ON player9.ally = tribe9.id
WHERE tag = 'dns';");

[kickstart]

A question though. Why do you put village a and player b and tribe c????

 

Saves having to write the full table namaes repeatedly when their are column names shared between tables.

 

All the best

 

Keith

[sp@rky13]

It's still returning errors Any ideas?

[kickstart]

Hi

 

What is the error you are getting now? Have you added the ; to the end of the previous line?

 

All the best

 

Keith

[sp@rky13]

This is what I have:

 

$result = mysql_query("SELECT * FROM village9
JOIN player9
ON village9.player = player9.id
JOIN tribe9
ON player9.ally = tribe9.id
WHERE tag = 'dns';"); or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo "[village]".$row['x']."|".$row['y']."[/village]"."<br>"; 
}

 

I was confused about the colon. did you guys add it in?

[kickstart]

Hi

 

Either take out the ; in the middle or the or die(mysql_error())

 

$result = mysql_query("SELECT * FROM village9
JOIN player9
ON village9.player = player9.id
JOIN tribe9
ON player9.ally = tribe9.id
WHERE tag = 'dns';") or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo "[village]".$row['x']."|".$row['y']."[/village]"."<br>"; 
}

 

All the best

 

Keith

[sp@rky13]

I apologise for my stupoiduty lol though I'm still getting errors. My new error is:

 

Unknown column 'player9.ally' in 'on clause'