MySQL INNER JOIN - two fields with same name!!
Posted: Fri Aug 07, 2009 7:38 am
I have an 'advertiser' PHP file that is meant to check the subscription of the users, and if they are within the month's subscribed period, it will display their uploaded advert.
The advert comprises of a headline, content, url and an image.
The image is the problem here as in dxadverts, there is a field called 'photo', and in dxusers', there is also a field called 'photo'. No, I didn't think about this before commencing!!!
I know I can do dxadverts.moderate in SQL, but it won't take it in PHP, to distinquish the 'photo' field from dxadverts, and not use the one in dxusers.
Here's the full code:
How can I render <img src='images/adverts/$photo' /> so it does show the image. All I see is the empty box on screen.
The advert comprises of a headline, content, url and an image.
The image is the problem here as in dxadverts, there is a field called 'photo', and in dxusers', there is also a field called 'photo'. No, I didn't think about this before commencing!!!
I know I can do dxadverts.moderate in SQL, but it won't take it in PHP, to distinquish the 'photo' field from dxadverts, and not use the one in dxusers.
Here's the full code:
Code: Select all
<?php
echo "<div class='advertboxtitle'>Sponsored Links</div>";
include "dbconn.php";
$result = mysql_query ("SELECT * FROM dxadverts INNER JOIN dxusers on dxusers.id = dxadverts.userid
WHERE dxadverts.moderate = 'online' ORDER BY RAND() LIMIT 0, 2");
while ($row = mysql_fetch_object($result))
{
//subscription date
$year = substr("$row->dxusers.subscribed",-10,4);
$month = substr("$row->dxusers.subscribed",-5,2);
$day = substr("$row->dxusers.subscribed",-2,2);
$photo = $row->dxadverts.photo;
//today date
$sysyear = substr("$today",-10,4);
$sysmonth = substr("$today",-5,2);
$sysday = substr("$today",-2,2);
//one month on
$endyear = $year;
$endmonth = ($month + 1);
$endday = $day;
if ($month <= $endmonth)
echo "
<div class='advertbox'>
<div class='advertboxheadline'><a href='http://$row->url'>$row->headline</a></div>
<div class='advertboximage'><a href='http://$row->url'><img src='images/adverts/$photo' style='width:186px; height: 60px' border='0' /></a></div>
<div class='advertboxcontent'><a href='http://$row->url'>$row->content</a></div>
</div>
";
}
mysql_free_result($result);
mysql_close($sqlconn);
?>