PHP: Incorrect SQL syntax error

Question

I have a PHP class that creates a SQL query based on values entered from a form. I'm getting

Incorrect syntax near the keyword 'WHERE'. ) )

Here is my code. The problem is occurring around each of the WHERE clauses, (already dealing with SQL injections btw).

    if($from != ''){
        $from = date('Y-m-d H:i:s',strtotime($from));
    }

    if($to != ''){
        $to   = date('Y-m-d H:i:s',strtotime($to));
    }



    $tsql = "SELECT COUNT(tblBackupArchive.StatusID) AS total, tblBackupArchive.StatusID ".
            "FROM tblBackupArchive INNER JOIN ".
            "tblBackup ON tblBackupArchive.BackupID = tblBackup.BackupID ".
            "GROUP BY tblBackupArchive.StatusID, tblBackup.ClientID ";

    if($from != '' && $to !=''){
        $tsql .=  "WHERE (tblBackupArchive.BackupDate BETWEEN '" . $from ."' AND '" . $to . "') ";
    }

    if($from != '' && $to=''){
        $tsql .= " WHERE (tblBackupArchive.BackupDate > '" . $from ."') ";
    }

    if($to != '' && $from = ''){
        $tsql .= " WHERE (tblBackupArchive.BackupDate < '" . $to ."') ";
    }

    if(isset($bmsid)){
        $tsql .= "HAVING (tblBackup.ClientID = " . $bmsid . ")";
    }

I'm terrible with these syntax errors :(

Can someone help me out?

Jonesy

It would be more helpful if you could provide the actual query that is generated and is causing the problem. — Coin_op
– Coin_op, Commented Oct 26, 2010 at 15:00

D'Arcy Rittich · Accepted Answer · 2010-10-26 14:58:51Z

5

Your WHERE clause needs to come before the GROUP BY clause.

answered Oct 26, 2010 at 14:58

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

thetaiko · Accepted Answer · 2010-10-26 15:02:28Z

4

Your GROUP BY clause is coming before your WHERE clause which is a problem. You'll also have to move your HAVING clause to appear after your GROUP BY clause.

More information will be available in the documentation.

MySQL: http://dev.mysql.com/doc/refman/5.0/en/select.html

PostgreSQL: http://www.postgresql.org/docs/current/static/sql-select.html

EDIT:

In addition you should should change $to = '' to $to == '' and $from = '' to $from == '' in your if clauses.

answered Oct 26, 2010 at 15:02

thetaiko

7,8342 gold badges35 silver badges49 bronze badges

Comments

George Johnston · Accepted Answer · 2010-10-26 14:59:25Z

You can't place a WHERE after a GROUP BY. You'll need to append your WHERE clauses, and then after all of your WHERE clauses, put the GROUP BY on the query. e.g.

$tsql = "SELECT COUNT(tblBackupArchive.StatusID) AS total, tblBackupArchive.StatusID ".
            "FROM tblBackupArchive INNER JOIN ".
            "tblBackup ON tblBackupArchive.BackupID = tblBackup.BackupID ";

    if($from != '' && $to !=''){
        $tsql .=  "WHERE (tblBackupArchive.BackupDate BETWEEN '" . $from ."' AND '" . $to . "') ";
    }

    if($from != '' && $to=''){
        $tsql .= " WHERE (tblBackupArchive.BackupDate > '" . $from ."') ";
    }

    if($to != '' && $from = ''){
        $tsql .= " WHERE (tblBackupArchive.BackupDate < '" . $to ."') ";
    }

    if(isset($bmsid)){
        $tsql .= "HAVING (tblBackup.ClientID = " . $bmsid . ")";
    }

    $tsql .= " GROUP BY tblBackupArchive.StatusID, tblBackup.ClientID ";

Ilian Iliev · Accepted Answer · 2010-10-27 08:04:57Z

3

I am pretty sure that the following

$to=''

must look like:

$to==''

This is logic problem not SQL but still will return strange results.

UPDATE: KM comment remind me for a colleague that proposed to write the value on the left side and the variable on the right as solutions of this problem. The code would look like:

$x = '';
if(5 = $x){} // this throw an error
if(5 == $x){} // this returns false

edited Oct 27, 2010 at 8:04

answered Oct 26, 2010 at 15:03

Ilian Iliev

3,2364 gold badges29 silver badges51 bronze badges

2 Comments

KM. Over a year ago

if($from != '' && $to=''){ should be if($from != '' && $to==''){ and if($to != '' && $from = ''){ should be if($to != '' && $from== ''){ in php, "=" means assign a value to a variable, while "==" means does equal. Even though you the code says if (.... && $to='') $to is being assigned the value of empty string and not being checked for having a value of empty string.

Your Common Sense Over a year ago

It spoils code readability. I know of that practice for ages but never used it myself.

Collectives™ on Stack Overflow

PHP: Incorrect SQL syntax error

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related