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 ($row2= mysql_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
  •