Page 1 of 1

date- How to change column's value without user interaction

Posted: Mon Mar 19, 2007 4:09 pm
by crazytopu
How can you change this "expire" to change from 0 to 1 when the required date is already gone? The only way I could think of is to check the database, get the request whose required_by_date > todays date where todays date is a php variable that stores today's date. But it means I have to check that every time the user visit the page. Isn't there a better way to set expire to 1 when the date is gone?



I am using this select statement to show the requests that have been approved and have not expired yet.

Code: Select all


$query= 'SELECT id,quote_no,username,title,description,post_date,required_by_date FROM request Where approve= 1 && expire=0 ORDER BY quote_no DESC';


table :

Code: Select all


CREATE TABLE `request` (
  `id` int(11) NOT NULL auto_increment,
  `quote_no` varchar(40) NOT NULL default '',
  `username` varchar(50) default NULL,
  `city` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `post_date` date NOT NULL default '0000-00-00',
  `required_by_date` date NOT NULL default '0000-00-00',
  `approve` int(11) NOT NULL default '0',
  `expire` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ;


Posted: Mon Mar 19, 2007 4:12 pm
by feyd
Why even have expire then?

Posted: Mon Mar 19, 2007 4:18 pm
by crazytopu
Then how it would work? Approve is only so the admin can read first before they appear on the site.

Expire is for two reasons -

1. The buyer can see the post until the required day is past. As soon as the required day is passed there is no point to display it is there?

2. Buyer can reqest admin to take off their post when they get a suitable supplier, i.e they dont wnt to get any more response. So, expire is set to 1 in that case.

I cant see how can I do this using just approve.

Posted: Mon Mar 19, 2007 4:44 pm
by feyd
So why not store the date when the record expired (if unnatural)?

Code: Select all

SELECT * FROM tableName WHERE required_by_date > NOW() AND expire IS NULL
for example.