How to fill an array when the data does not exist?

Question

I have a request that search the "Accommodations" and "Catering" services present on contracts. This query makes a sum per year. Everything works well ! The retrieved data is stored in an Array which is then sent to Javascript which can calculate and display a dynamic graph... The problem is that some years don't have "Restores", so the While Loop can't add information to the Array that doesn't exist, so the graph doesn't work, the data's colums shifts !

   while ($data = $requete1->fetch()) {
        // Hébergement : 
        if($data['P_TYPE']=="HEBERGEMENT"){    
            $hebergement[] =  array("label"=> $data['ANNEE'], "y"=> $data['ca_total']);
        }

        // Restauration : 
        if($data['P_TYPE']=="RESTAURATION"){    
            $restauration[] =   array("label"=> $data['ANNEE'], "y"=> $data['ca_total']);
        }
    }

I would need a result like this, With 0 values added when the services do not exist :

    // Hébergement années non comprises en bdd :  
    $hebergement[] = array("label"=> "2021", "y"=> 68279);
    $hebergement[] = array("label"=> "2020", "y"=> 30457);
    $hebergement[] = array("label"=> "2019", "y"=> 0);
    $hebergement[] = array("label"=> "2018", "y"=> 57993);
    $hebergement[] = array("label"=> "2017", "y"=> 49702);
    $hebergement[] = array("label"=> "2016", "y"=> 45493);
    $hebergement[] = array("label"=> "2015", "y"=> 38460);
        
    // Restauration années non comprises en bdd :  
    $restauration[] = array("label"=> "2021", "y"=> 12117);
    $restauration[] = array("label"=> "2020", "y"=> 697);
    $restauration[] = array("label"=> "2019", "y"=> 2687);
    $restauration[] = array("label"=> "2018", "y"=> 0);
    $restauration[] = array("label"=> "2017", "y"=> 40);
    $restauration[] = array("label"=> "2016", "y"=> 350);
    $restauration[] = array("label"=> "2015", "y"=> 200);*/

Thank you in advance for your help !

Today I try this,like ADyson said, but it doesn't work too :

while ($data = $requete3->fetch()) {
        // Hébergement :
        if ($data['P_TYPE']=="HEBERGEMENT"){
            if(isset($annee) AND ($annee-$data['ANNEE']>=1)){
                $restauration[] =  array("label"=> $data['ANNEE'], "y"=> 0);
                $annee='';
            }
            else{
                $hebergement[] =  array("label"=> $data['ANNEE'], "y"=> $data['ca_total']);
                $annee=$data['ANNEE'];
            }
        }
        // Hébergement :
        if ($data['P_TYPE']=="RESTAURATION"){
            if(isset($annee) AND ($annee-$data['ANNEE']>=1)){
                $hebergement[] =  array("label"=> $data['ANNEE'], "y"=> 0);
                $annee='';
            }
            else{
                $restauration[] =  array("label"=> $data['ANNEE'], "y"=> $data['ca_total']);
                $annee=$data['ANNEE'];
            }
        }      
    }

Answer 1

You need to create another cycle and fulfill the missing information You can do it on DB, in PHP, or JS.

Here is the PHP solution, since you provide PHP code.

First, modify your cycle and get the min and max year. Then, zero-pad missing years between min and max year.

$min = 9999; 
$max = date("Y"); //edited
while ($data = $requete1->fetch()) {
        // Hébergement : 
        if($data['P_TYPE']=="HEBERGEMENT"){    
                $hebergement[] =  array("label"=> $data['ANNEE'], "y"=> $data['ca_total']);
        }

        // Restauration : 
        if($data['P_TYPE']=="RESTAURATION"){    
                $restauration[] =   array("label"=> $data['ANNEE'], "y"=> $data['ca_total']);
        }

        /// Find the min year in datas
        if ($data['ANNEE'] < $min) $min = $data['ANNEE'];
}

I have to say, it's not the most elegant solution, but it is effective. It will work in the next years, till next year will contain datas.

----CODE EDITED I was thinking a better solution is retrieving the

$max = date("Y");

I was thinking also that, depending on the design, the best solution could also be to do it directly on js, before drawing the graph, because:

1. it reduces loads on the server distributing the extra cycle on the clients,
2. it avoids passing "zero-padding data" over the network,
3. make it work also on 2023/01/01 at 3:00 in Asia, showing the "2023" in the graph. (With PHP that's impossible).

But if data are so small, and if it's not a million requests/day server, and if you don't mind that Asia should wait GMT zero time to get the graph update on the new year, this one in PHP is good.

--- EDIT 2 A solution for countries with a timezone lower then yours, to show up 2023 in the graph when it's 2023/01/01 to them but not yet to your server tz, would be to use the "year of tomorrow".

$datetime = new DateTime('tomorrow');
$max = $datetime->format('Y');

So, since NNNN/12/31, it will show up also the "NNNN+1" year in X labels in the graph for everybody.

----- UPDATE After comments, I noticed there was an error in my answer

Given min and max, can be 2013-->2023, The direction for the cycle, from min to max, or from max to min, Is irrelevant.

/// Cycle between min and max, and add zero padding value when data is missing
//// It append the values at the end of the array
for ($i=$min; $i<=$max; $i++) {
        // Those are to search in a multidimensional associative array by key/value 
        if ( array_search($i, array_column($restauration, 'label')) === FALSE ) $restauration[] =   array("label"=> $i, "y"=> 0); 
        if ( array_search($i, array_column($hebergement, 'label')) === FALSE) $hebergement[] =   array("label"=> $i, "y"=> 0); 
        
}
/// Those are to sort both arrays DESC, it works for PHP > 7.5
usort($restauration, function ($item1, $item2) {return $item2['label'] <=> $item1['label']; });
usort($hebergement, function ($item1, $item2) {return $item2['label'] <=> $item1['label']; });

Answer 2

THIS CODE WORKS !!!

// Recherche de la date la plus récente : 
    $requete1 = $bdd->query('SELECT MAX(YEAR(contrats.date_debut)) as MAX FROM contrats');
    $max = $requete1->fetch();

    // Recherche de la date la plus ancienne : 
    $requete2 = $bdd->query('SELECT MIN(YEAR(contrats.date_debut)) as MIN FROM contrats');
    $min = $requete2->fetch();


// Création des l'Array Hébergement & restauration vides : 
    for ($i=$max['MAX']; $i >= $min['MIN'] ; $i--) { 
        $hebergement[] =  array("label"=> $i, "y"=> 0);
        $restauration[] =   array("label"=> $i, "y"=> 0);
    }


    // Remplissage des Array : 
    while ($data = $requete3->fetch()) {
        // Hébergement :
        if ($data['P_TYPE']=="HEBERGEMENT"){
            $id = array_search($data['ANNEE'], array_column($hebergement, 'label')); 
            $hebergement[$id]['y'] = $data['ca_total'];                       
        }
        // Restauration :
        if ($data['P_TYPE']=="RESTAURATION"){
            $id = array_search($data['ANNEE'], array_column($restauration, 'label')); 
            $restauration[$id]['y'] = $data['ca_total'];                       
        }
    }