Page 1 of 1

error in SQL syntax

Posted: Sat Apr 17, 2004 11:55 pm
by James M.
Hi, i'm doing this project from a tutorial to learn php and I can't find the problem with this function, MySQL give me the error:
getEvents fatal 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 'clubs, events, areas, types WHERE clubs.id=events.eclub AN
can anyone help?


heres the code:

Code: Select all

function getEvents($club_id=0, $range=0, $area=0, $type=0)
{
    global $link;
    $query="SELECT clubs.name, events.*, areas.area as areaname,
    types.type as typename";
    $query .="FROM clubs, events, areas, types WHERE ";
    $query .="clubs.id=events.eclub
    AND events.area=areas.id
    AND events.type=types.id";
    if( ! empty($club_id) && $club_id !="ANY")
    {
        $query .="AND events.eclub='$club_id'";
    }
    if( ! empty($range))
    {
        $query .="AND events.edate >='$rangeї0]' AND
        events.edate<='$range&#1111;1]'";
    &#125;
    if(! empty($area) && $area !="ANY")
    &#123;
        $query .="AND events.area='$area'";
    &#125;
    if( ! empty($type) && $type !="ANY")
    &#123;
        $query .="AND events.type='$type'";
    &#125;
    $query .="ORDER BY events.edate";
    $result=mysql_query($query, $link);
    if( ! $result)
    &#123;
        die("getEvents fatal error: ".mysql_error());
    &#125;
    $ret=array();
    while($row=mysql_fetch_array($result))
    &#123;
        array_push($ret, $row);
    &#125;
    return $ret;
&#125;
if you need any more information i'll be glad to give it.

Posted: Sun Apr 18, 2004 2:01 am
by JAM
Before running the query, try echo $query as text-output. Might help you debug it. I spot one problem i think;

Code: Select all

if( ! empty($type) && $type !="ANY")
    &#123;
        $query .="AND events.type='$type'";
    &#125;
You need spaces before AND in those...

Posted: Sun Apr 18, 2004 4:39 am
by steve2004
hi

period after events.*

Posted: Sun Apr 18, 2004 1:24 pm
by James M.
thanks for the help, i found the problem, there was a space missing

Code: Select all

$query="SELECT clubs.name, events.*, areas.area as areaname, 
    types.type as typename"; // <--need space after  'as typename'
saw it after JAM was talking about spaces, thanks.

Posted: Mon Apr 19, 2004 9:42 am
by JAM
Glad it worked out. What I saw was that if you sendt both $club_id and $range to the function, you'd have the spaces issue. You are perhaps not using it like that, but I'd like to mention it as it wasn't obvious on how the function works.

Code: Select all

// <snip>
    if( ! empty($club_id) && $club_id !="ANY")
    &#123;
        $query .="AND events.eclub='$club_id'"; // bad
        $query .=" AND events.eclub='$club_id'"; // better
    &#125;
    if( ! empty($range))
    &#123;
        $query .="AND events.edate >='$range&#1111;0]' AND events.edate<='$range&#1111;1]'"; // bad
        $query .=" AND events.edate >='$range&#1111;0]' AND events.edate<='$range&#1111;1]'"; // better (etc...)
    &#125; 
// <snip>
Adding a space before each AND in the if-clauses makes that work and the space after 'typename' can be ignored.