Solved

Unknown Column in on clause after Upgrade to V5

Posted on 2007-12-01
20
715 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

831 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