我在MySQL中使用以下SQL查询。
"SELECT SUBSTRING(invoices.dateCreated, 1, 7) AS month,
account.name AS accountName,
account.id AS accountId,
invoices.invId AS invoiceId,
productType.title AS productTitle,
sum(invoiceItems.cost*invoiceItems.quantity) AS totalValue,
sum(invoiceItems.quantity) AS totalQuantity
FROM account LEFT JOIN invoices ON invoices.accountId = account.id
LEFT JOIN invoiceItems ON invoices.id = invoiceItems.invoiceId
LEFT JOIN productType ON invoiceItems.productTypeId = productType.id
WHERE invoices.statusId != 'S62FD452B1D4'
GROUP BY invoiceItems.productTypeId, invoices.invId
ORDER BY month DESC, accountName ASC, invoices.id ASC
LIMIT ".$start_limit.", ".$records_per_page.";"
这可以很好地处理掉限制查询的部分。然而，当我添加极限部分时，变量$start_limit&$records_per_page没有值，而如果我把变量周围的线弄乱，就会进入查询。当我将变量周围的引号更改为'.$start_limit.'时也是如此。然而，查询似乎无法与此一起工作。
对于我做错了什么，如果有任何建议或帮助，我们将不胜感激。
这两个变量的值肯定是100%被传递的。在这个例子中，它们只是，起始极限=0，结束极限（每页）=50。我查过了，50远低于限额。
问题是，当我看到SQL错误弹出时，它说：
SELECT SUBSTRING(invoices.dateCreated, 1, 7) AS month,
account.name AS accountName,
account.id AS accountId,
invoices.invId AS invoiceId,
productType.title AS productTitle,
sum(invoiceItems.cost*invoiceItems.quantity) AS totalValue,
sum(invoiceItems.quantity) AS totalQuantity
FROM account LEFT JOIN invoices ON invoices.accountId = account.id
LEFT JOIN invoiceItems ON invoices.id = invoiceItems.invoiceId
LEFT JOIN productType ON invoiceItems.productTypeId = productType.id
WHERE invoices.statusId != 'S62FD452B1D4'
GROUP BY invoiceItems.productTypeId, invoices.invId
ORDER BY month DESC, accountName ASC, invoices.id ASC
LIMIT , ;
如果我改变一下，看看变量是否存在，我们就会得到：
SELECT SUBSTRING(invoices.dateCreated, 1, 7) AS month,
account.name AS accountName,
account.id AS accountId,
invoices.invId AS invoiceId,
productType.title AS productTitle,
sum(invoiceItems.cost*invoiceItems.quantity) AS totalValue,
sum(invoiceItems.quantity) AS totalQuantity
FROM account LEFT JOIN invoices ON invoices.accountId = account.id
LEFT JOIN invoiceItems ON invoices.id = invoiceItems.invoiceId
LEFT JOIN productType ON invoiceItems.productTypeId = productType.id
WHERE invoices.statusId != 'S62FD452B1D4'
GROUP BY invoiceItems.productTypeId, invoices.invId
ORDER BY month DESC, accountName ASC, invoices.id ASC
LIMIT '.0.', '.50.' ;
这两个值都不是用户提供的。所以这不是什么大问题。我不确定为什么变量在这个1查询中表现异常，我已经做了一百个类似的1，做了同样的事情。唯一不同的是分组和排序的数量。这会有什么不同吗？
如果$start_limit和/或$records_per_page未填充，则会出现问题。但是，如果您在将它们放入查询之前将其默认为标准值（如果您愿意）：
// default them values to 0 and 50
$start_limit = empty($start_limit) ? 0 : $start_limit;
$records_per_page = empty($records_per_page) ? 50 : $records_per_page;
// may also want to check (empty(...) || $var < 0 || $var > $threshold) as well.
然后继续你的业务：
$sql = "SELECT " . /* ... */ " LIMIT " . $start_limit . "," . $records_per_page;
不过，我应该注意的是，如果其中一个（或两者）是用户提供的（或者用户有机会更改这些值），我会在将它们放入查询之前先对它们进行消毒。例如
$start_limit = (int) $_REQUEST['start_limit'];
if ($start_limit < 0) // can't be <0
$start_limit = 0;
$records_per_page = (int) $_REQUEST['records_per_page'];
if ($records_per_page < 10) // can't be <10
$records_per_page = 10;
else if ($records_per_page > 100) // can't be >100
$records_per_page = 100;
然后你要确保$start_limit不包含任何类似;SELECT password FROM admin_table;的威胁。（SQL注入）
不过你的问题有点模糊，所以如果我偏离轨道，请更新问题，我也会对我的答案进行更新
你能使用PDO扩展吗？
您的代码容易出现SQL注入。使用PDO或MySQLi扩展。
使用PDO扩展的示例：
<?php
$query = "SELECT SUBSTRING(invoices.dateCreated, 1, 7) AS month, account.name AS accountName, account.id AS accountId, invoices.invId AS invoiceId, productType.title AS productTitle, sum(invoiceItems.cost*invoiceItems.quantity) AS totalValue, sum(invoiceItems.quantity) AS totalQuantity
FROM account
LEFT JOIN invoices ON invoices.accountId = account.id
LEFT JOIN invoiceItems ON invoices.id = invoiceItems.invoiceId
LEFT JOIN productType ON invoiceItems.productTypeId = productType.id
WHERE invoices.statusId != 'S62FD452B1D4'
GROUP BY invoiceItems.productTypeId, invoices.invId
ORDER BY month DESC, accountName ASC, invoices.id ASC
LIMIT ?, ?;"
$stmt = $dbh->prepare($query);
$stmt->bindParam(1, $start_limit, PDO::PARAM_INT);
$stmt->bindParam(2, $records_per_page, PDO::PARAM_INT);
$stmt->execute();
// other codes here
?>
$start_limit和$records_per_page的值可能无效或超出范围。也许可以尝试将它们记录下来以确定。