一个查询中的MySQL SELECT，LEFT JOIN和COUNT()返回错误 [英] MySQL SELECT, LEFT JOIN and COUNT() in one query returns error
问题描述
我陷入了相当普通的查询之中！
简而言之，我已经创建了一个搜索(过滤器)面板，现在我在其中添加了分页功能.
返回当前查询中的行数时遇到问题，主要取决于动态更改$detailed_search_query变量.
我需要做以下工作，并在其中适当添加COUNT()，因此新行的总数应包含unique_id的总数.
当前SQL:
$sql = $db->prepare( "
SELECT
individuals.individual_id,
individuals.unique_id,
individuals.fullname,
individuals.day_of_birth,
TIMESTAMPDIFF(YEAR,individuals.day_of_birth,CURDATE()) AS age,
individuals.gender,
individuals.record_timestamp,
individuals.active,
individuals.deleted,
individuals_dynamics.weight,
individuals_dynamics.degree,
individuals_dynamics.trainer_name
FROM
individuals as individuals
LEFT JOIN
individuals_dynamics AS individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id
WHERE
$detailed_search_query $display recognized = 'yes'
GROUP BY
individuals.record_timestamp
ORDER BY $by $how
LIMIT " . $limit);
如果我向其添加COUNT()，则我会看到PDO错误，提示Fatal error: Call to a member function execute() on a non-object.
这是我的新查询(刚开始，其余部分相同)的样子，返回上面的错误:
$sql = $db->prepare( "
SELECT
COUNT(individuals.unique_id),
individuals.individual_id,
individuals.unique_id,
individuals.fullname,
individuals.day_of_birth,
我在这里想念什么?
我如何使用COUNT()的示例在普通的预查询中起作用:
$sql = $db->prepare("SELECT count(unique_id) FROM individuals");
$sql->execute();
$total = $sql->fetchColumn();
$pagination = new Pagination($paginate_number);
$limit = $pagination->getLimit($total);
是的，对了，当我添加别名时，会返回相同的错误，例如:
$sql = $db->prepare( "
SELECT
COUNT(individuals.unique_id) as total,
individuals.individual_id,
最后一次编辑对我来说是不好的，如果您添加别名(如总数)，则查询有效，但它仅计数当前行并返回1，但我需要总行数，例如:
Array
(
[0] => Array
(
[total] => 1
[0] => 1
[individual_id] => 51
[1] => 51
[unique_id] => f598edae
[2] => f598edae
当替换PHP变量时，我在WHERE子句中有以下内容:
WHERE
individuals.fullname LIKE '%adam%' AND individuals_dynamics.degree BETWEEN '1' AND '3' AND EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),individuals.day_of_birth))))+0 BETWEEN '7' AND '10' AND individuals_dynamics.weight BETWEEN '20' AND '40' AND individuals_dynamics.degree BETWEEN '7' AND '10' AND deleted != 'yes' AND active != 'no' AND recognized = 'yes'
GROUP BY
individuals.record_timestamp
期望的结果将是在最终数组中具有键合计，该键合计表示在当前查询中基于动态PHP变量(如$detailed_search_query和$display)提取的结果总数:
现在我总共有1个.应该是75:
Array
(
[0] => Array
(
[total] => 1
[0] => 1
[individual_id] => 71
[1] => 71
[unique_id] => f598e2ae
[2] => f598e2ae
[fullname] => Name2 Name2 Name2
)
[1] => Array
(
[total] => 1
[0] => 1
[individual_id] => 65
[1] => 65
[unique_id] => b76497ca
[2] => b76497ca
)
您收到的错误意味着PDO无法准备查询，原因是SQL查询中存在错误，而数据库服务器可以不执行它.因此，为了更好地理解这个问题，您应该发布尝试直接在mysql客户端上执行查询的错误.
要获得所需的结果集，可以插入一个子查询，该子查询对与外部查询具有相同individual_id记录的记录进行计数.
在查询的第一部分之后:
SELECT
(SELECT COUNT(unique_id) FROM individuals i2 WHERE i2.individual_id = individuals. individual_id) AS total,
individuals.individual_id,
individuals.unique_id,
individuals.fullname,
individuals.day_of_birth,
请记住，要从子查询中正确引用外部查询的列，即使您从两个查询中的同一表(外部和子表)中进行选择，也应使用两个不同的别名.
I got stuck with fairly plain query!
Briefly, I have created a search (filter) panel and now I'm adding pagination to it.
I'm having a problem in returning the number of rows that is in current query, mostly dependent on dynamically changing $detailed_search_query variable.
I need to make the following work, with adding COUNT() to it properly, so the new row total would contain the overall number of unique_id's.
Current SQL:
$sql = $db->prepare( "
SELECT
individuals.individual_id,
individuals.unique_id,
individuals.fullname,
individuals.day_of_birth,
TIMESTAMPDIFF(YEAR,individuals.day_of_birth,CURDATE()) AS age,
individuals.gender,
individuals.record_timestamp,
individuals.active,
individuals.deleted,
individuals_dynamics.weight,
individuals_dynamics.degree,
individuals_dynamics.trainer_name
FROM
individuals as individuals
LEFT JOIN
individuals_dynamics AS individuals_dynamics ON individuals.unique_id = individuals_dynamics.individual_id
WHERE
$detailed_search_query $display recognized = 'yes'
GROUP BY
individuals.record_timestamp
ORDER BY $by $how
LIMIT " . $limit);
If I add COUNT() to it, I have PDO error saying Fatal error: Call to a member function execute() on a non-object.
This is how my new query ( just beginning, rest is the same ) looks like, that returns error above:
$sql = $db->prepare( "
SELECT
COUNT(individuals.unique_id),
individuals.individual_id,
individuals.unique_id,
individuals.fullname,
individuals.day_of_birth,
What am I missing here?
EDIT 1:
The example of how I use COUNT() results in plain pre-query that works:
$sql = $db->prepare("SELECT count(unique_id) FROM individuals");
$sql->execute();
$total = $sql->fetchColumn();
$pagination = new Pagination($paginate_number);
$limit = $pagination->getLimit($total);
EDIT 2:
Yes, right, when I add an alias same error returns, example:
$sql = $db->prepare( "
SELECT
COUNT(individuals.unique_id) as total,
individuals.individual_id,
EDIT 3:
It's my bad about the last EDIT, if you add alias, like as total, then query works BUT it only COUNTS current row and returns 1 but I need total row count, example:
Array
(
[0] => Array
(
[total] => 1
[0] => 1
[individual_id] => 51
[1] => 51
[unique_id] => f598edae
[2] => f598edae
EDIT 4:
When the PHP variables are replaced then I have something like this in WHERE clause:
WHERE
individuals.fullname LIKE '%adam%' AND individuals_dynamics.degree BETWEEN '1' AND '3' AND EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),individuals.day_of_birth))))+0 BETWEEN '7' AND '10' AND individuals_dynamics.weight BETWEEN '20' AND '40' AND individuals_dynamics.degree BETWEEN '7' AND '10' AND deleted != 'yes' AND active != 'no' AND recognized = 'yes'
GROUP BY
individuals.record_timestamp
EDIT 5:
The desired result would be to have in a final array the key total, that would represent the total amount of results that were extracted in the current query based on dynamic PHP variables, as $detailed_search_query and $display:
Now I have always 1 in the total. When it should be 75:
Array
(
[0] => Array
(
[total] => 1
[0] => 1
[individual_id] => 71
[1] => 71
[unique_id] => f598e2ae
[2] => f598e2ae
[fullname] => Name2 Name2 Name2
)
[1] => Array
(
[total] => 1
[0] => 1
[individual_id] => 65
[1] => 65
[unique_id] => b76497ca
[2] => b76497ca
)
The error that you get means that PDO can't prepare the query, and the reason is that there is an error in your SQL query and the database server can't execute it ... So to let understand better the question you should post the error that you get trying to executing the query on the mysql client directly .
To achieve the result set you need, you can insert a subquery that count the records that have the same individual_id as the outer query.
Following the first part of the query :
SELECT
(SELECT COUNT(unique_id) FROM individuals i2 WHERE i2.individual_id = individuals. individual_id) AS total,
individuals.individual_id,
individuals.unique_id,
individuals.fullname,
individuals.day_of_birth,
Bear in mind that to reference a column of the outer query correctly from the subquery you should use two different alias name, even if you are selecting from the same table in both query (outer and sub).
这篇关于一个查询中的MySQL SELECT，LEFT JOIN和COUNT()返回错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
