Unknown Column in on clause after Upgrade to V5

Have moved a website running php 4.4.7 and MySql 4.0.27 under linux to another running php 5.2.0 and MySql 5.0.27 also under linux.
I am fairly sure it is something to do with the way the JOIN query needs to be changed to run under MySql V5 but I ain't a programmer and the solutions I've seen are way out of my depth!

The page displays the error: Unknown column 'M.MatchID' in 'on clause' and can be seen at:
http://www.bromsgroverovers.org.uk/stats/matches.php

The section of matches.php which is responsible is attached with the JOIN statement near the top:

Thank You
Chris Curtis


<?php
// Construct db query to get fixtures from database.
$selectClause = "SELECT
                  M.MatchID AS id,
	         M.MatchAdditionalType AS additype,
	         O.OpponentName AS opponent,
	         O.OpponentID AS oppid,
	         M.MatchGoals AS goals,
	         M.MatchGoalsOpponent AS goals_opponent,
	         M.MatchPenaltyGoals AS penalty_goals,
	         M.MatchPenaltyGoalsOpponent AS penalty_goals_opponent,
	         M.MatchOvertime AS overtime,
	         M.MatchPenaltyShootout AS penalty_shootout,
	         DATE_FORMAT(M.MatchDateTime, '%M %Y') AS month,
	         DATE_FORMAT(M.MatchDateTime, '%a %e') AS date,
	         DATE_FORMAT(M.MatchDateTime, '%H:%i') AS time,
	         M.MatchPlaceID AS place,
                  M.MatchAttendance AS att,
	         M.MatchPublish AS publish,
	         MT.MatchTypeName AS typename,
	         P.PreviewText AS prewtext
	         FROM tplss_matches M, tplss_matchtypes MT, tplss_opponents O
	         LEFT OUTER JOIN tplss_previews P ON M.MatchID = P.PreviewMatchID ";
 
if (($defaultseasonid != 00) && ($defaultmatchtypeid != 0)) {
  $whereClause = "WHERE M.MatchTypeID = '$defaultmatchtypeid'
                  AND M.MatchSeasonID = '$defaultseasonid'
                  AND M.MatchTypeID = MT.MatchTypeID
	          AND M.MatchOpponent = O.OpponentID ";
} elseif (($defaultseasonid == 0) && ($defaultmatchtypeid != 0)) {
  $whereClause = "WHERE M.MatchTypeID = '$defaultmatchtypeid'
	          AND M.MatchTypeID = MT.MatchTypeID
	          AND M.MatchOpponent = O.OpponentID ";
} elseif (($defaultseasonid != 0) && ($defaultmatchtypeid == 0)) {
  $whereClause = "WHERE M.MatchSeasonID = '$defaultseasonid'
	          AND M.MatchTypeID = MT.MatchTypeID
	          AND M.MatchOpponent = O.OpponentID ";
} elseif (($defaultseasonid == 0) && ($defaultmatchtypeid == 0)) {
  $whereClause = "WHERE M.MatchTypeID = MT.MatchTypeID
	          AND M.MatchOpponent = O.OpponentID ";
}
 
$orderByClause = "ORDER BY M.MatchDateTime";
 
// Execute query.
$sql = $selectClause . $whereClause . $orderByClause;
$get_matches = mysql_query($sql, $connection) or die(mysql_error());
 
// Loop round fixtures (which come back from database in date order).
while($data = mysql_fetch_array($get_matches))
{
  // Print a month header row each time we hit a new month.
  if ($data['month'] <> $lastMonth) {
    echo "<tr>\n";
    echo '<td align="left" valign="middle" bgcolor="#009933" colspan="9">';
    echo '<font color="#FFffff"><b>' . $data['month'] . "</b></font>";
    echo "</td>\n";
    echo "</tr>\n\n";
    $lastMonth = $data['month'];
  }
 
  // Assign home/away based vars.
  if ($data['place'] == 1) {
    $placeBg = $bg4;
    $venue = "H";
  } else {
    $placeBg = $bg3;
    $venue = "A";
  }
 
  // Print date, ko time and venue.
  echo"<tr>\n";
  echo '<td align="left" valign="middle" bgcolor="' . $placeBg . '">';
  echo $data['date'];
  echo "</td>\n";
 
  echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">';
  echo $data['time'];
  echo "</td>\n";
 
  echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">';
  echo $venue;
  echo "</td>\n";
 
  // Print opponent team name (as a link if possible).
  echo '<td align="left" valign="middle" bgcolor="' . $placeBg . '">';
  if ($data['oppid'] == 1) {
    echo '$data[opponent]';
  } else {
    echo '<a href="opponent.php?opp=' . $data['oppid'] . '">' . $data['opponent'] . "</a>";
  }
  echo "</td>\n";
 
  // Print competition type.
  echo '<td align="left" valign="middle" bgcolor="' . $placeBg . '">';
  echo $data['typename'];
  if ($data['additype'] != '') {
    echo " / " . $data['additype'];
  }
  echo "</td>\n";
 
  // Print result, attendance and match report.
  if ($data['goals'] == NULL || $data['goals_opponent'] == NULL) {
    // No goals recorded - match can't have been played yet - print empty fields.
    echo '<td bgcolor="' . $placeBg . '">&nbsp;</td>' . "\n";
    echo '<td bgcolor="' . $placeBg . '">&nbsp;</td>' . "\n";
    echo '<td bgcolor="' . $placeBg . '">&nbsp;</td>' . "\n";
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">';
    if ($data['prewtext'] == '') {
      echo "&nbsp;";
    } else {
      echo '<a href="preview.php?id=' . $data['id'] . '">' . $txt_preview . "</a>";
    }
    echo "</td>\n";
  } else {
    // Goals recorded - figure out result and score - print required fields.
    if ($data['penalty_goals'] == NULL || $data['penalty_goals_opponent'] == NULL) {
      if ($data['goals'] > $data['goals_opponent'])
	$result = "<b>W</b>";
      elseif ($data['goals'] < $data['goals_opponent'])
	$result = "L";
      else
	$result = "D";
      $score = $data['goals'] . " - " . $data['goals_opponent'];
    } else {
      if ($data['penalty_goals'] > $data['penalty_goals_opponent'])
	$result = "<b>W</b>";
      else
	$result = "L";
      $score = $data['goals'] . " - " . $data['goals_opponent'] . " (" . $data['penalty_goals'] . " - " . $data['penalty_goals_opponent'] . ")";
    }
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">' . $result . "</td>\n";
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">' . $score . "</td>\n";
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">' . $data['att'] . "</td>\n";
    if($data['publish'] == 1)
    {
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '"><a href="matchdetails.php?id=' . $data['id'] . '">Report</td></a></td>' . "\n";
    }
    else
    {
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '"> </td></a></td>' . "\n";
    }
  }
 
  echo "</tr>\n\n";
}
 
// Free resultset.
mysql_free_result($get_matches);
 
?>

Open in new window

chrismkAsked:
Who is Participating?
 
chrismkAuthor Commented:
Solution provided by a colleague!

Added brackets to the FROM and ON part of the statement. Added P.PreviewMatchID to the SELECT part of the statement and each table selection needs an AS before its variable name e.g. tplss_matches M becomes tplss_matches AS M etc.
 
          "SELECT
          M.MatchID AS id,
          M.MatchAdditionalType AS additype,
          O.OpponentName AS opponent,
          O.OpponentID AS oppid,
          M.MatchGoals AS goals,
          M.MatchGoalsOpponent AS goals_opponent,
          M.MatchPenaltyGoals AS penalty_goals,
          M.MatchPenaltyGoalsOpponent AS penalty_goals_opponent,
          M.MatchOvertime AS overtime,
          M.MatchPenaltyShootout AS penalty_shootout,
          DATE_FORMAT(M.MatchDateTime, '%M %Y') AS month,
          DATE_FORMAT(M.MatchDateTime, '%a %e') AS date,
          DATE_FORMAT(M.MatchDateTime, '%H:%i') AS time,
          M.MatchPlaceID AS place,
          M.MatchAttendance AS att,
          M.MatchPublish AS publish,
          MT.MatchTypeName AS typename,
          P.PreviewText AS prewtext,
          P.PreviewMatchID    
          FROM (tplss_matches AS M, tplss_matchtypes AS MT, tplss_opponents AS O)
          LEFT OUTER JOIN tplss_previews AS P ON (P.PreviewMatchID = M.MatchID) ";
0
 
usarianOwnerCommented:
Check the table in the database named tplss_matches.  Make sure there is a column named MatchID
0
 
chrismkAuthor Commented:
Yes even I had thought of that :))
The table is called tplss_matches and the database is roverss_stats
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dansotoCommented:
Why not just simply it by identifying the column as:

tplss_matches.MatchID instead of the table alias "M".  

sometimes I get tired banging my head looking for the answers and just do it the easy way :)
0
 
nizsmoDeveloperCommented:
chrismk:

Also note that it is case sensitive, so make sure your MatchID is exactly cased that way (in your database) and not something like MatchId.
0
 
chrismkAuthor Commented:
I'll check and try those suggestions!
Remember I inherited this file and it worked fine under V4
0
 
chrismkAuthor Commented:
Is not using an alias really going to make a difference?
MatchID is correct
0
 
dansotoCommented:
It's worth 2 seconds to see if it does.
0
 
chrismkAuthor Commented:
So why does it work in V4 then?
And how does that affect line 22?
0
 
chrismkAuthor Commented:
The aliases are used throughout the program and other parts work OK.
0
 
dansotoCommented:
Honestly, I'm not sure right now.  Did you try it?  If so, what was the result?
0
 
chrismkAuthor Commented:
Now I get
Unknown column 'tplss_matches.MatchID' in 'field list'

From what I've read but not understood is that the problem lies in how V5 treats the JOIN statement as opposed to V4
0
 
dansotoCommented:
Trying doing joins on the remaining tables....since I don't know the structure of your tables I can't really list it out.

tplss_matches join to some other table
tpless_matchtypes join
tplss_opponents join...

Until all tables are joined by some relationship.  I'm not sure if that's a restriction in 5+ or not..

FROM tplss_matches M, tplss_matchtypes MT, tplss_opponents O
LEFT OUTER JOIN tplss_previews P ON M.MatchID = P.PreviewMatchID ";
0
 
chrismkAuthor Commented:
I've no idea what you mean!
0
 
dansotoCommented:
How about if you list the tables in the query and their columns and I'll see what I can determine the relationships.
0
 
chrismkAuthor Commented:
Like this?

tplss_matches
  MatchID AS id,
  MatchAdditionalType AS additype,
  MatchGoals AS goals,
  MatchGoalsOpponent AS goals_opponent,
  MatchPenaltyGoals AS penalty_goals,
  MatchPenaltyGoalsOpponent AS penalty_goals_opponent,
  MatchOvertime AS overtime,
  MatchPenaltyShootout AS penalty_shootout,
  MatchDateTime, '%M %Y') AS month,
  MatchPlaceID AS place,
  MatchAttendance AS att,
  MatchPublish AS publish,

tplss_opponents
  OpponentName AS opponent,
  OpponentID AS oppid,

tplss_matchtypes
  MatchTypeName AS typename,

tplss_previews
  PreviewText AS prewtext

FROM tplss_matches M, tplss_matchtypes MT, tplss_opponents O
LEFT OUTER JOIN tplss_previews P ON tplss_matches.MatchID = P.PreviewMatchID ";
0
 
chrismkAuthor Commented:
I've uploaded the original matches.php file that camewith the program and that returns the same error.

This is the link that mentions the change from previous versions:
http://dev.mysql.com/doc/refman/5.0/en/join.html
0
 
nizsmoDeveloperCommented:
Well done glad you found your solution, and thank you for sharing with us!
0
 
Computer101Commented:
Closed, 500 points refunded.
Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.