Is it possible to replace the NULL value of a database with the current year in php pdo?

Stack Overflow Asked by Eléa Urbain on January 1, 2022

I am new to PHP / MySQL PDO. I would like to know if it was possible to change the "NULL" value of a database to do calculations without modifying the NULL in the database.

In my database, the "NULL" correspond to the current year (this is a list of prime ministers who have either not completed their term or are not dead).

I need to create a form that searches for the list of prime ministers mandating by mandate date.

I would like to replace the "NULL" of the database by a variable taking the current year generated by $var = date('Y'); in my PHP code.

Screenshot of the database

enter image description here

It’s not easy to explain. I would like the "NULL" to stay "NULL" in the database.

In my PHP code, I would like to assign a value to these NULLs without changing the structure in the database so that I can perform operations.

I created a form that displays by year all the ministers of Belgium mandating that year. I use the "NULL" in the database to say that the minister is not yet deceased or has not yet completed his term. To display the minister of the year 2020 (and who has not yet finished his mandate), I need to say that the "NULL" of the database is equivalent to the current year because we do not know predict whether the minister will resign or will have completed his term as long as it is not changed in the official year in the database.

I was wondering if it was possible to say (in PHP) that "NULL" are the current year, like for example $ var = date (‘Y’); but saying "NULL" equals date (‘Y’).

If this is not possible, how could I update the NULL data in PHP prepared request in the database based on the current year? Something like UPDATE table SET nom_colonne_1 = 'nouvelle valeur' WHERE condition

My current code to search by mandate year:

<form method="GET" action="index.php">
  <label for="annee">Année de mandat: </label>
  <input type="number" name="annee">
  <input type="submit" name="rechercher" value="Rechercher">

  $creation_belgique = 1831;
  $aujourdhui = date('Y'); // Y = reprend l'année en cours automatiquement
  if(isset($_GET['rechercher'])) { // Rechercher les premiers ministres mandatant en fonction de l'année choisie du formulaire (du début à la fin du mandat)
    $req_recherche=$bdd->prepare("SELECT * FROM premiers INNER JOIN partis ON premiers.id_partis=partis.id_partis WHERE annee_debut<=:annee AND annee_fin>=:annee"); // préformation de la requête au serveur 

    // Rechercher le roi régnant en fonction de l'année choisie du formulaire (du début à la fin du règne)
    $req_recherche_b=$bdd->prepare("SELECT * FROM rois WHERE annee_debut<=:annee AND annee_fin>=:annee"); // préformation de la requête au serveur 

    // Si l'année est inférieure à la date de la création de la Belgique -> pas de premier ministre
    if ($_GET['annee'] < $creation_belgique) {
       echo "<p>Avant 1831, date de création de la Belgique, il n'y avait pas de Premier ministre.";
    } else {  // Si l'année correspond à une date de mandat -> affichage des premiers ministres
       echo '<p>En ',$_GET['annee'],', sous le règne de ', 
       $rois['nom_rois'],', les Premiers ministres mandatant sont :';

       while($premiers_annee=$req_recherche->fetch()) {
           echo '<li>',$premiers_annee['nom_premiers'],', ',$premiers_annee['annee_debut'],', ',$premiers_annee['nom_partis'],'<br>';

Thank you for your answers

2 Answers

while selecting from column that can have NULLs you can use COALESCE function, it returns first non-null value from given list. .. not sure about MySql, but in PosgreSQL I'd use something like:

SELECT * FROM mytable 
WHERE **COALESCE**(column_with_nulls, extract('year' from CURRENT_DATE)) BETWEEN param1 AND param2

Answered by Игорь Тыра on January 1, 2022

I think IF will do the job :

Example structure :

  `id` int(32) unsigned NOT NULL,
  `datas` int(32) unsigned,
  PRIMARY KEY (`id`)
INSERT INTO `db_table` (`id`, `datas`) VALUES (1, 3), (2, null), (3, 11), (4, null);

Example request :

SELECT id, IF(datas IS NULL, NOW(), datas)
FROM db_table

Example Fiddle :!9/8626d2/4

Answered by Bazaim on January 1, 2022

Add your own answers!

Related Questions

What will this recursive function yield?

1  Asked on December 9, 2021 by user13812739


How to style Rebass Switch

5  Asked on December 9, 2021 by amaster


Redis Cluster Mode – replicas not working

1  Asked on December 9, 2021 by ramprakash


Disable seeking in mediaelement.js

1  Asked on December 9, 2021 by pelirrojo


Table not getting created sqlite android

4  Asked on December 9, 2021 by user3844417


Angularjs using JWT breaks CORS to Amazon S3 on login

2  Asked on December 9, 2021 by codephobia


Write a value with opc ua Python

2  Asked on December 9, 2021


unable to install vue chart.js in vue

2  Asked on December 9, 2021 by prabina-sht


Ask a Question

Get help from others!

© 2023 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP