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 ?

Recommended Answers

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

I think it should be:

INTERVAL 1 MINUTES
Jump to Post

The date_sub bit seems to work for me:

SELECT DATE_SUB(NOW(), INTERVAL 100 MINUTE)

Gave me the datetime of 100 minutes ago.

Jump to Post

Indeed, read the table backwards ;)

Jump to Post

You can use the mysql date like this:

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

Then you'll always have the mysql date.

Jump to Post

All 19 Replies

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

I think it should be:

INTERVAL 1 MINUTES
Member Avatar

The date_sub bit seems to work for me:

SELECT DATE_SUB(NOW(), INTERVAL 100 MINUTE)

Gave me the datetime of 100 minutes ago.

Indeed, read the table backwards ;)

Member Avatar

Indeed, read the table backwards ;)

lol

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 ??

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 :(

Show your table structure.

be6e17e2d90aed63839219a5c7994756

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 ;
SELECT * FROM `chat` WHERE `cht_date` < DATE_SUB(NOW(), INTERVAL 1 MINUTE)

Does this return anything?

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

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

am using MySQL client version: 5.0.51a
on localhost appserve

What do you see when you run this:

SELECT DATE_SUB(NOW(), INTERVAL 1 MINUTE)

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

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

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

You can use the mysql date like this:

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

Then you'll always have the mysql date.

commented: you are awesome +2

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.