error in SQL syntax

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
James M.
Forum Contributor
Posts: 119
Joined: Wed Mar 31, 2004 6:41 pm
Location: Tallahassee

error in SQL syntax

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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...
steve2004
Forum Newbie
Posts: 14
Joined: Fri Apr 16, 2004 1:18 pm

Post by steve2004 »

hi

period after events.*
User avatar
James M.
Forum Contributor
Posts: 119
Joined: Wed Mar 31, 2004 6:41 pm
Location: Tallahassee

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply