PHP: Incorrect SQL syntax error

Question

Hi folks, 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

Answer 1

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

Answer 2

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 ";

Answer 3

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.

Answer 4

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