SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I do a join query between three tables where each table has a column called ID, how do I echo those ID's to the webpage selectively. For instance:

    $sql = "Select * from tbl1, tbl2, tbl3";
    $res = mysql_query($sql);
    while ( $row = mysql_fetch_array($res) ) {

    echo " (ID from tbl1) (ID from tbl2) (ID from tbl)";

    }

    Whate would I replace the paranthetical statements with to get the respective ID's echoed to the browser.

    Thanks.
    Marty H.

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well if the field is called "ID" in each table MySQL is not going to like that. So where there is ambiguity, append the table name to the field to defin its scope. It is also handy to give the field an alias too using AS (this becomes handy in the php code later as well).

    Eg:
    Code:
    $sql = "SELECT tbl1.ID AS id1, 
                            tbl2.ID AS id2,
                            tbl3.ID AS id3
               FROM tbl1. tbl2. tbl3";
    $result = mysql_query($sql);
    while($row = mysql_fetch_array($result) {
       echo $row["id1"] . $row["id2"] . $row["id3"];
    
    }
    BTW, you have no where clause in your sql statement. Are you aware that joining tables (that's the FROM clause bit) creates a set which is the cartesian product of the elements of the joined tables. Blah - what I'm saying is; say you have 3 records in tbl1, tbl2 and tbl3. The result set of the above sql query will contain 27 rows. To filter the result set you use a WHERE clause.
    Last edited by freakysid; Apr 5, 2001 at 06:16.

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid
    BTW, you have no where clause in your sql statement. Are you aware that joining tables (that's the FROM clause bit) creates a set which is the cartesian product of the elements of the joined tables. Blah - what I'm saying is; say you have 3 records in tbl1, tbl2 and tbl3. The result set of the above sql query will contain 27 rows. To filter the result set you use a WHERE clause.
    Actually the real sql statement does have a where clause. I just did this for illustration purposes. Thanks, for your help, that is pretty much what I am doing now, just thought there might be a more efficient way.
    Marty H.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •