mysql联表查询count错误,一个查询中的MySQL SELECT，LEFT JOIN和COUNT（）返回错误

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).