Solved

Unknown Column in on clause after Upgrade to V5

Posted on 2007-12-01
20
708 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
Comment Utility
Check the table in the database named tplss_matches.  Make sure there is a column named MatchID
0
 

Author Comment

by:chrismk
Comment Utility
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
Comment Utility
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
 
LVL 21

Expert Comment

by:nizsmo
Comment Utility
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
Comment Utility
I'll check and try those suggestions!
Remember I inherited this file and it worked fine under V4
0
 

Author Comment

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

Expert Comment

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

Author Comment

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

Author Comment

by:chrismk
Comment Utility
The aliases are used throughout the program and other parts work OK.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Expert Comment

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

Author Comment

by:chrismk
Comment Utility
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
Comment Utility
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
Comment Utility
I've no idea what you mean!
0
 
LVL 7

Expert Comment

by:dansoto
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Well done glad you found your solution, and thank you for sharing with us!
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Closed, 500 points refunded.
Computer101
EE Admin
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now