...

View Full Version : SQL syntax error when using with php - RESOLVED



_Aerospace_Eng_
09-04-2006, 04:48 PM
I am using this query inside of a php document.

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'";
I keep getting this error

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 's Cross Thistle' AND match_type='league' AND match_date >= '200
Can anyone spot out any syntax errors? I have a feeling that it might be too many ANDs in the query.

Fumigator
09-04-2006, 05:35 PM
The syntax looks fine... but it looks like you have a single quote in the value of the $teamname variable, which is breaking the query.. "something something's Cross Thistle". You should wrap your variables in the mysql_real_escape_string() function which will escape your quotes.

vinyl-junkie
09-04-2006, 05:58 PM
I find it helpful to put a


die($dbQuery);
immediately after a problematic query, then copy/paste that into Notepad or something and examine it for syntax problems. Many times, you can tell by that just how the code needs to be fixed.

_Aerospace_Eng_
09-04-2006, 06:18 PM
Thanks you two the mysql_escape_string() function is what I needed. vinyl-junkie your tip helped me figure out where I needed to change stuff. I read on php.net that mysql_escape_string() was deprecated and we should be using mysql_real_escape_string(). Does it really matter? One thing I noticed is now that I escaped the quote how do I get it to print out without the backslash escaping the quote? Here is the part where I think it will decide the order of the teams by points. I think I should be using stripslashes() but I'm not sure where to put it.

//---------------------------------

// Select all teams from database - This now starts the selecting for populating the league table

//---------------------------------

$dbQuery = "SELECT team_name, own_team ";

$dbQuery .= "FROM teams ORDER BY points_total DESC, goaldiff DESC";

$result = mysql_query($dbQuery) or die("Couldn't get file list");

while($row = mysql_fetch_array($result)) {

$teamname = mysql_real_escape_string($row["team_name"]);
$own_team = $row["own_team"];





//-------------------------------

// select total points for team

//-------------------------------

$getpoints = "SELECT sum(points) as points FROM league_table WHERE team_name = '$teamname' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $pointsresult = mysql_query($getpoints) or die($getpoints);



$points= mysql_result($pointsresult, 0, "points");


if ($points == '') {

$points = '0';

}













//-------------------------------

// select number of wins

//-------------------------------

$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'W' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$win=mysql_numrows($w_d_l);



//-------------------------------

// select number of losses

//-------------------------------

$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'L' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$lose=mysql_numrows($w_d_l);



//-------------------------------

// select number of draws

//-------------------------------

$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'D' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$draw=mysql_numrows($w_d_l);



//-------------------------------

// select number of home wins

//-------------------------------

$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'W' AND home_away='home' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$homewin=mysql_numrows($w_d_l);



//-------------------------------

// select number of home losses

//-------------------------------

$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'L' AND home_away='home' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$homelose=mysql_numrows($w_d_l);



//-------------------------------

// select number of home draws

//-------------------------------

$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'D' AND home_away='home' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$homedraw=mysql_numrows($w_d_l);



//-------------------------------

// select number of away wins

//-------------------------------

$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'W' AND home_away='away' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$awaywin=mysql_numrows($w_d_l);



//-------------------------------

// select number of away losses

//-------------------------------

$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'L' AND home_away='away' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$awaylose=mysql_numrows($w_d_l);



//-------------------------------

// select number of wins

//-------------------------------

$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'W' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$win=mysql_numrows($w_d_l);



$dbQuery = "SELECT w_d_l ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND w_d_l = 'D' AND home_away='away' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $w_d_l = mysql_query($dbQuery) or die("Couldn't get file list");

$awaydraw=mysql_numrows($w_d_l);



//-------------------------------

// select games played

//-------------------------------

$dbQuery = "SELECT team_name ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $pld = mysql_query($dbQuery) or die("Couldn't get file list");

$totalpld=mysql_numrows($pld);



if ($totalpld == '') {

$totalpld = '0';

}









//-------------------------------

// select total goals for team

//-------------------------------

$dbQuery = "SELECT goals_for ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $goals_for = mysql_query($dbQuery) or die("Couldn't get file list");





$goalsfor = "SELECT sum(goals_for) as goalsf FROM league_table WHERE team_name = '$teamname' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $goalsfresult = mysql_query($goalsfor) or die(mysql_error());



$gfor= mysql_result($goalsfresult, 0, "goalsf");





//-------------------------------

// select total goals against for team

//-------------------------------

$dbQuery = "SELECT goals_against ";

$dbQuery .= "FROM league_table WHERE team_name = '$teamname' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $goals_against = mysql_query($dbQuery) or die("Couldn't get file list");





$goalsagainst = "SELECT sum(goals_against) as goalsa FROM league_table WHERE team_name = '$teamname' AND match_type='$league' AND match_date >= '$seasonstart' AND match_date <= '$seasonend'"; $goalsaresult = mysql_query($goalsagainst) or die(goalsagainst);



$gagainst= mysql_result($goalsaresult, 0, "goalsa");



if ($gfor == '') {

$gfor = '0';

}



if ($gagainst == '') {

$gagainst = '0';

}



$gd = ("$gfor" - "$gagainst");



//echo "$points";

//echo "$win_w_d_l";

//echo "$lose_w_d_l";

//echo "$draw_w_d_l";





if ($bgcolor === "$col_table_row")

{

$bgcolor = "$col_table_row2";

} else {

$bgcolor = "$col_table_row";

}





?>
<tr>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$position"; ?></td>
<?php

if ($own_team == 'yes') {

?>
<td width="35%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_link" ?>"><a href="teamprofile.php?fileId=<?php echo $row["team_name"]; ?>"><font size="4" color="#<?php echo $col_link ?>"><b><?php echo "$teamname" ?></b></a></td>
<?php

}

ELSE {

?>
<td width="35%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_link" ?>"><a href="teamprofile.php?fileId=<?php echo $row["team_name"]; ?>"><font color="#<?php echo $col_link ?>"><?php echo "$teamname" ?></a></td>
<?php

}

?>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$totalpld"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$gfor"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$gagainst"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$win"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$draw"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$lose"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$homewin"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$homedraw"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$homelose"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$awaywin"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$awaydraw"; ?></td>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$awaylose"; ?></td>
<?php if ($gd > '0') {

?>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "+$gd"; ?></td>
<?php

}

ELSE {



?>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$gd"; ?></td>
<?php

}

?>
<td width="4%" bgcolor="#<?php echo "$bgcolor" ?>"><font color="#<?php echo "$col_table_row_text" ?>"><?php echo "$points"; ?></td>
</tr>
<?php



++$position;

}

I got it. It was in the if else statement at the bottom. I just used stripslashes on the echo.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum