php how to set a value for not found rows in array

Question

i am new in Php 7 , i am trying to write a function that checks in the database if a product has a qte in a certain month(extracted from date field) but in the case that in that the product date do not have this month so all data have to be added to the array but the qte must be set to zero to the value of that month , i got results only of the existing qte in the months , but the results of the months that the products do not have any qte are not in my final array

Here what i have tried:

   public function getProductsStatsByMonths() {
    $today =  date("Y-m-d"); 
    $date_arr = explode("-", $today);  
    $year = $date_arr[0];
    $month = $date_arr[1];
    $day = $date_arr[2];
 
    $prods = array();
    $months = array(
    1 ,
    2 ,
    3 ,
    4 ,
    5 ,
    6 ,
    7 ,
    8 ,
    9 ,
    10 ,
    11 ,
    12
 );

    foreach ($months as $month){
        $stmt = $this->conn->prepare("SELECT sum(qte) as total,ligne, produit,date,heure,qte FROM production where YEAR(date) = ? and MONTH(date) = ? group by LOWER(ligne) ");
        $stmt->execute([$year,$month]);

        while( $row = $stmt->fetch(pdo::FETCH_ASSOC)){
            array_push($prods, new ProductionByMonth(0,$row["ligne"],$row["produit"],$row["date"],$row["heure"],$row["qte"],$month,$year));
        }
    }

    echo json_encode($prods);
   
   }

the result im obtaining:

[
    {
        "id": 0,
        "ligne": "Biscuit",
        "produit": "Major",
        "date": "2021-08-10",
        "heure": "10:00",
        "qte": "130",
        "month": 8,
        "year": "2021"
    },
    {
        "id": 0,
        "ligne": "Eau",
        "produit": "Safia 1.5",
        "date": "2021-08-10",
        "heure": "14:00",
        "qte": "200",
        "month": 8,
        "year": "2021"
    },
    {
        "id": 0,
        "ligne": "Lait",
        "produit": "Vitalait 1/2",
        "date": "2021-08-10",
        "heure": "8:00",
        "qte": "80",
        "month": 8,
        "year": "2021"
    },
    {
        "id": 0,
        "ligne": "Salami",
        "produit": "Mazraa",
        "date": "2021-08-10",
        "heure": "8:00",
        "qte": "100",
        "month": 8,
        "year": "2021"
    },
    {
        "id": 0,
        "ligne": "Yaourt",
        "produit": "Delice",
        "date": "2021-08-10",
        "heure": "12:00",
        "qte": "150",
        "month": 8,
        "year": "2021"
    }
]

for example this row contains this product that have a qte in the month 8:

{
            "id": 0,
            "ligne": "Biscuit",
            "produit": "Major",
            "date": "2021-08-10",
            "heure": "10:00",
            "qte": "130",
            "month": 8,
            "year": "2021"
        },

my goal is to add extra rows for this same product for the rest of the months but set qte for them as "0"

my goal is to make sure that my array in the end contains also the months that there is no qte (quantity) , and the value of qte must be set to zero and the data must be displayed like all data that exists except the value of the field qte

knot22 · Answer 1 · 2021-08-11T21:38:24.213

Here's a description of a general approach that works for this type of problem.

Get the unique products from the database data.
Create a calendar of all the dates needed.
Do the equivalent of a database "cross join" in PHP of the datasets from 1 and 2 and set qte to 0 for all combinations generated.
Overwrite qte for those combinations that actually exist in the database with a non-zero quantity.

"Combination" means product and date. So if you create a calendar that contains the 12 months of 2021 and have 5 products then step 3 will generate 60 combinations.

Also, if $day is not used anywhere in the code, there's no need to create it.

Edit - here is the code:

// this is modified sample data
// changes made:
// set date values to first day of each month
// added "productId" with fabricated values since "id" was 0 for everything in original data and therefore not useful
// added another entry for productID = 12 with a different date
$prods = '[
    {
        "id": 0,
        "ligne": "Biscuit",
        "produit": "Major",
        "date": "2021-08-01",
        "heure": "10:00",
        "qte": "130",
        "month": 8,
        "year": "2021",
        "productId": 12
    },
    {
        "id": 0,
        "ligne": "Biscuit",
        "produit": "Major",
        "date": "2021-09-01",
        "heure": "12:00",
        "qte": "158",
        "month": 9,
        "year": "2021",
        "productId": 12
    },
    {
        "id": 0,
        "ligne": "Eau",
        "produit": "Safia 1.5",
        "date": "2021-08-01",
        "heure": "14:00",
        "qte": "200",
        "month": 8,
        "year": "2021",
        "productId": 16
    },
    {
        "id": 0,
        "ligne": "Lait",
        "produit": "Vitalait 1/2",
        "date": "2021-08-01",
        "heure": "8:00",
        "qte": "80",
        "month": 8,
        "year": "2021",
        "productId": 38
    },
    {
        "id": 0,
        "ligne": "Salami",
        "produit": "Mazraa",
        "date": "2021-08-01",
        "heure": "8:00",
        "qte": "100",
        "month": 8,
        "year": "2021",
        "productId": 27
    },
    {
        "id": 0,
        "ligne": "Yaourt",
        "produit": "Delice",
        "date": "2021-08-01",
        "heure": "12:00",
        "qte": "150",
        "month": 8,
        "year": "2021",
        "productId": 22
    }
]';

// create an associative array of products - assign a unique value as key (this will be useful in a later step)
$products = [];
foreach (json_decode($prods, true) as $product) {
    $key = $product['productId'] . '_' . $product['date'];
    $products[$key] = $product;
}

// get unique product IDs from database data
$productIds = [];
foreach ($products as $product) {
    $productIds[] = $product['productId'];
}
$uniqueProductIds = array_unique($productIds);

// create a calendar of all possible dates needed (just did a year's worth in this example)
$year = date("Y");
$buckets = [];
for ($month = 1; $month <= 12; $month++) {
    $buckets[] = date($year . '-' . str_pad($month, 2, '0', STR_PAD_LEFT) . '-01');
}

// do cross join equivalent to create all combinations of bucket and unique product ID (in this example: 12 buckets x 5 unique products IDs = 60 combinations)
$cartesianProduct = [];
foreach ($buckets as $bucket) {
    foreach ($uniqueProductIds as $uniqueProductId) {
        $data = [];
        $data['date'] = $bucket;
        $data['id'] = $uniqueProductId;
        $data['qte'] = 0;
        $key = $uniqueProductId . '_' . $bucket;
        $cartesianProduct[$key] = $data;
    }
}

// overwrite combination data (in $cartesianProduct) with data from database (in $products) - this is where $key in $products and in $cartesianProduct is critical
$result = array_merge($cartesianProduct, $products);

// look at results    
echo '<pre>' . print_r($result, 1) . '</pre>';

Hello , thank you for your answer but i did not understand half of it — Road to engineering, Aug 11 '21 at 01:28
Create a calendar of all the dates needed. Do the equivalent of a database "cross join" in PHP of the datasets from 1 and 2 and set qte to 0 for all combinations generated. — Road to engineering, Aug 11 '21 at 01:29
By cross join I mean a cartesian product. See https://stackoverflow.com/questions/21738966/php-nested-foreach-to-create-something-like-cartesian-product — knot22, Aug 11 '21 at 14:09
@Roadtoengineering I added code so you can see how it works. To look at any intermediate dataset just mimic the very last line of code. For instance, to see what's in `$buckets` add this line of code: `echo '
' . print_r($buckets, 1) . '
';` to display its contents in the browser. — knot22, Aug 11 '21 at 21:31

php how to set a value for not found rows in array

1 Answers1