MySQL syntax error in SELECT query

Question

Hi everyone i'm getting this syntax problem and i don't see anything wrong with my query. I'm kind of new at SQL as i'm still an undergrad. I'd really appreciate it if you could help me with this. This is the code. :

$results = mysql_query("SELECT event.nameevent,event.eventid
    FROM event,sub
    WHERE sub.userid=$userid AND event.eventid=sub.orgid AND 
    EXTRACT(YEAR FROM startdate)=$year AND EXTRACT(MONTH FROM startdate)=$month
    AND EXTRACT(DAY FROM startdate)=$list_day") 
or die(mysql_error());

if(mysql_num_rows($results) >0 )
{
    while($row=mysql_fetch_assoc($results))
    {
        $nameevent = $row['nameevent'];
        $eventid   = $row['eventid'];
        $calendar.="<a href='memberview.php?eventid=$eventid'>".$nameevent."</a>";
        $calendar.= "<br>";
    }
}
else
{
    $calendar.='you have no events today';
}

It returns 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 'AND event.eventid=sub.orgid AND EXTRACT(YEAR FROM startdate)=2010 AND EXTRACT( at line 3

I tested this code in a blank page and it works however when i integrate it inside the calendar page it doesn't work. Any suggestions? I have tested $userid,$year,$month and $day and it all returns a value.

Answer 1

mysql_query("
SELECT `event.nameevent`, `event.eventid` 
FROM `event`,`sub` 
WHERE `sub.userid`='" . $userid . "'
AND `event.eventid`=`sub.orgid` 
AND EXTRACT(YEAR FROM `startdate`)='" . $year . "' 
AND EXTRACT(MONTH FROM `startdate`)='" . $month . "' 
AND EXTRACT(DAY FROM `startdate`)='" . $list_day . "'
");

You should escape your data and DB fields.

Answer 2

You'll find it useful when starting out to create a variable containing your sql statement.

$sql = "SELECT event.nameevent,event.eventid FROM event,sub WHERE sub.userid=$userid AND event.eventid=sub.orgid AND EXTRACT(YEAR FROM startdate)=$year AND EXTRACT(MONTH FROM startdate)=$month AND EXTRACT(DAY FROM startdate)=$list_day" ;

$results = mysql_query( $sql ) ;`

This affords you the ability to

echo $sql ;

onto the page, where you can a) take a close look at it and b) paste the statement directly into you sql manager.

Not saying this is answering your question, but providing you with a strategy to divide and conquer PHP and SQL errors - especially common quoting errors.

Jolly useful as a sanity check that there actually is corresponding data in your database in the case of zero results.

Answer 3

Make sure that the $userid variable contains something. If it's an empty string you'll end up with

SELECT event.nameevent,event.eventid
FROM event,sub
WHERE sub.userid= AND

And this will throw an error.