How to fix this MySQL query
Currently I have a system where a PHP script logs some information (time and "sid") of a visit when the page is loaded.
I then use:
SELECT * FROM ( SELECT COUNT(*), time FROM visit WHERE sid = $sid GROUP BY time ORDER BY time DESC LIMIT 14 ) AS abc ORDER BY time ASC
to get the results from the last fortnight in reverse order.
The problem with this query is that on days with no visits I do not get the result:
0, [time], but instead nothing. What would I do to get a result of zero on those days? Is it even possible in MySQL or would I have to process it with PHP?
Any help greatly appreciated!
You have to use a second table containing all dates (or use a subquery like below). So extend the subquery for table
CURDATE()-13 for having last 14 days.
SELECT COUNT(*),t.time FROM ( SELECT CURDATE() AS time UNION SELECT CURDATE()-1 AS time UNION SELECT CURDATE()-2 AS time UNION SELECT CURDATE()-3 AS time UNION SELECT CURDATE()-4 AS time UNION [...] ) AS t LEFT JOIN visit AS v ON t.time=v.time AND v.sid=$sid ORDER BY t.time DESC
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]