sp@rky13 Posted November 14, 2009 Share Posted November 14, 2009 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 Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 14, 2009 Author Share Posted November 14, 2009 Anyone able to just link me a tutorial on joining three tables? Quote Link to comment Share on other sites More sharing options...
Andy-H Posted November 14, 2009 Share Posted November 14, 2009 Theres a tutorial on data joins on PHPfreaks... http://www.phpfreaks.com/tutorial/data-joins-unions Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 15, 2009 Author Share Posted November 15, 2009 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? Quote Link to comment Share on other sites More sharing options...
Philip Posted November 15, 2009 Share Posted November 15, 2009 I think it'd be best if you posted sample data from each table and how you wanted it joined. Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 15, 2009 Author Share Posted November 15, 2009 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 Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 15, 2009 Author Share Posted November 15, 2009 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. Quote Link to comment Share on other sites More sharing options...
Zane Posted November 15, 2009 Share Posted November 15, 2009 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) Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 15, 2009 Author Share Posted November 15, 2009 Sorry but huh ????? I don't understand. What would you like to know? Quote Link to comment Share on other sites More sharing options...
Zane Posted November 15, 2009 Share Posted November 15, 2009 What would you like to know? What do you tables look like? What columns do you have? Quote Link to comment Share on other sites More sharing options...
Zane Posted November 15, 2009 Share Posted November 15, 2009 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. Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 15, 2009 Author Share Posted November 15, 2009 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 Quote Link to comment Share on other sites More sharing options...
kickstart Posted November 15, 2009 Share Posted November 15, 2009 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 Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 15, 2009 Author Share Posted November 15, 2009 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???? Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 15, 2009 Author Share Posted November 15, 2009 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 Quote Link to comment Share on other sites More sharing options...
Philip Posted November 15, 2009 Share Posted November 15, 2009 Missing semicolon: $result = ("SELECT * FROM village9 JOIN player9 ON village9.player = player9.id JOIN tribe9 ON player9.ally = tribe9.id WHERE tag = 'dns';") Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 15, 2009 Author Share Posted November 15, 2009 Where am i missing the semicolon from? Quote Link to comment Share on other sites More sharing options...
Philip Posted November 15, 2009 Share Posted November 15, 2009 The line I posted. Quote Link to comment Share on other sites More sharing options...
Andy-H Posted November 15, 2009 Share Posted November 15, 2009 $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';"); Quote Link to comment Share on other sites More sharing options...
kickstart Posted November 16, 2009 Share Posted November 16, 2009 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 Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 16, 2009 Author Share Posted November 16, 2009 It's still returning errors Any ideas? Quote Link to comment Share on other sites More sharing options...
kickstart Posted November 16, 2009 Share Posted November 16, 2009 Hi What is the error you are getting now? Have you added the ; to the end of the previous line? All the best Keith Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 16, 2009 Author Share Posted November 16, 2009 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? Quote Link to comment Share on other sites More sharing options...
kickstart Posted November 16, 2009 Share Posted November 16, 2009 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 Quote Link to comment Share on other sites More sharing options...
sp@rky13 Posted November 16, 2009 Author Share Posted November 16, 2009 I apologise for my stupoiduty lol though I'm still getting errors. My new error is: Unknown column 'player9.ally' in 'on clause' Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.