I have a column in my database with an inserton inside of my users database and would like to delete a user after 5 years of a inserton,

I want to do this with php how can i make this if attribute work?

The inserton is for example 2022-03-18 13:18:10. Instead of the database delete string i just echo'd delete for a better view of what I want to . It's just the if attribute that I can not make up to delete the user after 5 years of the inserton. The code I have right now as an example:

if($user['inserton'] > 5 years){
 "echo delete"; }

CodePudding user response：

If you have a column in your database, which indicates the user's insertion date and time, the best way would be to remove all the users from there:

DELETE FROM User WHERE 'createdAt' < NOW() - INTERVAL 5 Year;

However if you want to delete the user by it's id, then you should check the time inside the PHP:

if((new DateTime($user['inserton']))->modify(" 5 year") < new DateTime()) {
  $query = "DELETE FROM User WHERE id = ?;"; 
  $con = new mysqli($host, $username, $pass, $database); //database connection

  $stmt = $con->prepare($query);
  $stmt->bind_param('i', $userId);
  $stmt->execute();
  $stmt->close();
}

Note: the first way, removes all the users that are older than 5 years in the database, however the second way only removes the user that you've retrieved from the database.

CodePudding user response：

this is code

<?php
$date1 = date_create($user['inserton']);
$date2 = date_create(date());
$diff = date_diff($date1,$date2);
$diff = $diff->format("%y");
if($diff > 5){
    mysqli_query($con,"DELETE FROM User WHERE id= '$UserId'");
 }else{
    echo "not complete 5 years ";
  }

?>

CodePudding user response：

you have to execute the Delete query after the if condition, the code will look like this :

if($user['inserton'] > 5 years){
  $sql = "DELETE FROM User WHERE id= '$UserId'";

  if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
  } else {
    echo "Error deleting record: " . $conn->error;
  }
}