delete rows created 1 hour ago

[OsaMasw]

hey there I have problem in running som function in php
I've created simple chat system , I want to delete every message create 1 hour ago or 30 minutes ago
I've used

mysql_query("DELETE FROM `chat` WHERE `cht_date` < DATE_SUB(NOW(), INTERVAL 1 MINUTE)");

my "cht_date" type in database is "TIME" .
but the messages didn't delted even once :(
whats wrong with my command ?

[pritaeas]

If you execute this query in phpMyAdmin, do you get an error?

I think it should be:

INTERVAL 1 MINUTES

[diafol]

I think MINUTE is right:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

[diafol]

The date_sub bit seems to work for me:

SELECT DATE_SUB(NOW(), INTERVAL 100 MINUTE)

Gave me the datetime of 100 minutes ago.

[pritaeas]

Indeed, read the table backwards ;)

[diafol]

Indeed, read the table backwards ;)

lol

[OsaMasw]

So what should I do ? I've changed my "cht_date" type in database to "datetime" and waited for 1 minute and nothing deleted

function get_chat() {
    del_chat();
    $chat   = array();
    $query = "SELECT * FROM `chat` ORDER BY cht_date DESC";
    $query = mysql_query($query);
    while ($row = mysql_fetch_assoc($query)) {
        $chat[] = $row;
    }
    return $chat;
}
function del_chat() {
mysql_query("DELETE FROM `chat` WHERE `cht_date` < DATE_SUB(NOW(), INTERVAL 1 MINUTE)");
mysql_query("OPTIMIZE TABLE `chat` ");
}

any idea ??

[OsaMasw]

I've tried the code in SQL in PHPmyadmin and the results was

Deleted rows: 0

then there is somthing wrong with my date format :(

[pritaeas]

Show your table structure.

[OsaMasw]

this from phpmyadmin table structure

CREATE TABLE `chat` (
  `cht_id` int(11) NOT NULL auto_increment,
  `cht_user` varchar(100) NOT NULL,
  `cht_msg` text NOT NULL,
  `cht_date` datetime NOT NULL,
  PRIMARY KEY  (`cht_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;

[pritaeas]

SELECT * FROM `chat` WHERE `cht_date` < DATE_SUB(NOW(), INTERVAL 1 MINUTE)

Does this return anything?

[OsaMasw]

MySQL returned an empty result set (i.e. zero rows). 

[pritaeas]

But you do have data? Can you show some? What MySQL version are you using?

[OsaMasw]

am using MySQL client version: 5.0.51a
on localhost appserve

[pritaeas]

What do you see when you run this:

SELECT DATE_SUB(NOW(), INTERVAL 1 MINUTE)

[OsaMasw]

I think I've began to understand what happening here
the result for pritaeas code is

2013-07-01 13:49:49

but I've noticed the date stored for messages is

2013-07-01 14:49:16

So I think the time difference is the problem

[pritaeas]

Difference between client and server? DST offset maybe? How do you insert the date into your table?

[OsaMasw]

$date=date("Y-m-d H:i:s");

[pritaeas]

You can use the mysql date like this:

INSERT INTO `chat` (`cht_date`) VALUES (NOW())

Then you'll always have the mysql date.

[OsaMasw]

Thanks pritaeas you are saver :D, Problem solved by using NOW() in Insert statment .