Hellp with failing code

[j4ymf]

Hello i have been running a fantasy football site for 7 years with this code but it is failing now with an error,  Why!

 

can anyone please help because its been so long since ive used php

 

<?php
function build_league_table($teamname) {

$connection = mysql_connect($host, $user,$password) or die ("Couldn't connect to server.");
$db = mysql_select_db($database, $connection) or die ("Couldn't select database.");

$sql = "SELECT a.id, a.name, a.team, a.price, a.points, c.NEW_POINTS, ";
$sql .= "IF (c.NEW_POINTS IS NOT NULL , a.points - c.NEW_POINTS, a.points) AS 'REAL_TOTAL', ";
$sql .= "IF (c.NEW_POINTS IS NOT NULL, 'SUB', 'NOTSUB') AS 'SUBQ' ";
$sql .= "FROM teams b, dreamteam a ";
$sql .= "LEFT OUTER JOIN subs2 c ON c.NEW_ID = a.ID ";
$sql .= "AND b.team_name = c.team_name ";
$sql .= "WHERE b.team_name = '$teamname' "; 
$sql .= "AND a.id ";
$sql .= "IN (b.player_1, b.player_2, b.player_3, b.player_4, b.player_5, ";
$sql .= "b.player_6, b.player_7, b.player_8, b.player_9, b.player_10, b.player_11) ";
$sql .= "ORDER BY a.id ";   // change to .team

$result = mysql_query($sql) or die ("Error when building the team league table!  (function 2)");



if ($myrow = mysql_fetch_array($result)){

 

many thanks jason

[joel24]

what error is it throwing?

[j4ymf]

thankyou

 

its saying

 

Error when building the team league table!  (function 2)

[joel24]

change the line to this and post the error it displays

$result = mysql_query($sql) or die ("Error when building the team league table!  (function 2)<br/>".mysql_error());

[j4ymf]

thank you joul

 

 

this is what i get now

 

Error when building the team league table! (function 2)

Unknown column 'b.team_name' in 'on clause'

 

[MasterACE14]

is the column actually called 'team_name' ? should double check that, according to mysql_error() it doesn't exist.

[j4ymf]

hello this is my site

 

this is my error page

http://www.manninglevels.co.uk/ccompare_teams1.php

[j4ymf]

hello

 

yes i do have a column called team_names in my teams table.

 

in my editor the text is red on this line

$sql = "SELECT a.id, a.name, a.team, a.price, a.points, c.NEW_POINTS, ";

 

thank you for your time jason

[jcbones]

Post a database table structure dump of the `teams` table.

 

If using phpMyAdmin, choose table, goto Export , un-check everything but 'Structure', make sure you DO NOT check 'Save as File'.  Copy and paste what is printed to the screen.  Post it here.

[j4ymf]

thankyou

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

 

CREATE TABLE teams (

  TEAM_NAME varchar(50) collate latin1_general_ci NOT NULL default '',

  TEAM_ADMIN int(2) NOT NULL default '0',

  PLAYER_1 int(3) NOT NULL default '0',

  PLAYER_2 int(3) NOT NULL default '0',

  PLAYER_3 int(3) NOT NULL default '0',

  PLAYER_4 int(3) NOT NULL default '0',

  PLAYER_5 int(3) NOT NULL default '0',

  PLAYER_6 int(3) NOT NULL default '0',

  PLAYER_7 int(3) NOT NULL default '0',

  PLAYER_8 int(3) NOT NULL default '0',

  PLAYER_9 int(3) NOT NULL default '0',

  PLAYER_10 int(3) NOT NULL default '0',

  PLAYER_11 int(3) NOT NULL default '0',

  PRIMARY KEY  (TEAM_NAME)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

[j4ymf]

some one else has told me to turn these around which has got rid of the error but it still cant build the table!

 

$sql .= "WHERE b.team_name = '$teamname' ";

$sql .= "AND b.team_name = c.team_name ";

 

confusing it has worked for so long until now!

[j4ymf]

a friend come to my help,

 

this is how we ended up!

 

$sql = "SELECT a.id, a.name, a.team, a.price, a.points, c.NEW_POINTS, ";
$sql .= "IF (c.NEW_POINTS IS NOT NULL , a.points - c.NEW_POINTS, a.points) AS 'REAL_TOTAL', ";
$sql .= "IF (c.NEW_POINTS IS NOT NULL, 'SUB', 'NOTSUB') AS 'SUBQ' ";
$sql .= "FROM teams b";
$sql .= "	INNER JOIN dreamteam a ";
$sql .= "		ON a.id IN (";
$sql .= "				b.player_1, b.player_2, b.player_3, b.player_4, b.player_5, ";
$sql .= "				b.player_6, b.player_7, b.player_8, b.player_9, b.player_10, b.player_11) ";
$sql .= "	LEFT OUTER JOIN subs2 c ON c.NEW_ID = a.ID AND b.team_name = c.team_name ";
$sql .= "WHERE b.team_name = '$teamname' ";
$sql .= "ORDER BY a.id"

 

thanks for everyones help today - jason