How to count records in MySQL and merge results in PHP

Question

I have a table which stores clients like this:

id    name
--    ----
1     John
2     Jane
...

I also have another table which stores links created by clients:

id    client_id    link    created
--    ---------    ----    -----------
1     1            ...     2015-02-01
2     1            ...     2015-02-26
3     1            ...     2015-03-01
4     2            ...     2015-03-01
5     2            ...     2015-03-02
6     2            ...     2015-03-02

I need to find how many links a client has created today, this month and during all the time. I also need their name in the result, so I'll be able to craete a HTML table to display the statistics. I thought I can code as less as possible like this:

$today = $this->db->query("SELECT COUNT(*) as today, c.id as client_id, c.name FROM `links` l JOIN `clients` c ON l.client_id = c.id WHERE DATE(l.created) = CURDATE() GROUP BY c.id");

$this_month = $this->db->query("SELECT COUNT(*) as this_month, c.id as client_id, c.name FROM `links` l JOIN `clients` c ON l.client_id = c.id WHERE YEAR(l.created) = YEAR(NOW()) AND MONTH(l.created) = MONTH(NOW()) GROUP BY c.id");

$yet = $this->db->query("SELECT COUNT(*) as yet, c.id as client_id, c.name FROM `links` l JOIN `clients` c ON l.client_id = c.id WHERE GROUP BY c.id");

And then merge them in PHP as I asked HERE before, like this:

$result = array_replace_recursive($today, $this_month, $yet);

So I'll be able to loop into the result and print my HTML table.

But there are logical problems here. Everything works fine, but the result in a month is a wrong number, forexample the whole created links of one person is 1 but it shows 4 in the monthly counter! I also tried to use RIGHT JOIN in SQL query to get all clients, so array_replace_recursive in PHP could work fine as I think it doesn't work properly at the moment, but no success and got wrong results again.

Can anyone show me a way to make the job done?

Just for future reference you should add a SQLFiddle whenever possible for database stuff. Really saves people time and gets you an answer faster. — Jhecht, Mar 02 '15 at 07:07

score 0 · Accepted Answer · answered Mar 02 '15 at 06:51

This query should do it for today

$query_today="
SELECT name, id AS user_id, (
 SELECT COUNT( * ) 
 FROM  links 
 WHERE client_id = user_id AND created = '2015-03-02'
) AS alllinks
FROM clients"

adjust the WHERE clause in the subquery for months and all

$query_month="
SELECT name, id AS user_id, (
 SELECT COUNT( * ) 
 FROM  links 
 WHERE client_id = user_id AND created like '2015-03%'
) AS alllinks
FROM clients"

$query_all="
SELECT name, id AS user_id, (
 SELECT COUNT( * ) 
 FROM  links 
 WHERE client_id = user_id
) AS alllinks
FROM clients"

How to count records in MySQL and merge results in PHP

1 Answers1