Join 3 tables

Thread Solved

Join Date: May 2009
Posts: 10
Reputation: raul66 is an unknown quantity at this point 
Solved Threads: 0
raul66 raul66 is offline Offline
Newbie Poster

Join 3 tables

 
0
  #1
Jun 7th, 2009
Hi everybody,
I want to join 3 tables and gain same data from these tables.
1.table:
usr - user information
usr_id
2.table:
res- results
res_usr_id
res_exc_id
res_result
3.table:
exc- excercises
exc_id
exc_excercise_name

Now I want print all results from one user and all excercises from one user.Can somebody help me?I don't know how to write this sql query...

Thanks
Reply With Quote Quick reply to this message  
Join Date: Jun 2009
Posts: 21
Reputation: Baldy76 is an unknown quantity at this point 
Solved Threads: 3
Baldy76 Baldy76 is offline Offline
Newbie Poster

Re: Join 3 tables

 
0
  #2
Jun 8th, 2009
Originally Posted by raul66 View Post
Hi everybody,
I want to join 3 tables and gain same data from these tables.
1.table:
usr - user information
usr_id
2.table:
res- results
res_usr_id
res_exc_id
res_result
3.table:
exc- excercises
exc_id
exc_excercise_name

Now I want print all results from one user and all excercises from one user.Can somebody help me?I don't know how to write this sql query...

Thanks
would you like the users that don't have exercises to also be displayed? If so just replace the word "inner" with "left".
  1. <?php
  2. $query = "select * from
  3. usr
  4. inner join res on usr.usr_id = res.res_usr_id
  5. inner join exc on res.res_exc_id = exc.exc_id";
  6. ?>
Last edited by Baldy76; Jun 8th, 2009 at 12:01 am.
Reply With Quote Quick reply to this message  
Join Date: Jun 2009
Posts: 68
Reputation: djjjozsi is an unknown quantity at this point 
Solved Threads: 10
djjjozsi djjjozsi is offline Offline
Junior Poster in Training

Re: Join 3 tables

 
0
  #3
Jun 8th, 2009
if a user doesn't have results, that will be empty field in the results:
users , excercises , and res_result is the 3 table (you did not give these details)
<?php
$uid=1;
$sql="SELECT users.usr_id, users.usr, excercises.exc, excercises.exc_excercise_name, results.res, results.res_result
FROM excercises RIGHT JOIN (results RIGHT JOIN users ON results.res_usr_id = users.usr_id) ON excercises.exc_id = results.res_exc_id
WHERE (((users.usr_id)='$uid'))";
/* fetch here */
?>
Last edited by djjjozsi; Jun 8th, 2009 at 4:45 am. Reason: forget the BBcode
Reply With Quote Quick reply to this message  
Join Date: Oct 2007
Posts: 78
Reputation: navi17 is an unknown quantity at this point 
Solved Threads: 5
navi17 navi17 is offline Offline
Junior Poster in Training

Re: Join 3 tables

 
0
  #4
Jun 8th, 2009
this seems to very complex
here is the simple solution:

  1. $sql="SELECT *
  2. FROM user as a, resutls as b,excercises as c where a.usr_id= b.res_usr_id and b.res_exc_id=c.exc_id";

i hope it works.
Last edited by peter_budo; Jun 8th, 2009 at 10:35 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Reply With Quote Quick reply to this message  
Join Date: May 2009
Posts: 10
Reputation: raul66 is an unknown quantity at this point 
Solved Threads: 0
raul66 raul66 is offline Offline
Newbie Poster

Re: Join 3 tables

 
0
  #5
Jun 8th, 2009
Ok, my code, which doesn't work.

  1. //this only for time, which I want to print
  2. <?php
  3. $query="SELECT res_time FROM res";
  4. $result = mysql_query($query);
  5. $dbf = mysql_fetch_assoc($result);
  6. ?>
  7. <div class="main">
  8. <table cellspacing="20">
  9. <tr>
  10. <th></th>
  11. <th></th>
  12. <th>Count of good excercises</th>
  13. <th>Count of bad excercises</th>
  14. <th>Success</th>
  15. </tr>
  16. <tr><td><?php echo $dbf['res_time']; ?></td></tr>
  17. <?php
  18. $query1="SELECT exc_excercise_name, res_result, usr_id FROM exc, res, usr WHERE res_exc_id = exc_id AND res_usr_id = usr_id";
  19. $result1 = mysql_query($query1);
  20. while (list($exc_excercise_name,$res_result) = mysql_fetch_row($result1)) {
  21. echo "<tr>",
  22. "<td>",
  23. "</td>",
  24. "<td>",
  25. $exc_excercise_name,"</td>",
  26. "<td>",
  27. "</td>",
  28. "<td>",
  29. "</td>",
  30. "<td>",
  31. $res_result,
  32. "</td>",
  33. "</tr>";
  34. }
  35. @mysql_close($connect);
  36. ?>
  37. </table>

I want print ALL excercises and ALL his results from this excercises, which makes the schoolar in fixed time, when I click on his name on other search page. When he didn't make any excercises, his result list will be blank.
Last edited by peter_budo; Jun 8th, 2009 at 10:36 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Reply With Quote Quick reply to this message  
Join Date: Jun 2009
Posts: 68
Reputation: djjjozsi is an unknown quantity at this point 
Solved Threads: 10
djjjozsi djjjozsi is offline Offline
Junior Poster in Training

Re: Join 3 tables

 
0
  #6
Jun 8th, 2009
lets test the suggested queries in phpmyadmin, and select the correct one. notice that the table names should changed.

(i've tested my query )
Last edited by djjjozsi; Jun 8th, 2009 at 8:20 am.
Reply With Quote Quick reply to this message  
Join Date: May 2009
Posts: 10
Reputation: raul66 is an unknown quantity at this point 
Solved Threads: 0
raul66 raul66 is offline Offline
Newbie Poster

Re: Join 3 tables

 
0
  #7
Jun 8th, 2009
Originally Posted by djjjozsi View Post
lets test the suggested queries in phpmyadmin, and select the correct one. notice that the table names should changed.

(i've tested my query )
A tested these queries, but no one works, how I want.
Could you show me the right one?

Thanks
Reply With Quote Quick reply to this message  
Join Date: Jun 2009
Posts: 68
Reputation: djjjozsi is an unknown quantity at this point 
Solved Threads: 10
djjjozsi djjjozsi is offline Offline
Junior Poster in Training

Re: Join 3 tables

 
0
  #8
Jun 8th, 2009
tell us the table names, or change the names from ours.
Reply With Quote Quick reply to this message  
Join Date: May 2009
Posts: 10
Reputation: raul66 is an unknown quantity at this point 
Solved Threads: 0
raul66 raul66 is offline Offline
Newbie Poster

Re: Join 3 tables

 
0
  #9
Jun 8th, 2009
Originally Posted by raul66 View Post
A tested these queries, but no one works, how I want.
Could you show me the right one?

Thanks

You are right, one of these queries was good for me.
It work's.

Thanks
Reply With Quote Quick reply to this message  
Join Date: Jun 2009
Posts: 68
Reputation: djjjozsi is an unknown quantity at this point 
Solved Threads: 10
djjjozsi djjjozsi is offline Offline
Junior Poster in Training

Re: Join 3 tables

 
0
  #10
Jun 8th, 2009
which one?

- i see now -
Last edited by djjjozsi; Jun 8th, 2009 at 9:08 am.
Reply With Quote Quick reply to this message  
Reply

This thread has been marked solved.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC