Joining tables

I am building a eCart shop that sells photographic prints. I am trying to get the location name to display in the product info.

I need to get the 'LocationName' (VARCHAR) from the 'Location' table using 'ImageLocation' (INT) from the 'Image' table.

I have tried doing it with a form variable (LocID) but can't get it to work!!

Here is my page: month.php?Month=1

This is my PHP on the page:

$colname_rsLocationName = "-1";
if (isset($_POST['LocID'])) {
$colname_rsLocationName = (get_magic_quotes_gpc()) ? $_POST['LocID'] : addslashes($_POST['LocID']);
}
mysql_select_db($database_connLandscapes365, $connLandscapes365);
$query_rsLocationName = sprintf("SELECT LocationName FROM Location WHERE LocationID = %s", GetSQLValueString($colname_rsLocationName, "int"));
$rsLocationName = mysql_query($query_rsLocationName, $connLandscapes365) or die(mysql_error());
$row_rsLocationName = mysql_fetch_assoc($rsLocationName);
$totalRows_rsLocationName = mysql_num_rows($rsLocationName);

If you view source of your page, the LocIDform element is in a separate form from add to cart button.


<td class="info"><form action="month.php" method="post" name="loc209" id="loc209">
<input name="LocID" type="hidden" value="2" />2. </form></td>
<td class="info">16 x 24 in</td>
<td class="price">£75.00</td>
<td class="form"><form action="/test/month.php?Month=1" method="post" name="L365cart_1_ATC_" id="L365cart_1_ATC_">
<input type="hidden" name="L365cart_1_ID_Add" value="209" />
<input type="hidden" name="L365cart_1_Quantity_Add" value="1" size="4" />

<input type="image" src="images/design/buy.gif" border="0" value="Add to Cart" name="L365cart_1_ATC" alt="Buy now!">
</form></td>


Move the LocIDform element so that it is part of the Add to cart form.

Hi Jason,

Thanks for the reply, but it didn't work. At the moment I can get the 'ImageLocation' integer to appear with each image, but I want to display the 'LocationName' from Locations instead by joining (merging?) the two.

You will see on the test page above that it displays the INT OK.

I will attach the complete page, so that you can see the php code.

My Image table has the following relevant columns:
ImageID (INT)
ImageLocation (INT)

…and the Location table:
LocationID (INT)
LocationName (VARCHAR)

Change the Select .... From prtion of the rsImages recordset:

SELECT *, DATE_FORMAT(ImageDate, '%%D %%M %%Y') as ImageDate FROM Image

to:

SELECT Image.*,Location.LocationName , DATE_FORMAT(Image.ImageDate, '%%D %%M %%Y') as ImageDate 
FROM Image
INNER JOIN Location
ON Image.ImageLocation = Location.LocationID

The you can use the LocationName from the rsImages recordset to return the correct location name.

Thank you Jason, that worked perfectly!!