Geek asks and answers

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

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

  • Select all sent SMS by date without a time part, receiving an array with date / quantity pairs day after day;
  • Add “fake” rows for days without any entry in my table that fills all the “spaces” of dates with zeros.

An approximate result and the corresponding table (here simplified):

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 this or should I manually play with PHP arrays?

+5
source share
2 answers

You can use the expression UNION

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 for joining.

Creating a dataset for a query

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

, . . , .

+4

,

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');

, , - .

+1

More articles:


All Articles