Jump to content

Error message with mysql v.5.0.45

steve hyde

By steve hyde
in MySQL Help

 Share

Recommended Posts

steve hyde Newbie

steve hyde

Posted
Posted

My hosting comppany installed mysql v.5.0.45 two days ago. Since then the following script which was working fine now returns the following error:

 

Unknown column 'M.MatchID' in 'on clause'

 

I've no programming skills and was wondering if someone could check out the script below and give me a few pointers in the right direction. Thanks

 

<?php

/*

***************************************************************************

*  tplSoccerStats

*  -----------------------------------------------------------------------

*  Copyright: TPL Design

*  email:    info@tpl-design.com

*  www:      www.tpl-design.com/tplsoccerstats

***************************************************************************

*/

 

// Starts a new session or resumes the current one.

session_start();

 

// Get db host, db username, db password, and db name from admin/user.php

// (each team has its own db).

include('admin/user.php');

 

// Establish db connection. Select appropriate team db.

$connection = mysql_connect("$host", "$user", "$password") or die(mysql_error());

mysql_select_db("$txt_db_name", $connection) or die(mysql_error());

 

// Get preferences stored in db.

$sql = "SELECT * FROM tplss_preferences WHERE ID = '0'";

$pref = mysql_query($sql, $connection) or die(mysql_error());

$pdata = mysql_fetch_array($pref);

mysql_free_result($pref);

 

// Include php preferences.

include('preferences.inc');

 

// Use session defaults if available, otherwise set them from database values.

if ((!isSet($_SESSION['defaultseasonid_tplss']))    ||

    (!isSet($_SESSION['defaultmatchtypeid_tplss'])) ||

    (!isSet($_SESSION['defaultlanguage_tplss'])))    {

 

    $_SESSION['defaultseasonid_tplss'] = $pdata['DefaultSeasonID'];

    $_SESSION['defaultmatchtypeid_tplss'] = $pdata['DefaultMatchTypeID'];

    $_SESSION['defaultlanguage_tplss'] = $pdata['DefaultLanguage'];

}

$defaultseasonid = $_SESSION['defaultseasonid_tplss'];

$defaultmatchtypeid = $_SESSION['defaultmatchtypeid_tplss'];

$defaultlanguage = $_SESSION['defaultlanguage_tplss'];

 

// Include language based global vars, in our case english, e.g. $txt_preview = 'Preview'

include('language.inc');

 

// Include pafc banner and menu bar.

include('header.php');

 

// Start an html form.

echo '<form method="post" action="change.php">' . "\n";

 

// Print images at top of form if we have them (we don't).

$image_url = "images/header.jpg";

$image_url2 = "images/header.gif";

if(file_exists($image_url) || file_exists($image_url2))

{

  echo '<table align="center" width="' . $tb_width . '" cellspacing="0" cellpadding="0" border="0">' . "\n";

  echo "<tr>\n";

  echo '<td align="center">' . "\n";

 

  if (file_exists($image_url)) {

    echo '<img src="' . $image_url . '" ALT=""><br>' . "\n";

  }

  elseif (file_exists($image_url2)) {

    echo '<img src="' . $image_url2 . '" ALT="">' . "\n";

  }

  echo "</td></tr></table>\n\n";

}

 

?>

 

<!-- Print change bar table -->

<table align="center" width="<?php echo $tb_width ?>" cellspacing="0" cellpadding="0" border="0" bgcolor="<?php echo $border_c ?>">

<tr>

<td>

<table width="100%" cellspacing="1" cellpadding="5" border="0">

<tr>

<td bgcolor="<?php echo $inside_c ?>" align="center">

<?= $txt_change ?>:

<select name="season">

<option value="0"><?= $txt_all ?></option>

<?php

$sql = "SELECT * FROM tplss_seasonnames WHERE SeasonPublish = '1' ORDER BY SeasonName DESC";

$get_seasons = mysql_query($sql, $connection) or die(mysql_error());

while($data = mysql_fetch_array($get_seasons))

{

  if($data['SeasonID'] == $defaultseasonid)

    echo '<option value="' . $data['SeasonID'] . '" SELECTED>' . $data['SeasonName'] . "</option>\n";

  else

    echo '<option value="' . $data['SeasonID'] . '">' . $data['SeasonName'] . "</option>\n";

}

mysql_free_result($get_seasons);

?>

</select>

<select name="matchtype">

<option value="0"><?= $txt_all ?></option>

<?php

$sql = "SELECT * FROM tplss_matchtypes ORDER BY MatchTypeName";

$get_types = mysql_query($sql, $connection) or die(mysql_error());

while($data = mysql_fetch_array($get_types))

{

  if($data['MatchTypeID'] == $defaultmatchtypeid)

    echo '<option value="' . $data['MatchTypeID'] . '" SELECTED>' . $data['MatchTypeName'] . "</option>\n";

  else

    echo '<option value="' . $data['MatchTypeID'] . '">' . $data['MatchTypeName'] . "</option>\n";

}

mysql_free_result($get_types);

?>

</select> <input type="submit" name="submit" value=">>">

 <?= $txt_moveto ?>: <select name="changeto">

<option value="0"><?= $txt_player_statistics ?></option>

<option value="2"><?= $txt_recordbook ?></option>

<option value="3"><?= $txt_searchengine ?></option>

<option value="4"><?= $txt_manual ?></option>

</select> <input type="submit" name="changepage" value=">>">

</td>

</tr>

</table>

</td>

</tr>

</table>

 

<!-- Print outer fixture tables -->

<table align="center" width="<?php echo $tb_width ?>" cellspacing="0" cellpadding="0" border="0" bgcolor="<?php echo $border_c ?>">

<tr>

<td>

<table width="100%" cellspacing="1" cellpadding="5" border="0">

<tr>

<td bgcolor="<?php echo $inside_c ?>" align="center">

 

<!-- Print inner fixture table -->

<table width="<?= $tb2_width ?>%" cellspacing="1" cellpadding="2" border="0">

 

<tr>

<td align="left" valign="middle" bgcolor="#000066">

<font color="#FFffff"><b>Date</b></font>

</td>

 

<td align="left" valign="middle" bgcolor="#000066">

<font color="#FFffff"><b>KO</b></font>

</td>

 

<td align="center" valign="middle" bgcolor="#000066">

<font color="#FFffff"><b>V</b></font>

</td>

 

<td align="left" valign="middle" bgcolor="#000066">

<font color="#FFffff"><b>Opponent</b></font>

</td>

 

<td align="left" valign="middle" bgcolor="#000066">

<font color="#FFffff"><b>Competition</b></font>

</td>

 

<td align="center" valign="middle" bgcolor="#000066" colspan="2">

<font color="#FFffff"><b>Result</b></font>

</td>

 

<td align="center" valign="middle" bgcolor="#000066">

<font color="#FFffff"><b>Att</b></font>

</td>

 

<td align="center" valign="middle" bgcolor="#000066">

<font color="#FFffff"><b>MR</b></font>

</td>

</tr>

 

<?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="#000066" 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 = $bg1;

    $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="left" 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 . '"> </td>' . "\n";

    echo '<td bgcolor="' . $placeBg . '"> </td>' . "\n";

    echo '<td bgcolor="' . $placeBg . '"> </td>' . "\n";

    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">';

    if ($data['prewtext'] == '') {

      echo " ";

    } 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 = "W";

      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 = "W";

      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";

    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '"><a href="matchdetails.php?id=' . $data['id'] . '">Report</td></a></td>' . "\n";

  }

 

  echo "</tr>\n\n";

}

 

// Free resultset.

mysql_free_result($get_matches);

 

?>

</table>

 

</td>

</tr>

</table>

</td>

</tr>

</table>

<?php

// Print tpl soccers stats message.

include('bottom.txt');

?>

</form>

 

<?php

// Finish off html.

include('footer.php');

?>

Link to comment
https://forums.phpfreaks.com/topic/93659-error-message-with-mysql-v5045/
Share on other sites
fenway Advanced Member

fenway

Posted
Posted

Change this:

 

FROM tplss_matches M, tplss_matchtypes MT, tplss_opponents O
            LEFT OUTER JOIN tplss_previews P ON M.MatchID = P.PreviewMatchID ";

 

To this:

 

FROM ( tplss_matches M, tplss_matchtypes MT, tplss_opponents O )
            LEFT OUTER JOIN tplss_previews P ON M.MatchID = P.PreviewMatchID ";

 

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.