Help with error "Syntax near..."

[Darkmatter5]

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]

Put gs.game_id=g.game_id in parentheses

[Darkmatter5]

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]

Parentheses are ( and ) 

 

 

[Darkmatter5]

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

[Darkmatter5]

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]

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

[Darkmatter5]

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]

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

[Mchl]

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

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

[fenway]

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

[Darkmatter5]

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]

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]

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

[fenway]

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]

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]

That's still in double-quotes.

[Darkmatter5]

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]

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]

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