...

View Full Version : Resolved Display image where field equals value from another table



whitewidow
05-22-2010, 02:52 PM
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:



$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:



WHERE blogID='$result1["blogID"]' ");


If anyone can tell me how to do this it would be appreciated

met
05-22-2010, 03:05 PM
considered using JOIN (http://www.w3schools.com/sql/sql_join.asp)?

whitewidow
05-22-2010, 04:17 PM
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

# 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();

?>

whitewidow
05-22-2010, 05:03 PM
OK I have worked it out. Added an IF statement



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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum