Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts

    SQL syntax error when using with php - RESOLVED

    I am using this query inside of a php document.
    PHP Code:
    $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
    Code:
    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.
    Last edited by _Aerospace_Eng_; 09-04-2006 at 09:04 PM.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #3
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,092
    Thanks
    2
    Thanked 23 Times in 23 Posts
    I find it helpful to put a

    Code:
    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.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #4
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    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.
    PHP Code:
    //---------------------------------

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

    }
    Edit: I got it. It was in the if else statement at the bottom. I just used stripslashes on the echo.
    Last edited by _Aerospace_Eng_; 09-04-2006 at 05:59 PM.
    ||||If you are getting paid to do a job, don't ask for help on it!||||


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •