#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    25
    Rep Power
    0
    Hi

    I知 trying to join two tables called Tutors and Course along Course ID column. I知 not sure how to do this with MySQL, and especially how to display such join using PHP. Can you help?

    Thanks
    Lisa
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Location
    Pune, Maharashtra, India
    Posts
    86
    Rep Power
    14
    As far as I remember MYSQL do no support Join function as ORACLE And many other database support.

    [This message has been edited by nikunj (edited August 24, 2000).]
  4. #3
  5. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    Ahh, right. Without the ability to join a database would be worthless.

    Lisa,

    select .... from Tutors t, Course c where t.courseid=c.courseid;

    You can select whatever fields you want from both tables but if there is ambiguity in the the field names (same field name in both tables) you'll need to preface the field name with the table name or the alias as I have demonstrated (t or c).
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Austin, TX, USA
    Posts
    11
    Rep Power
    0
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    $results = mysql_fetch_array($database,$query,$link);
    [/code]
    Then you can step through the array using several methods, the while loop with list, each being the best, or array walk. Each pass through the array will generate some output, such as <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>echo $array_var[$data1];[/code]

    One more thing, the select statement works better like this.
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>$query_stmt = "SELECT tutors.t as t,course.c as c from tutors, course WHERE 'tutors.t=course.c'";[/code]
    Don't forget the syntax, especially "'"'s because PHP requires some translation of quotes into mysql.

    This type of SELECT let's you define aliases for the resulting fieldset and this is easier to work with when doing mysql_fetch_object and mysql_fetch_array functions.

    ------------------
    Dave Bryant
    dbryant@jump.net

    [This message has been edited by davebryant (edited August 24, 2000).]
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Seattle, WA USA
    Posts
    23
    Rep Power
    0
    A Note On Joins
    ************************
    Any database that supports Ansi SQL statements, generally will support joins.

    Here is a quick summary:

    <UL TYPE=SQUARE>
    <LI>Equal Joins: "WHERE table1.field = table2.field"
    <LI>Cartesian Joins: No fields are linked in the WHERE clause.
    <LI>Outer Joins: "WHERE table1.field (+) = table2.field"
    </UL>

    In the last example the table/field on the right table2 will included all of it's contents and table1 will only display it's matches.

    MySQL is a great database to use...and soon it will support Transactions!


    ------------------
    Carl Sweetland
    carl@sweetlands.com
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    25
    Rep Power
    0
    Thanks all for replying
    However I can稚 quite get my head around it.
    I知 trying to get the results of a table join to display without success. Could some kind person please point out where I知 going wrong. The code is below:
    Note: the code works by clicking a submit button which links to the code.


    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    <?

    mysql_connect("localhost","username","password");
    $query = "SELECT tutor.tutor_name, course.course_name from tutor,course WHERE tutor.course_id = course.tutor_id;
    $result = mysql_db_query("example", $query);

    if ($result) {
    echo "Found these entries in the database:<ul>";
    while ($r = mysql_fetch_array($result)) {
    $tutor_name= $r["tutor_name"];
    $course_name = $r["course_name"];
    echo "<li>$tutor_name, $course_name";
    }
    echo "</ul>" ;
    } else {
    echo "No data.";
    }
    mysql_free_result($result);
    ?>
    [/code]

    Just in case the above is not readable here is the same code below:

    <?

    mysql_connect("localhost","username","password");
    $query = "SELECT tutor.tutor_name, course.course_name from tutor,course WHERE tutor.course_id = course.tutor_id;
    $result = mysql_db_query("example", $query);

    if ($result) {
    echo "Found these entries in the database:<ul>";
    while ($r = mysql_fetch_array($result)) {
    $tutor_name= $r["tutor_name"];
    $course_name = $r["course_name"];
    echo "<li>$tutor_name, $course_name";
    }
    echo "</ul>" ;
    } else {
    echo "No data.";
    }
    mysql_free_result($result);
    ?>

  12. #7
  13. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    <<
    <?

    mysql_connect("localhost","username","password");
    $query = "SELECT tutor.tutor_name, course.course_name from tutor,course WHERE tutor.course_id = course.tutor_id;
    $result = mysql_db_query("example", $query);

    if ($result) {
    echo "Found these entries in the database:<ul>";
    while ($r = mysql_fetch_array($result)) {
    $tutor_name= $r["tutor_name"];
    $course_name = $r["course_name"];
    echo "<li>$tutor_name, $course_name";
    }
    echo "</ul>" ;
    } else {
    echo "No data.";
    }
    mysql_free_result($result);
    ?>
    >>


    you should try your sql query something like this(ie, using table alias name):

    "SELECT t.tutor_name, c.course_name from tutor as t,course as c WHERE t.course_id = c.tutor_id"



    <<
    $query = "SELECT tutor.tutor_name, course.course_name from tutor,course WHERE tutor.course_id = course.tutor_id;
    >>

    you have not closed the above sql query..


    try the following code:


    <?
    mysql_connect("localhost","username","password");
    $query = "SELECT t.tutor_name, c.course_name from tutor as t,course as c WHERE t.course_id = c.tutor_id";

    $result = mysql_db_query("example", $query);

    if (mysql_num_rows($result)>0) {
    echo "Found these entries in the database:<ul>";
    while ($r = mysql_fetch_array($result)) {
    $tutor_name= $r["tutor_name"];
    $course_name = $r["course_name"];
    echo "<li>$tutor_name, $course_name";
    }
    echo "</ul>" ;
    } else {
    echo "No data.";
    }
    mysql_free_result($result);
    ?>

    Good Luck!!!

    ------------------
    SR -
    webshiju.com
    www.jobxyz.com-IT Career Portal
    ezipindia.com--WebStudio


    "The fear of the LORD is the beginning of knowledge..."


    [This message has been edited by Shiju Rajan (edited August 25, 2000).]
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    25
    Rep Power
    0
    Thanks!

Similar Threads

  1. inner join, conditional And sorting query
    By byte me in forum PHP Development
    Replies: 0
    Last Post: January 17th, 2004, 11:44 AM
  2. inner join, conditional And sorting query
    By byte me in forum MySQL Help
    Replies: 3
    Last Post: January 17th, 2004, 11:11 AM
  3. Help, please. Cant get the join to work
    By happybunny in forum MySQL Help
    Replies: 1
    Last Post: January 15th, 2004, 01:43 PM
  4. trouble with a join query
    By Chauzer in forum MySQL Help
    Replies: 5
    Last Post: January 9th, 2004, 05:34 PM

IMN logo majestic logo threadwatch logo seochat tools logo