SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: join tables

  1. #1
    SitePoint Zealot amit290's Avatar
    Join Date
    Oct 2001
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    join tables

    Hi,

    Can someone please share some code to do this...

    I got 3 mysql tables. You could, by looking below, think that it would be easier to put them in 1 table but I need data in tables 1 and 2 to use somewhere else

    TableName(1): linksections = FieldNames: SectionId, SectionName
    TableName(2): linknames = FieldNames: LinkNameId, LinkName
    TableName(3): links = FieldNames: LinkId, SectionId, LinkNameId, LinkUrl

    I want "SectionId" in table 3 to get the "SectionName" from table 1
    I want "LinkNameId" in table 3 to get the "LinkName" from table 2


    I want it echo'd in the format "<a href="linkurl(tbl3)">$linkname(tbl2)</a>

    How do I go about doing this?

    Thanks in advance.

  2. #2
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Michigan
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use 3 connections at once... while I have this on my clipboard, take a look at what I did to pull data from three tables.

    PHP Code:

    <?

    //Database & MYSQL Server Information
    $host2 "localhost";
    $user2 "nfire";
    $password2 "***";
    $database2 "nfire";
    $tablename2="games";


    //Database Connection
    $link2 mysql_connect ($host2$user2$password2);



    $query2 "SELECT id, title, players, esrb, rumble, platform, release_date, dev FROM $tablename2 where id='$id' ";

    $result2 mysql_db_query ($database2$query2$link2);


    print(
    "<h3 align=\"center\">$row2[title]</h3>");


    while (
    $row2mysql_fetch_array($result2)) {

              
           
                
    $title $row2[title];
                
    $players $row2[players];


    echo(
    "
    <h3>
    $title</h3>
    platform: 
    $row2[platform]<br>
    date: 
    $row2[release_date]<br>
    Players: 
    $row2[players]<br>
    Rumble: 
    $row2[rumble]<br>
    ESRB: <img src=\"/esrb/
    $row2[esrb].gif\">
    "
    );

    $host "localhost";
    $user "nfire";
    $password "***";
    $database "nfire";
    $tablename="developers";


    //Database Connection
    $link mysql_connect ($host$user$password);

    $query "SELECT developer, developer_link from $tablename where id='$row2[dev]'";

    $result mysql_db_query ($database$query$link);


    while (
    $row mysql_fetch_array



       
    ($result)) {

    $developer $row[developer];


    $id $developer;
    $developerlink $row[developer_link];

    print(
    "Developer: <a href=\"developers.php?id=$row2[dev]\">$id</a><br>");

    $host3 "localhost";
    $user3 "nfire";
    $password3 "***";
    $database3 "nfire";
    $tablename3="reviews";

    //Connect to Reviews Database

    $link3 mysql_connect ($host3$user3$password3);
    $query3 "SELECT * FROM $tablename3 where id = '$row2[id]'";
    $result3 mysql_db_query ($database3$query3$link3);
    while (
    $row3 mysql_fetch_array($result3)) {
    print(
    "Review by <a href=\"/reviews/reviews.php?id=$row3[pid]\">$row3[name]</a>");
    }
    }
    }

    mysql_close ($link);
    mysql_close ($link2);
    mysql_close ($link3);



    ?>

  3. #3
    Made with a Mac! philm's Avatar
    Join Date
    Sep 2001
    Location
    Portsmouth, UK
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hiya, I'll have a shot at it, please bear in mind this is untested and i'm still pretty new to php/mysql myself, but this may help you to a start at least
    PHP Code:
    <? $query "SELECT linksections.SectionName, linknames.LinkName
    FROM linksections ls, linknames ln, links
    WHERE ls.SectionId = links.SectionId
    AND ln.LinkNameId = links.LinkNameId" 
    or die("Error in query"); ?>

  4. #4
    SitePoint Guru enygmadae's Avatar
    Join Date
    Sep 2002
    Location
    Dallas, Tx.
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    same thing, just with shorter aliases:
    PHP Code:
    select
        t3
    .LinkUrl,
        
    t2.LinkName
    from 
        table1 t1
    ,
        
    table2 t2,
        
    table3 t3
    where
        t3
    .SectionId=t1.SectionId and
        
    t3.LinkNameId=t2.LinkName 
    PHP News, Views and Community: http://www.phpdeveloper.org

  5. #5
    SitePoint Zealot amit290's Avatar
    Join Date
    Oct 2001
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guys, thx for the code. I've changed it slightly and im having some problems with it.

    PHP Code:
        //get global variables
        
    global $lang;

      
    $query mysql_query("SELECT myuwb_linkname.LinkName_$lang, myuwb_links.LinkUrl FROM \n"
               
    ."myuwb_linksection, myuwb_linkname, myuwb_links WHERE myuwb_links.SectionId='1' \n"
               
    ."AND myuwb_linkname.LinkNameId=myuwb_links.LinkNameId AND myuwb_links.LinkLanguage='english'") or die("Error in query");

            while (
    $links_row mysql_fetch_array($query)) {

                
    $link_name $links_row["LinkName_$lang"];
                
    $link_url  $links_row["LinkUrl"];

                echo 
    "<img src=\"/config/themes/$theme_choice/images/navarrow.gif\" border=\"0\" alt=\"arrow\" /><a href=\"$link_url\" class=\"navbar\">$link_name</a><br>\n";
            }

            
    mysql_free_result($query); 
    I want to extract only records where "SectionName" = "account" (in this instance). Not sure if that is possible, so I tried extracting by "SectionId" from the "links" table, but that returns duplicates of the same link (4 of each).

    Also it doesnt look at this bit of code
    PHP Code:
    AND myuwb_links.LinkLanguage='english' 
    Any ideas?

    Thx

  6. #6
    SitePoint Guru enygmadae's Avatar
    Join Date
    Sep 2002
    Location
    Dallas, Tx.
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    select
        t3
    .LinkUrl,
        
    t2.LinkName
    from 
        table1 t1
    ,
        
    table2 t2,
        
    table3 t3
    where
        t3
    .SectionId=t1.SectionId and
        
    t3.LinkNameId=t2.LinkName and
        
    t1.SectionName='account' 
    ???
    PHP News, Views and Community: http://www.phpdeveloper.org

  7. #7
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature


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
  •