PHP：不正确的 SQL 语法错误
我有一个 PHP 类，它根据从表单输入的值创建 SQL 查询。我得到了
关键字“WHERE”附近的语法不正确。 ) )
这是我的代码。问题发生在每个 WHERE 子句周围（顺便说一句，已经在处理 SQL 注入）。
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 . ")";
}
我对这些语法错误感到很糟糕:(
有人可以帮我吗？
Jonesy
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您的
WHERE子句需要位于GROUP BY子句之前。Your
WHEREclause needs to come before theGROUP BYclause.您的
GROUP BY子句位于您的WHERE子句之前，这是一个问题。您还必须将HAVING子句移至 GROUP BY 子句之后。文档中将提供更多信息。
MySQL： http://dev.mysql.com/doc/refman/5.0 /en/select.html
PostgreSQL：http://www .postgresql.org/docs/current/static/sql-select.html
编辑：
此外，您应该将
$to = ''更改为$to == ''和 $if子句中的from = ''到$from == ''。Your
GROUP BYclause is coming before yourWHEREclause which is a problem. You'll also have to move yourHAVINGclause 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 yourifclauses.不能在 GROUP BY 之后放置 WHERE。您需要附加 WHERE 子句，然后在所有 WHERE 子句之后，将 GROUP BY 放在查询中。例如
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.
我很确定下面的内容
一定是这样的：
这是逻辑问题而不是 SQL，但仍然会返回奇怪的结果。
更新： KM评论提醒我一位同事建议将值写在左侧，将变量写在右侧作为此问题的解决方案。代码如下：
I am pretty sure that the following
must look like:
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: