MySQL INNER JOIN - two fields with same name!!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

MySQL INNER JOIN - two fields with same name!!

Post by simonmlewis »

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:

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);
?>
How can I render <img src='images/adverts/$photo' /> so it does show the image. All I see is the empty box on screen.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL INNER JOIN - two fields with same name!!

Post by Weirdan »

Code: Select all

 
SELECT *,dxusers.photo AS userphoto, dxadverts.photo AS advertphoto FROM dxadverts INNER JOIN dxusers ON dxusers.id = dxadverts.userid
WHERE dxadverts.moderate = 'online' ORDER BY RAND() LIMIT 0, 2
 
Then use $row->userphoto or $row->advertphoto depending on what you need.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

[RESOLVED] Re: MySQL INNER JOIN - two fields with same name!

Post by simonmlewis »

And that is why I am here - brilliant developers, answering a head-banging developer quickly, with a brilliant answer.

JOB DONE! Thanks.
And one for my memory bank, as I forgot you can use AS to assign a value to a variable like that. I've done it with count(id) AS numrows before....

Chrs.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL INNER JOIN - two fields with same name!!

Post by simonmlewis »

Actually, something here isn't right.

Code: Select all

<?php
 
echo "<div class='advertboxtitle'>Sponsored Links</div>";
include "dbconn.php";
$result = mysql_query ("SELECT *, dxusers.photo AS userphoto, dxadverts.photo AS advertphoto 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);
  
//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 ($sysmonth <= $endmonth && $sysmonth >= $month)
      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/$row->advertphoto' 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);
?>
The method in line 26 to check their subscription isn't working, and won't work in the new year.

Basically they have to have been registered recently to have their advert shown. So if they subscribed over a month ago, it won't show.

So today is 7 August. If they subscription on 3 August it should show. If they subscribed on 4 July, and not since.... it should NOT show.

Something isn't right in my code....but can I pin point it? :(
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL INNER JOIN - two fields with same name!!

Post by Weirdan »

Code: Select all

 
$year = substr("$row->dxusers.subscribed",-10,4);
$month = substr("$row->dxusers.subscribed",-5,2);
$day = substr("$row->dxusers.subscribed",-2,2);
 
You can't use dxusers.subscribed like this, just as you couldn't use dxusers.photo. You might want to alias subscribed field as well.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL INNER JOIN - two fields with same name!!

Post by simonmlewis »

Code: Select all

<?php
 
echo "<div class='advertboxtitle'>Sponsored Links</div>";
include "dbconn.php";
$result = mysql_query ("SELECT *, dxusers.photo AS userphoto, dxadverts.photo AS advertphoto, dxusers.subscribed AS usersub 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->usersub",-10,4);
  $month = substr("$row->usersub",-5,2);
  $day = substr("$row->usersub",-2,2);
  
//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 ($sysmonth <= $endmonth && $sysmonth >= $month)
      echo "
      <div class='advertbox'>
      
      <div class='advertboxheadline'>$usersub<a href='http://$row->url'>$row->headline</a></div>
      <div class='advertboximage'><a href='http://$row->url'><img src='images/adverts/$row->advertphoto' 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);
?>
Mmmm this isn't working.
I have tried the subscribed date to be 2009-08-03 (yyyy-mm-dd)which should accept it and show the ads, but isn't.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply