Update field value in Mysql using PHP if condition

Question

I have a table in Mysql named "Register" where it has the following fields:

• id - Type: Auto Increment - Primary Key
• dataCadastro - Type: Date
• statusFK - Values 1 (On) and 2 (Off) ---> In the case this field is related to a table called "status" that is, it is a Foreing Key within the table "registration")

In case I created an index.php page with an HTML table with these three fields and would like that when the field "dataCadastro" is less than the current date, the field statusFK automatically updated to 2 (disabled).

However, I am using the following code below but it is updating all the values of the field "statusFK" for 2 (disabled) which in case would be only the field with date less than today’s date:

if ( date('Y-m-d',strtotime($row['dataCadastro'])) < date('Y-m-d')){

$connection->query("UPDATE Cadastro SET statusFK = '2'");

}

Below is the connection code to the database where the table Register this located:

<?php

$username = 'root';
$password = '';
$connection = new PDO( 'mysql:host=localhost;dbname=mysystem;charset=utf8;', $username, $password );

?>

How could I change my above code for such? Thank you.

Answer 1

I believe you will get a better result using php’s Datetime() class. How it works is simple and allows you to compare dates in a practical way. I will describe the standard usage, and you adapt your needs, ok? ;)

The basic syntax is:

$dataAtual = new DateTime();
$dataComparacao = new DateTime($row['dataCadastro']);

To print the date, use the "format" method, as below:

$dataAtual->format('//Formato que a data e/ou hora devem aparecer');

To compare the dates, just follow the same logic you were using. With this you will already be able to compare your dates and also check whether the dates are being set correctly.

If you don’t make it, say there that I try to make it clear to you.