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);
?>