Jump to content


Photo

Joining tables with identical fields


  • Please log in to reply
2 replies to this topic

#1 BigTime

BigTime
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 27 August 2006 - 11:38 PM

Hi there.

Having trouble with a join, which then goes into a loop.  Both tables have an identical structure.  Im trying to join everything, then spit out my normal loop with just the records across both tables that have a similar entry in fieldname.....then sort all those results, by day, and time...the loop just helps me seperate the records by week, with each week getting their own table....and Im passing the fieldname variable via the url ie mypage.php?fieldname=ThisField

My problem is that they have the same column names, so I keep getting Column: 'time' in field list is ambiguous...Im unsure how to solve it as Im formating time when its coming out, but I need all the records to sort on that column across both tables.

<?
    # setup SQL statement
$SQL = " SELECT time, league, week, hometeam, awayteam, month, date, division, id, fieldlink, fieldname, TIME_FORMAT(time, '%l.%i %p') AS newtime FROM pac10 JOIN mac10 ON ( pac10.fieldname = mac10.fieldname) WHERE fieldname LIKE '$fieldname%' ORDER BY week ASC, date ASC, fieldname ASC, time ASC";

    # execute SQL statement
    $retid = mysql_db_query($db, $SQL, $cid);

    # check for errors
    if (!$retid) { echo( mysql_error()); }

    else {
	    $weektracker = 0;
		while ($row = mysql_fetch_array($retid)) {
		$league = $row["league"];
		$week = $row["week"];
		$hometeam = $row["hometeam"];		
		$awayteam = $row["awayteam"];		
		$month = $row["month"];
		$date = $row["date"];
		$newtime = $row["newtime"];
		$homescore = $row["homescore"];
		$awayscore = $row["awayscore"];
		$id = $row["id"];
		$division = $row["division"];
		$homeconference = $row["homeconference"];
		$awayconference = $row["awayconference"];
		$fieldlink = $row["fieldlink"];
		$fieldname = $row["fieldname"];
		
#BEGINNING MY LOOP BY WEEK AND DATA OUTPUT

{
   if($weektracker < $week) 
   {
       if($weektracker > 0)
	   echo ("</table><BR><BR>\n");

      echo ("<b><font face=arial size=2 color=#e0e0e0>WEEK $week</b></font>\n");
      echo ("<TABLE cellpadding=2 border=1 width=99% style=\"border-collapse: collapse; border: solid; border:1px;\">");
	  echo ("<TR><TD><font face=arial size=1 color=#e0e0e0><b>LEAGUE</b></td><TD width=80><font face=arial size=1 color=#e0e0e0><b>DIVISION</b></TD><TD width=40><font face=arial size=1 color=#e0e0e0><B>DATE</B></TD><td width=60><font face=arial size=1 color=#e0e0e0><b>TIME</b></TD><td><font face=arial size=1 color=#e0e0e0><B>AWAY</B></TD><td><font face=arial size=1 color=#e0e0e0><b>HOME</b></td><TD><font face=arial size=1 color=#e0e0e0><b><center>FIELD</center></b></TD></TR>");
      $weektracker = $week;
	    }


            echo ("<TR>");
            echo ("<TD><font face=arial size=1 color=#e0e0e0>$league</font></td><td><font face=arial size=1 color=#e0e0e0>$division</td><td><font face=arial size=1 color=#e0e0e0>$month - $date</td><td><font face=arial size=1 color=#e0e0e0>$newtime</TD><TD><font face=arial size=1 color=#e0e0e0>$awayteam</td><td><font face=arial size=1 color=#e0e0e0>$hometeam</td><td><a href=\"http://$fieldlink\" target=_blank><font face=arial size=1 color=#e0e0e0>$fieldname</td>\n");
			echo ("</TR>");
  }}

        echo ("</TABLE><BR>");

 

}

     

?>

thanks in advance for any insight :)



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 August 2006 - 10:20 PM

You need to use column and table aliases.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 BigTime

BigTime
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 29 August 2006 - 07:11 PM

Thanks Fenway....these boards used to be quite helpful with directives, now its all like generic canned spam.

For anyone finding this in a search....this is what I have learned:

JOINS are used for adding vertical colums...for instance having 2 vertical labeled colums from one table and then 5 other labeled columns from another table which would create a 7 columned table.

UNIONS are used for adding horizontal rows...so this is what I was after rather than a JOIN.

UNIONS need the fields aliased if you want to ORDER them....so here is my final query that worked:

$SQL = "SELECT time as t, league, week as w, hometeam, awayteam, month, date as d, division, fieldlink, fieldname as f, TIME_FORMAT(time, '%l.%i %p') AS newtime FROM pac10 WHERE fieldname LIKE '$fieldname%' 
UNION 
SELECT time as t, league, week as w, hometeam, awayteam, month, date as d, division, fieldlink, fieldname as f, TIME_FORMAT(time, '%l.%i %p') AS newtime FROM mac10 WHERE fieldname LIKE '$fieldname%' 
UNION
SELECT time as t, league, week as w, hometeam, awayteam, month, date as d, division, fieldlink, fieldname as f, TIME_FORMAT(time, '%l.%i %p') AS newtime FROM big10 WHERE fieldname LIKE '$fieldname%' 
ORDER BY w ASC, d ASC, f ASC, t ASC";






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users