Solved

Unknown Column in on clause after Upgrade to V5

Posted on 2007-12-01
20
719 Views
Last Modified: 2008-02-01
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

0
Comment
Question by:chrismk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 2
  • +2
20 Comments
 
LVL 5

Expert Comment

by:usarian
ID: 20388740
Check the table in the database named tplss_matches.  Make sure there is a column named MatchID
0
 

Author Comment

by:chrismk
ID: 20388772
Yes even I had thought of that :))
The table is called tplss_matches and the database is roverss_stats
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20388841
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 21

Expert Comment

by:nizsmo
ID: 20388861
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
 

Author Comment

by:chrismk
ID: 20388915
I'll check and try those suggestions!
Remember I inherited this file and it worked fine under V4
0
 

Author Comment

by:chrismk
ID: 20389003
Is not using an alias really going to make a difference?
MatchID is correct
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20389012
It's worth 2 seconds to see if it does.
0
 

Author Comment

by:chrismk
ID: 20389024
So why does it work in V4 then?
And how does that affect line 22?
0
 

Author Comment

by:chrismk
ID: 20389035
The aliases are used throughout the program and other parts work OK.
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20389039
Honestly, I'm not sure right now.  Did you try it?  If so, what was the result?
0
 

Author Comment

by:chrismk
ID: 20389059
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
 
LVL 7

Expert Comment

by:dansoto
ID: 20389082
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
 

Author Comment

by:chrismk
ID: 20389102
I've no idea what you mean!
0
 
LVL 7

Expert Comment

by:dansoto
ID: 20389171
How about if you list the tables in the query and their columns and I'll see what I can determine the relationships.
0
 

Author Comment

by:chrismk
ID: 20389222
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
 

Author Comment

by:chrismk
ID: 20389269
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
 

Accepted Solution

by:
chrismk earned 0 total points
ID: 20391064
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
 
LVL 21

Expert Comment

by:nizsmo
ID: 20391758
Well done glad you found your solution, and thank you for sharing with us!
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20605541
Closed, 500 points refunded.
Computer101
EE Admin
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question