0

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 ?

3
Contributors
19
Replies
29
Views
4 Years
Discussion Span
Last Post by OsaMasw
Featured Replies
  • 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 Read More

  • 1

    You can use the mysql date like this: INSERT INTO `chat` (`cht_date`) VALUES (NOW()) Then you'll always have the mysql date. Read More

0

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

I think it should be:

INTERVAL 1 MINUTES

Edited by pritaeas

0

The date_sub bit seems to work for me:

SELECT DATE_SUB(NOW(), INTERVAL 100 MINUTE)

Gave me the datetime of 100 minutes ago.

0

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

0

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

0

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 ;
0

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

Edited by pritaeas

0

am using MySQL client version: 5.0.51a
on localhost appserve

Edited by OsaMasw

1

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

Edited by OsaMasw

1

You can use the mysql date like this:

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

Then you'll always have the mysql date.

Votes + Comments
you are awesome
0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.