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

Recommended Answers

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

Jump to Post

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'

Jump to Post

what current date are u talking about?

current date is auto? its like now()

Jump to Post

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 …

Jump to Post

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

Jump to Post

All 20 Replies

can it be an if statement??

Member Avatar

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)

Member Avatar

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'

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

Member Avatar

what current date are u talking about?

current date is auto? its like now()

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?

Member Avatar

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

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

Member Avatar

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 }

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?

Member Avatar

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'

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

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

Member Avatar

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?

error appear on if statement

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

Member Avatar

i missed out a ;

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

its not letting you delete any date

Member Avatar

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

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'; }

Member Avatar

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

Be a part of the DaniWeb community

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