Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    May 2010
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display image where field equals value from another table

    Can anyone explain to me how I can show information from one table that equals the value from another?

    What I mean by this is as follows:

    I have 2 tables (blog & images) which are linked via a field called 'blogID'.
    The blogID value is inserted into the blog table and then passed to the images table.

    I wish to rerieve the blog information and then display any associated image that matches the same blogID.

    So far I have this:

    PHP Code:
    $result1 mysql_query("SELECT blogSubject, blogContent, blogTimestamp FROM blog WHERE blogID>'0' ");
    $result2 mysql_query("SELECT imagePath, imageName FROM images WHERE blogID='$result1["blogID"]' "); 
    I am clearly wrong with this bit of code:

    PHP Code:
    WHERE blogID='$result1["blogID"]' "); 
    If anyone can tell me how to do this it would be appreciated
    Last edited by whitewidow; 05-22-2010 at 05:04 PM.

  • #2
    met
    met is offline
    Regular Coder
    Join Date
    Oct 2009
    Location
    United Kingdom
    Posts
    728
    Thanks
    4
    Thanked 119 Times in 119 Posts
    considered using JOIN?

  • #3
    New Coder
    Join Date
    May 2010
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok yes just tried that and that works nicely, thanks.

    One things though, this causes a little problem in that it will only show a blog if there is an image that matches the blogID in the blog table.

    So, the JOIN function works great up to a point but I need it to be a little more clever. By this I mean that I would like all blog posts to show regardless of whether there is an image that has a identical blogID.

    I'm probably not explaining this very clearly.

    I want the blog subject and content to be displayed and if there is an image, stored in the images table that matches the blogID from the blog table, then to show it, else don't display/echo the table cell and just show the blog.

    Hopefully that makes sense?

    My code:

    PHP Code:
    <?php

    # Database connection details
    $username "root"
    $password ""
    $hostname "localhost";

    # connect to database 
    mysql_connect($hostname,$username,$password); 
    mysql_select_db("crookedblog"); 


    // Construct our join query
    $query1 "SELECT blog.blogSubject, blog.blogContent, blog.blogTimestamp, images.imageName, images.imagePath " "FROM blog, images " "WHERE images.blogID = blog.blogID";
    $result mysql_query($query1) or die(mysql_error());

    # Generate table to hold retrieved data    
    echo "<table border='0' cellpadding='0' cellspacing='0'>";
        while(
    $row mysql_fetch_array($result)) 
            {
                
    $image $row['imagePath'] . $row['imageName'];

    # This is for the header of each blog box - Holds blogSubject             
                
    echo "<tr>";
                    echo 
    "<td class='text1' width='439' height='22' background='images/h1-background.png'>" $row['blogSubject'] . "</td>";
                echo 
    "</tr>";
    echo 
    "</table>";

    # This is for the content of each blog box - Holds blogContent
    echo "<table border='0' cellpadding='0' cellspacing='0'>";  
                
                echo 
    "<tr height='10'>";
                    echo 
    "<td background='images/background_wallpaper_tile.gif'></td>";
                    echo 
    "<td background='images/background_wallpaper_tile.gif'></td>";
                    echo 
    "<td background='images/background_wallpaper_tile.gif' width='20'></td>";
                
    "</tr>";
                
                echo 
    "<tr>";
                      echo 
    "<td class='text2' width='439' style='border: 0px 1px 0px 1px solid #000;' background='images/background_wallpaper_tile.gif' valign='top'>" $row['blogContent'] . "</td>";
                    echo 
    "<td style='border: 1px solid #000' background='images/background_wallpaper_tile.gif'><img src=" $image " /></td>";
                    echo 
    "<td background='images/background_wallpaper_tile.gif' width='20'></td>";
                echo 
    "</tr>";

                echo 
    "<tr height='10'>";
                    echo 
    "<td background='images/background_wallpaper_tile.gif'></td>";
                    echo 
    "<td background='images/background_wallpaper_tile.gif'></td>";
                    echo 
    "<td background='images/background_wallpaper_tile.gif' width='20'></td>";
                
    "</tr>";

    echo 
    "</table>"

    # This is for the footer of each blog box - Holds blogTimestamp
    echo "<table border='0' cellpadding='0' cellspacing='0'>";
                echo 
    "<tr>";
                    echo 
    "<td class='text3' width='439' height='22' align='right' background='images/h1-background2.png'>" $row['blogTimestamp'] . "</td>";
                echo 
    "</tr>";
    //Adds gap between blog posts
                
    echo "<tr height='5'>";
                echo 
    "</tr>";
            }
    echo 
    "</table>";

    mysql_close();

    ?>

  • #4
    New Coder
    Join Date
    May 2010
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK I have worked it out. Added an IF statement

    PHP Code:
    if (($row['imageName'])>"")
    {                
    echo 
    "<td style='border: 1px solid #000' background='images/background_wallpaper_tile.gif' valign='top'><img src=" $image " /></td>";

    Thanks for your help though


  •  

    Posting Permissions

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