Tuesday, 25 April 2017

Get records from last hour or last 20 items if there is none in the last hour

I have a chat system and I want to show messages sent in the last hour, but I also want to show the last 20 messages no matter how long ago they were sent.

Is there a way I can do this in.a SQL query?

CREATE TABLE IF NOT EXISTS `chat` (
  `id`        INT(11) UNSIGNED                                                                            NOT NULL AUTO_INCREMENT,
  `user_id`   INT(11) UNSIGNED                                                                            NOT NULL,
  `item_id`   INT(11) UNSIGNED                                                                            NOT NULL,
  `message`   TEXT                                                                                        NOT NULL,
  `recipient` INT(11)                                                                                     NOT NULL DEFAULT '0',
  `type`      ENUM ('message', 'announcement') NOT NULL DEFAULT 'message',
  `channel`   ENUM ('general', 'private')                                              NOT NULL DEFAULT 'general',
  `posted`    DATETIME                                                                                    NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `posted` (`posted`),
  KEY `type` (`type`),
  KEY `channel` (`channel`),
  KEY `recipient` (`recipient`)
)
  ENGINE = MyISAM
  DEFAULT CHARSET = `utf8`
  AUTO_INCREMENT = 2;



via Draven

No comments:

Post a Comment