SQLSTATE[42000]：语法错误或访问冲突：1064 PHP/MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19051716/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLSTATE[42000]: Syntax error or access violation: 1064 PHP/MySQL
提问by Karina McGourty
I am creating a script to automatically export the results of a query i have created in MySQL, in a table format in an email. I have it working up to the point where i receive 2 emails, one email gives me the error: SQLSTATE[42000]: Syntax error or access violation: 1064 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 '' at line 1 [ SELECT COUNT(*) as count,region, MONTHNAME(date) asmonthFROM tempur_stores.stats WHERE date> DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) AND date< DATE(NOW()) GROUP BY region, MONTH(date]I am sure my syntax is correct as i get the expected results whenever i run my query in SQL itself.
我正在创建一个脚本，以在电子邮件中以表格格式自动导出我在 MySQL 中创建的查询结果。我已经收到 2 封电子邮件，其中一封电子邮件给了我错误：SQLSTATE[42000]：语法错误或访问冲突：1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行 [ SELECT COUNT(*) as count, region, MONTHNAME( date) as monthFROM tempur_stores.stats WHERE date> DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) AND date< DATE(NOW()) GROUP BY region, MONTH( date]我确信我的语法是正确的，因为每当我在 SQL 中运行我的查询时我都会得到预期的结果。
The other email just has the headings i have specified in my code, count, regionand month
另一封电子邮件只有我在代码中指定的标题、计数、地区和月份
Any suggestions where i am going wrong/missing something?
有什么建议我哪里出错了/遗漏了什么？
PHP
PHP
public function action_third_party_searches()
{
$stmt = DB::query(Database::SELECT, 'SELECT COUNT(*) as `count`,`region`, MONTHNAME(`date`) as`month` FROM tempur_stores.stats WHERE `date` > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) AND `date` < DATE(NOW()) GROUP BY `region`, MONTH(`date`');
$result = $stmt;
$sendTo = '[email protected]';
try {
$result = $stmt->execute()->as_array();
} catch (Exception $e) {
mail('[email protected]', 'Tempur 3rd Party Store Locator Searches', $e->getMessage());
}
$subject = 'Third Party Store Locator Searches - '.date("Y-m-d", strtotime("-1 week")).' - '.date("Y-m-d");
if (count($result) > 0) {
$toEmail = array('
<html>
<head>
<title>Third Party Store Locator Searches</title>
</head>
<body>
<table>
<tr>
<td>Count</td>
<td>Region</td>
<td>Month</td>
</tr>
');
foreach ($result as $row) {
$toEmail[] = '
<tr>
<td>'.$row['count'].'</td>
<td>'.$row['region'].'</td>
<td>'.$row['month'].'</td>
</tr>
';
}
$toEmail[] = '
</table>
</body>
</html>';
} else {
$toEmail = array('No searches were taken last week!');
}
$headers = "Content-type: text/html; charset=utf-8\n".
"X-Mailer: PHP/".phpversion()."\n".
"MIME-Version: 1.0\n".
"From: T UK <[email protected]>\n".
"Mailed-by: T UK <[email protected]>\n".
"Reply-To: T UK <[email protected]>\n".
"Return-Path: T UK <[email protected]>\n";
mail($sendTo, $subject, implode('', $toEmail), $headers);
// mail('[email protected]', $subject, implode('', $toEmail), $headers);
}
回答by silkfire
You're missing a closing parenthesis in your query:
您在查询中缺少右括号：
$stmt = DB::query(Database::SELECT, 'SELECT COUNT(*) as `count`,`region`, MONTHNAME(`date`) as`month`
FROM tempur_stores.stats
WHERE `date` > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK)
AND `date` < DATE(NOW())
GROUP BY `region`, MONTH(`date`');
---------- ^ right there
