943,633 Members | Top Members by Rank

Ad:
  • PHP Discussion Thread
  • Marked Solved
  • Views: 644
  • PHP RSS
Jun 7th, 2009
0

Join 3 tables

Expand 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
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
raul66 is offline Offline
10 posts
since May 2009
Jun 8th, 2009
0

Re: Join 3 tables

Click to Expand / Collapse  Quote originally posted by raul66 ...
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".
php Syntax (Toggle Plain Text)
  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.
Reputation Points: -1
Solved Threads: 3
Newbie Poster
Baldy76 is offline Offline
21 posts
since Jun 2009
Jun 8th, 2009
0

Re: Join 3 tables

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
Reputation Points: 12
Solved Threads: 11
Junior Poster in Training
djjjozsi is offline Offline
69 posts
since Jun 2009
Jun 8th, 2009
0

Re: Join 3 tables

this seems to very complex
here is the simple solution:

php Syntax (Toggle Plain Text)
  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.
Reputation Points: 15
Solved Threads: 6
Junior Poster
navi17 is offline Offline
118 posts
since Oct 2007
Jun 8th, 2009
0

Re: Join 3 tables

Ok, my code, which doesn't work.

php Syntax (Toggle Plain Text)
  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.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
raul66 is offline Offline
10 posts
since May 2009
Jun 8th, 2009
0

Re: Join 3 tables

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.
Reputation Points: 12
Solved Threads: 11
Junior Poster in Training
djjjozsi is offline Offline
69 posts
since Jun 2009
Jun 8th, 2009
0

Re: Join 3 tables

Click to Expand / Collapse  Quote originally posted by djjjozsi ...
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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
raul66 is offline Offline
10 posts
since May 2009
Jun 8th, 2009
0

Re: Join 3 tables

tell us the table names, or change the names from ours.
Reputation Points: 12
Solved Threads: 11
Junior Poster in Training
djjjozsi is offline Offline
69 posts
since Jun 2009
Jun 8th, 2009
0

Re: Join 3 tables

Click to Expand / Collapse  Quote originally posted by raul66 ...
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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
raul66 is offline Offline
10 posts
since May 2009
Jun 8th, 2009
0

Re: Join 3 tables

which one?

- i see now -
Last edited by djjjozsi; Jun 8th, 2009 at 9:08 am.
Reputation Points: 12
Solved Threads: 11
Junior Poster in Training
djjjozsi is offline Offline
69 posts
since Jun 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in PHP Forum Timeline: search
Next Thread in PHP Forum Timeline: Hi, need help..





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC