CODEWITHSUNDEEP
phpmysqlsql
gremo
gremo

Reputation: 48919

Is there any way to generate "fake" rows in MySQL result set?

I need this for charting purposes. Basically i have to:

  1. Select all sent SMS by date without time part, getting an array with dates/counts pairs day-by-day;
  2. Add "fake" rows for days without any record in my table, that is filling all dates "gaps" with zeroes.

An example result and corresponding table (here simplified) would be:

array(
    '2012-05-26 00:00:00' => 1,
    '2012-05-27 00:00:00' => 0, // Fake added row
    '2012-05-28 00:00:00' => 2,
)

+----------------+----------------------+
| Table SMS | id | sent_at              |
+----------------+----------------------+
|           |  1 | 2012-05-26 21:58:41  |
+----------------+----------------------+
|           |  2 | 2012-05-28 22:19:21  |
+----------------+----------------------+
|           |  3 | 2012-05-28 02:19:21  |
+----------------+----------------------+

Is there any SQL command for doing this or should i do manually playing with PHP arrays?

Upvotes: 4

Views: 3538

Answers (2)

John Hartsock
John Hartsock

Reputation: 86882

You can use a UNION Statement

SELECT 
   sent_at,
   Count(*)
FROM (Select
        id,
        DATE(sent_at) as sent_at
      FROM TableName 
      Group by Date(sent_at)
      UNION ALL
      Select
        '0' as id,
        DATE('2012-05-27') as sent_at) derived_table
Group By sent_at

Edited

I suggested creating a special table to join against.

Creation of a datetable for querying

CREATE TABLE DateTable (
  DateValue DateTime,
  Year Int,
  Month Int,
  Day Int)

Now populate this table with all date values within the ranges you are querying. You can easily join to this table with any valid date. Allowing you to aggregate for Dates that do and do not exist.

Upvotes: 3

KCD
KCD

Reputation: 10291

Yes, I do this all the time

DROP PROCEDURE IF EXISTS `Example`;
DELIMITER $$

CREATE PROCEDURE `Example` (
    $StartDate DATE,
    $EndDate DATE
    )
BEGIN

    DECLARE $curDay DATE;
    SET $StartDate = IFNULL($StartDate,'2000-01-01');
    SET $curDay = $StartDate;


    DROP TEMPORARY TABLE IF EXISTS `Day`;
    CREATE TEMPORARY TABLE `Day`(
        `Date` DATE
    );

    DaysLoop:LOOP

        INSERT INTO
            `Day`(`Date`)
        SELECT 
            $curDay
        ;

        SET $curDay = $curDay + INTERVAL 1 DAY;

        IF 
            $curDay >= $EndDate OR $curDay >= NOW()
        THEN 
            LEAVE DaysLoop;
        END IF;

    END LOOP DaysLoop;

    SELECT
        D.Date,
        COUNT(S.id)
    FROM
        `Day` AS D
    LEFT JOIN
        `SMS` AS S
        ON D.Date = DATE(S.sent_at)
    GROUP BY
        D.Date
    ORDER BY
        D.Date
    ;

END $$

DELIMITER ;

CALL Example('2012-01-01','2012-05-01');

I use and recommend stored procedures for reports, just make sure you version control the create scripts and whatnot.

Upvotes: 1

Related Questions