i need a simple SQL query.

[kevinkhan]

Im looking for a query to update a published column to 0 if the value is equal to 4 and it is created more than two days ago. The published field is stored as a unix time stamp as an int data type..

 

Here is the query i have so far

 

UPDATE Classified set `published`= 0 WHERE `published` = 4 AND `created` > 2 days old

 

here is the structure of the table

 

CREATE TABLE IF NOT EXISTS `Classified` (

`id` int(11) NOT NULL auto_increment,

`created` int(11) NOT NULL,

`modified` int(11) NOT NULL,

`published` tinyint(1) NOT NULL default '0',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1498 ;

 

can anybody help me with this?

[RussellReal]

Why is create an integer, why not a date? with it as a date you will be able to use date functions..

 

namely:

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_datediff

[kevinkhan]

im not sure. the guy that built the website used the unit time stamp.

 

I would find it difficult to change it too a datetime data type now..

 

is there anyway i can write a query to do this with Unix time stamp?

[mikosiko]

play with the TIMESTAMP() , TIMESTAMPADD() , TIMESTAMPDIFF(), UNIX_TIMESTAMP() and FROM_UNIXTIME() functions

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestamp