if statement with dates

[aran87]

hello, im using a delete function but some how i want to use an if statement; for example if the current server date matches the date from a row from a database table and if the date is 2days from the current date it can't be delete.

Delete from House where rooms='10';

[aran87]

can it be an if statement??

i prefer to use php rather than hard coding in mysql as date var can be set and change easier

## php $del_date = date('Y-m-d', strtotime(' + 2 days')); Delete from House where rooms='10' and roomdate > '$del_date'

## mysql
Delete from House where rooms='10' and roomdate < DATE_SUB(CURDATE(), INTERVAL 2 DAY)

php should be -2 days
## php $del_date = date('Y-m-d', strtotime(' -2 days')); Delete from House where rooms='10' and roomdate < '$del_date'

[aran87]

hello but how do i get current date? to be implemented into that code??

what current date are u talking about?

current date is auto? its like now()

[aran87]

hello im on about current date so how would i implement all that code how would it be?

$del_date = date('Y-m-d', strtotime(' -2 days'));
Delete from House where rooms='10' and roomdate < '$del_date'

nothing needs to go in sql right?

nothing needs to go into sql just as long as you have a date to del from like roomdate. $del_date = date('Y-m-d', strtotime(' -2 days')); // this makes the date 2 days ago eg. 2008-04-25

Delete from House where rooms='10' and roomdate < '$del_date'
// find rooms with 10 and all room dates less than $del_date

run query
Delete from House where rooms='10' and roomdate < '2008-04-25'
house1 - 10 - 2008-04-26
house2 - 10 - 2008-04-11 [ deleted ]
house3 - 5 - 2007-04-22
house4 - 7 - 2008-04-25
house5 - 10 - 2008-04-24 [ deleted ]
house6 - 10 - 2008-04-25

[aran87]

how can i add some sort of message saying you can not delete this record and a message saying deleted?? also i dont want the user to be able to input a date they only input an id and click submit and the code you have given requires a date to be entered by a user or does it?? cheers

1.
$del_date = date('Y-m-d', strtotime(' -2 days')); // this makes the date 2 days ago
eg. 2008-04-25

you set the date. it is not set by the user.

run query
Delete from House where rooms='10' and roomdate < '2008-04-25'
house1 - 10 - 2008-04-26
house2 - 10 - 2008-04-11 [ deleted ]
house3 - 5 - 2007-04-22
house4 - 7 - 2008-04-25
house5 - 10 - 2008-04-24 [ deleted ]
house6 - 10 - 2008-04-25

this was an example of wat would happen. the date at end is an example. the date would come from quote 1 - $del_date

---------------

$del_date = date('Y-m-d', strtotime(' -2 days'));

$sql = "DELETE FROM House where id='".mysql_real_escape_string($id)."' and roomdate < '$del_date'";
$query = mysql_query($sql) ; 
if(mysql_affected_rows()>0){ deleted }else{ not deleted }

[aran87]

ok i get the idea but im confused where your getting the current date from like todays date and is it comparing against the date that needs to be deleted?

ok i get the idea but im confused where your getting the current date from like todays date

date('Y-m-d'); // this gets current todays date $del_date = date('Y-m-d', strtotime(' -2 days')); // this gets 2 days ago

is it comparing against the date that needs to be deleted?

i don't know wat the date field it needs to be compared against. i put roomdate as a example. it should be replaced with the date you want to compare delete date to.

Delete from House where rooms='10' and wat_ever_your_date_compare > '$del_date'

[aran87]

hello my date stored in sql is stored as 24/3/2008 will that make any difference

[aran87]

also error on this line if(mysql_affected_rows()>0){ deleted }else{ not deleted }

my spidy senses are telling me this is gooing to be long.

if(mysql_affected_rows()>0){ deleted }else{ not deleted } the delete and not delete is where u put your error message.

hello my date stored in sql is stored as 24/3/2008 will that make any difference

ok, do you have any more suprises?

----------------------------------------------------------------------

$del_date = date('Y/m/d', strtotime(' -2 days')); // cureent date is set here minus 2 days


$sql = "DELETE FROM House where room='10' and roomdate < '$del_date'";
$query = mysql_query($sql) ; 

if(mysql_affected_rows()>0){ echo 'deleted'; }else{ echo 'you can not delete this record'  }

anyyhing you don't understand look up fuction here http://uk3.php.net/manual/en/function.date.php it will be better explained
go here to learn basics http://www.w3schools.com/php/default.asp

with basic knowledge you will understand better. you don't need to learn anything fancy.

how is user going to enter data in db post or get? http://www.w3schools.com/php/php_get.asp

wat is the field for id named? is the room needed? wat is the date field named?

[aran87]

error appear on if statement

Parse error: syntax error, unexpected '}', expecting ',' or ';'

i missed out a ;

if(mysql_affected_rows()>0){ echo 'deleted'; }else{ echo 'you can not delete this record';  }

[aran87]

its not letting you delete any date

wat is the code you are using?
are there any dates matching the query to delete?

[aran87]

Hello yea there is dates for example 2/5/2008, but dont delete or say can not be deleted jus says you can not this record thanks

$del_date = date('j/n/Y', strtotime(' -2 days'));

$query1 = "DELETE FROM hotel WHERE id = '$value1' AND date < '$del_date'";
mysql_query($query1);
if(mysql_affected_rows()>0){ echo 'deleted'; }else{ echo 'you can not delete this record'; }

ok you want to delete 30/4/2008 + 2 days = 2/5/2008.

+ will do 30/4/2008 + 1 days = 1/5/2008
- will do 30/4/2008 - 1 days = 29/4/2008

$del_date = date('j/n/Y', strtotime(' +1 days'));

< will delete dates before $del_date
> will delete dates After $del_date

$query1 = "DELETE FROM hotel WHERE id = '$value1' AND date > '$del_date'";
mysql_query($query1);
if(mysql_affected_rows()>0){ echo 'deleted'; }else{ echo 'you can not delete this record'; }

ok, i have set del_date date to 1 day. so wat will happen with current code

id=5 date=1/5/2008

house1 - 5 - 1/5/2008
house2 - 5 - 4/5/2008 [ deleted ]
house3 - 1 - 1/5/2008
house4 - 5 - 24/4/2008
house5 - 5 - 2/5/2008 [ deleted ]
house6 - 1 - 10/5/2008