Help with error "Syntax near..."

Darkmatter5 · March 27, 2009

Here's my query

SELECT gs.gs_id, g.game_id, g.title, g.url_wikipedia, ge.genre FROM games AS g INNER JOIN game_genres AS gg ON gg.game_id=g.game_id INNER JOIN genres AS ge ON ge.genre_id=gg.genre_id INNER JOIN game_systems AS gs ON gs.game_id=g.game_id WHERE g.title LIKE 'j%' GROUP BY g.title ORDER BY g.title ASC LIMIT 0, 20

Here' the 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 '.title LIKE 'j%'' at line 1

The query runs, but produces this error, what am I doing wrong?

Mchl · March 27, 2009

Put gs.game_id=g.game_id in parentheses

Darkmatter5 · March 27, 2009

I tried the following, but it broke and returned nothing.

SELECT gs.gs_id, g.game_id, g.title, g.url_wikipedia, ge.genre FROM games AS g INNER JOIN game_genres AS gg ON gg.game_id=g.game_id INNER JOIN genres AS ge ON ge.genre_id=gg.genre_id INNER JOIN game_systems AS gs ON "gs.game_id=g.game_id" WHERE g.title LIKE 'j%' GROUP BY g.title ORDER BY g.title ASC LIMIT 0, 20

Mchl · March 27, 2009

Parentheses are ( and )

Darkmatter5 · March 30, 2009

*slap forehead* of course they are. :-)

Darkmatter5 · March 30, 2009

Okay here's the query now.

SELECT gs.gs_id, g.game_id, g.title, g.url_wikipedia, ge.genre FROM games AS g INNER JOIN game_genres AS gg ON gg.game_id=g.game_id INNER JOIN genres AS ge ON ge.genre_id=gg.genre_id INNER JOIN game_systems AS gs ON (gs.game_id=g.game_id) WHERE gs.system_id=6 AND g.title LIKE 'h%' GROUP BY g.title ORDER BY g.title ASC LIMIT 0, 25

Still no go, still same 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 '.system_id=6 AND g.title LIKE 'h%'' at line 1"

fenway · April 1, 2009

Ignoring the fact that you're misusing GROUP BY for a minute, that's very odd -- what if you remove the where clause?

Darkmatter5 · April 2, 2009

Okay this query is constructed by php based on user selected parameters. Here's the code that builds the query.

$quebeg="SELECT gs.gs_id, g.game_id, g.title, g.url_wikipedia, ge.genre
              FROM games AS g
              INNER JOIN game_genres AS gg ON gg.game_id=g.game_id
              INNER JOIN genres AS ge ON ge.genre_id=gg.genre_id
              INNER JOIN game_systems AS gs ON gs.game_id=g.game_id";
$queend="GROUP BY g.title ORDER BY g.title ASC";
if(is_numeric($system)) {
  if(is_numeric($genre)) {
    if($char=='num') { $quewr="WHERE gs.system_id=$system AND ge.genre_id=$genre AND g.title REGEXP '^[0-9]'"; }
    elseif(is_string($char)) { ($char=='all'?$quewr="WHERE gs.system_id=$system AND ge.genre_id=$genre":$quewr="WHERE gs.system_id=$system AND ge.genre_id=$genre AND g.title LIKE '$char%'"); }
  }
  else {
    if($char=='num') { $quewr="WHERE gs.system_id=$system AND g.title REGEXP '^[0-9]'"; }
    elseif(is_string($char)) { ($char=='all'?$quewr="WHERE gs.system_id=$system":$quewr="WHERE gs.system_id=$system AND g.title LIKE '$char%'"); }
  }
}
else {
  if(is_numeric($genre)) {
    if($char=='num') { $quewr="WHERE ge.genre_id=$genre AND g.title REGEXP '^[0-9]'"; }
    elseif(is_string($char)) { ($char=='all'?$quewr="WHERE ge.genre_id=$genre":$quewr="WHERE ge.genre_id=$genre AND g.title LIKE '$char%'"); }
  }
  else {
    if($char=='num') { $quewr="WHERE g.title REGEXP '^[0-9]'"; }
    elseif(is_string($char)) { ($char=='all'?$quewr=" ":$quewr="WHERE g.title LIKE '$char%'"); }
  }
}

Then the query is constructed by using all three query segments. $quebeg, $quewr and $queend

This code is all run within a function where the parameters of $system, $genre and $char are passed. If $system is 'all' and $genre is 'all' I get no errors, as $quewr is " " and has no WHERE portion. If $system is numeric or $genre is numeric $quewr has a WHERE clause and creates the error. Any ideas?

fenway · April 2, 2009

Seems like it doesn't like the gs...

Mchl · April 2, 2009

Weird errors require weird methods... what happens if you change gs alias to something else?

(Not much hope...but it's weird )

fenway · April 2, 2009

Could you post the table structure so we can try it on our end?

Darkmatter5 · April 2, 2009

I'll do my best to show you my db structure.

CREATE TABLE `game_systems` (

`gs_id` int(11) NOT NULL auto_increment,

`game_id` int(11) NOT NULL,

`system_id` int(11) NOT NULL,

`upc` varchar(13) default NULL,

`url_gamespot` varchar(500) default NULL,

`url_ign` varchar(500) default NULL,

`temp_image_name` varchar(200) default NULL,

PRIMARY KEY (`gs_id`)

) ENGINE=MyISAM AUTO_INCREMENT=187 DEFAULT CHARSET=latin1;

CREATE TABLE `games` (

`game_id` int(11) NOT NULL auto_increment,

`title` varchar(100) NOT NULL,

`descr` varchar(3000) default NULL,

`note2admin` varchar(500) default NULL,

`add_date` datetime NOT NULL,

`url_wikipedia` varchar(500) default NULL,

PRIMARY KEY (`game_id`)

) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

CREATE TABLE `genres` (

`genre_id` int(11) NOT NULL auto_increment,

`genre` varchar(50) NOT NULL,

PRIMARY KEY (`genre_id`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

CREATE TABLE `game_genres` (

`game_id` int(11) NOT NULL,

`genre_id` int(11) NOT NULL default '1'

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Is this what you mean?

Also just so you know I use Navicat 8 to do db stuff and in Navicat I get no error, This error is only generated while run in PHP. Sorry if I didn't mention that before.

PFMaBiSmAd · April 2, 2009

gs (GS) is a regular expression character name. Perhaps when it is followed by the . it gets parsed as something it is not. Do as Mchl posted and try a different alias name.

Darkmatter5 · April 2, 2009

I changed all references to gsys, but the error still exists.

fenway · April 3, 2009

gs (GS) is a regular expression character name.

Really?

Also just so you know I use Navicat 8 to do db stuff and in Navicat I get no error, This error is only generated while run in PHP. Sorry if I didn't mention that before.

No kidding... well, then it's a php parsing issue -- try single quotes.

Darkmatter5 · April 3, 2009

Do you mean

"SELECT gs.gs_id, g.game_id, g.title, g.url_wikipedia, ge.genre FROM games AS g Inner Join game_genres AS gg ON gg.game_id=g.game_id Inner Join genres AS ge ON ge.genre_id=gg.genre_id Inner Join game_systems AS gs ON gs.game_id=g.game_id WHERE 'gs.system_id'=6 GROUP BY g.title ORDER BY g.title ASC LIMIT 0, 20"

That breaks the query and returns nothing.

Okay I didn't want to have to post my whole function, but I'm going to just go ahead and do it.

function item_listing($category,$system,$genre,$char,$item) {
            if(isset($_SESSION['member_id'])) {
                $query=mysql_query("SELECT * FROM members WHERE member_id=$_SESSION[member_id]") or die(mysql_error());
                $meminfo=mysql_fetch_array($query);
            }
            $i=0;
            (isset($meminfo['rowsperpage'])?$rowsPerPage=$meminfo['rowsperpage']:$rowsPerPage=20);
            $pageNum=1;
            if(isset($_GET['page'])) { $pageNum=$_GET['page']; }
            $offset=($pageNum-1)*$rowsPerPage;
            switch ($category) {
                case "dvd":
                    echo "Movies";
                    break;
                case "games";
                    $quebeg="SELECT gs.gs_id, g.game_id, g.title, g.url_wikipedia, ge.genre
                             FROM games AS g
                             Inner Join game_genres AS gg ON gg.game_id=g.game_id
                             Inner Join genres AS ge ON ge.genre_id=gg.genre_id
                             Inner Join game_systems AS gs ON gs.game_id=g.game_id";
                    $queend="GROUP BY g.title ORDER BY g.title ASC";
                    if(is_numeric($system)) {
                        if(is_numeric($genre)) {
                            if($char=='num') { $quewr="WHERE gs.system_id=$system AND ge.genre_id=$genre AND g.title REGEXP '^[0-9]'"; }
                            elseif(is_string($char)) { ($char=='all'?$quewr="WHERE gs.system_id=$system AND ge.genre_id=$genre":$quewr="WHERE gs.system_id=$system AND ge.genre_id=$genre AND g.title LIKE '$char%'"); }
                        }
                        else {
                            if($char=='num') { $quewr="WHERE gs.system_id=$system AND g.title REGEXP '^[0-9]'"; }
                            elseif(is_string($char)) { ($char=='all'?$quewr="WHERE 'gs.system_id'=$system":$quewr="WHERE gs.system_id=$system AND g.title LIKE '$char%'"); }
                        }
                    }
                    else {
                        if(is_numeric($genre)) {
                            if($char=='num') { $quewr="WHERE ge.genre_id=$genre AND g.title REGEXP '^[0-9]'"; }
                            elseif(is_string($char)) { ($char=='all'?$quewr="WHERE ge.genre_id=$genre":$quewr="WHERE ge.genre_id=$genre AND g.title LIKE '$char%'"); }
                        }
                        else {
                            if($char=='num') { $quewr="WHERE g.title REGEXP '^[0-9]'"; }
                            elseif(is_string($char)) { ($char=='all'?$quewr=" ":$quewr="WHERE g.title LIKE '$char%'"); }                        }
                    }
                    $quefull="$quebeg $quewr $queend LIMIT $offset, $rowsPerPage";
                    echo "$quefull<p>";
                    $result=mysql_query($quefull) or die(mysql_error());
                    if(mysql_num_rows($result)==0) { echo "No games fitting that query found!"; }
                    else {
                        echo "<table class='listings' width='700' cellpadding='4' cellspacing='0' border='0'><tr><th>Title</th><th width='200'>System(s)</th><th width='100'>Genre</th><th>Wikipedia link</th><th width='50'> </th></tr>";
                        while($row1=mysql_fetch_array($result)) {
                            if($i==0) { $tgcolor="#B0B0B0"; }
                            elseif($i==1) { $tgcolor="#888888"; }
                            $query="SELECT GROUP_CONCAT(DISTINCT s.name ORDER BY s.name ASC SEPARATOR ', ') AS sysnames
                                    FROM games AS g
                                    INNER JOIN game_systems AS gs ON g.game_id=gs.game_id
                                    INNER JOIN systems AS s ON gs.system_id=s.system_id
                                    WHERE g.game_id=$row1[game_id]";
                            $row2=mysql_fetch_array(mysql_query($query));
                            echo "<tr bgcolor=$tgcolor><td class='medium_text'><b><a href='title.php?s=" .($_GET['s']==null?"all":"$_GET[s]"). "&g=all&c=all&i=$row1[gs_id]'>$row1[title]</a></b></td><td align='center' class='small_text'>" .wordwrap($row2['sysnames'],30,"<br>",FALSE). "</td><td align='center' class='small_text'>$row1[genre]</td><td align='center'>";
                            if(empty($row1['url_wikipedia'])) { echo " "; }
                            else { echo "<a href='$row1[url_wikipedia]'>Click here</a>"; }
                            if($meminfo['priv_admin']==1) { echo "</td><td><input type='button' onclick='parent.location=\"res_editgame.php?i=$row1[game_id]\"' value='Edit game'></td></tr>"; }
                            else { echo "</td><td> </td></tr>"; }
                            if($i==0) { $i=1; }
                            elseif($i==1) { $i=0; }
                        }
                        $quecnt="SELECT COUNT(*) AS numrows FROM games$quewr";
                        $rescnt=mysql_query($quecnt) or die(mysql_error());
                        $row=mysql_fetch_array($rescnt);
                        $numrows=$row['numrows'];
                        //How many pages we have
                        $maxPage=ceil($numrows/$rowsPerPage);
                        $self=$_SERVER['PHP_SELF'];
                        $nav='';
                        for($page=1; $page<=$maxPage; $page++) {
                            if($page==$pageNum) { $nav.=" $page "; }
                            else { $nav.=" <a href=\"$self?s=$_GET[s]&g=$_GET[g]&c=" .(($_GET['c']!=null)?"$_GET[c]":"all"). "&page=$page\">$page</a> "; }
                        }
                        if($pageNum>1) {
                            $page=$pageNum-1;
                            $prev=" <a href=\"$self?s=$_GET[s]&g=$_GET[g]&c=" .(($_GET['c']!=null)?"$_GET[c]":"all"). "&page=$page\">[Prev]</a> ";
                            $first=" <a href=\"$self?s=$_GET[s]&g=$_GET[g]&c=" .(($_GET['c']!=null)?"$_GET[c]":"all"). "&page=1\">[First Page]</a> ";
                        }
                        else {
                            $prev=' ';
                            $first=' ';
                        }
                        if($pageNum<$maxPage) {
                            $page=$pageNum+1;
                            $next=" <a href=\"$self?s=$_GET[s]&g=$_GET[g]&c=" .(($_GET['c']!=null)?"$_GET[c]":"all"). "&page=$page\">[Next]</a> ";
                            $last=" <a href=\"$self?s=$_GET[s]&g=$_GET[g]&c=" .(($_GET['c']!=null)?"$_GET[c]":"all"). "&page=$maxPage\">[Last Page]</a> ";
                        }
                        else {
                            $next=' ';
                            $last=' ';
                        }
                        echo "<tr><td colspan='4' align='center'><span class='large_text'>$first $prev $nav $next $last</span></td></tr>";
                        echo "</table>";
                    }
                    break;
            }
        }

fenway · April 3, 2009

That's still in double-quotes.

Darkmatter5 · April 3, 2009

Oh you meant

$quebeg='SELECT gs.gs_id, g.game_id, g.title, g.url_wikipedia, ge.genre
         FROM games AS g
         Inner Join game_genres AS gg ON gg.game_id=g.game_id
         Inner Join genres AS ge ON ge.genre_id=gg.genre_id
         Inner Join game_systems AS gs ON gs.game_id=g.game_id';
$queend='GROUP BY g.title ORDER BY g.title ASC';

etc..., that still produces the error.

fenway · April 3, 2009

I have no idea... there's nothing "wrong" with the sql syntactically... if you can run it with navicat, then php is to blame.

Darkmatter5 · April 3, 2009

Thanks anyways! I think I'm going to go completely rewrite that whole code segment. Maybe I stumble onto something along the way.

Sign In

Help with error "Syntax near..."

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information