我正在创建一个脚本,以电子邮件中的表格格式自动导出我在MySQL中创建的查询结果。 我已经工作到我收到 2 封电子邮件,一封电子邮件给我错误: SQLSTATE[42000]:语法错误或访问冲突:1064 您的 SQL 语法有错误; 检查与您的MySQL服务器版本相对应的手册,了解在第1行的"附近使用的正确语法[ 选择计数(*(作为count
, region
, MONTHNAME( date
( 作为 tempur_stores.STATS 的month
,其中 date
> DATE_ADD(日期(现在(((, 间隔 -1 周( 和 date
我确信我的语法是正确的,因为每当我在 SQL 本身中运行查询时,我都会得到预期的结果。
另一封电子邮件只有我在代码、计数、区域和月份中指定的标题
有什么建议我出错/错过了什么吗?
.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 = 'myemail@live.com';
try {
$result = $stmt->execute()->as_array();
} catch (Exception $e) {
mail('myemail@live.com', '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 <no-reply@me.com>'n".
"Mailed-by: T UK <no-reply@me.com>'n".
"Reply-To: T UK <no-reply@me.com>'n".
"Return-Path: T UK <no-reply@me.com>'n";
mail($sendTo, $subject, implode('', $toEmail), $headers);
// mail('myemail@live.com', $subject, implode('', $toEmail), $headers);
}
查询中缺少右括号:
$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