AnswerBun.com

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">
</form>

<?php
  date_default_timezone_set('Europe/Brussels');
  $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 
    $req_recherche->execute(array('annee'=>$_GET['annee']));

    // 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 
    $req_recherche_b->execute(array('annee'=>$_GET['annee']));
    $rois=$req_recherche_b->fetch();

    // 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>';
       }
       $req_recherche->closeCursor();
       $req_recherche_b->closeCursor();
    }
}
?>

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 : https://www.w3schools.com/SQl/func_mysql_if.asp

Example structure :

CREATE TABLE IF NOT EXISTS `db_table` (
  `id` int(32) unsigned NOT NULL,
  `datas` int(32) unsigned,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
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 : http://sqlfiddle.com/#!9/8626d2/4

Answered by Bazaim on January 1, 2022

Add your own answers!

Related Questions

Issues with manipulating a drop down in Javascript

0  Asked on November 15, 2021 by orgg

   

How to find all the sub matrix of a N*N matrix?

1  Asked on November 15, 2021 by dronzer

     

How to make new person in class player?

2  Asked on November 15, 2021 by joshmann15

   

Accessing elements in Set

0  Asked on November 15, 2021 by lost_and_found

   

WebView onKeyListener not working

3  Asked on November 15, 2021 by federico-ponzi

   

Why is docker not completely deleting my file?

4  Asked on November 15, 2021 by schoon

   

How to update the elastic search document with python?

1  Asked on November 15, 2021 by user6882757

   

Breaking up conditionals in order to reduce DRAM usage

3  Asked on November 15, 2021 by saxpy

       

Ansible Create SubFolders Matching Pattern

1  Asked on November 15, 2021 by switch

 

Ask a Question

Get help from others!

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