How to fix this MySQL query


Problem :

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!



Solution :

You have to use a second table containing all dates (or use a subquery like below). So extend the subquery for table t till 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

Mysql Tutorials

Mysql Howto..

MySQL query already GROUPed and ORDERed : how to ORDER inside the GROUPs?

How to find slower MySQL queries from many small queries

How to log query response time in MySQL?

How do I make Rich Text Format or PDF files using data from a MySQL table? [closed]

how to use dayofmonth function using node mysql

How to get rows with array of ids in MySQL

How to sync mysql table fields with prefix

How to fetch and output properly data from table, inserted with mysqli_real_escape_string

how to create a set of users with the same prefix in mysql? Using wildcard?

How to use Triggers for Logging History of database changes in MySQL?

How to delete table rows in MySQL and PHP?

MySQL full text issue - How to match two columns in the same query?

How to use MySQL DB data inside JavaScript function?

How to select the lastest record from a group in MySQL table using SQl syntax

How to clean up mysql out in python?

How to display normalized MySQL data in a flat HTML table with PHP?

How to do a “horizontal UNION” in MySQL? (concating tables)

MySQL how to group by and select certain status from multiple rows?

How to select multiple values from single column with multiple tables in MySQL?

How to test mysqli's real_escape_string()?

How to synchronize flow of action in java web application using struts?

How to link sent and received messages in mysql?

How to change mysql_connect into PDO. Updating form

how to SELECT and UPDATE query involving two table

how to make a gallery wherein the images used are from mysql database?

How to find users aged between 25 and 30 when the birthdates are stored as type “date” in MySQL?

How to use MySQL view in django?

Getting labels to show

How to enable MySQLi extension in php 7?

How to select distinct rows from SQL Server table