php query is not adding all data to jsonarray

Question

i am new to PHP OOP , i have a database that contains products, the goal is to extract for every product grouped by ligne field all the months data of the products, after i executed the result i have been surprised that only the data of the month 8 is displayed while there are some products that have data in months like 4 or 6.

Here the database rows:

Here the json Result array and we can observe that some products qte in some months is displayed as zero while their qte is not zero in some cases

[
    {
        "id": "8",
        "ligne": "Biscuit",
        "produit": "Major",
        "date": "2021-08-11",
        "heure": "10:00",
        "qte": "380",
        "months": [
            {
                "month": 1,
                "qte": 0
            },
            {
                "month": 2,
                "qte": 0
            },
            {
                "month": 3,
                "qte": 0
            },
            {
                "month": 4,
                "qte": 0
            },
            {
                "month": 5,
                "qte": 0
            },
            {
                "month": 6,
                "qte": 0
            },
            {
                "month": 7,
                "qte": 0
            },
            {
                "month": 8,
                "qte": 380
            },
            {
                "month": 9,
                "qte": 0
            },
            {
                "month": 10,
                "qte": 0
            },
            {
                "month": 11,
                "qte": 0
            },
            {
                "month": 12,
                "qte": 0
            }
        ],
        "year": "2021"
    },
    {
        "id": "6",
        "ligne": "Eau",
        "produit": "Safia 1.5",
        "date": "2021-08-11",
        "heure": "14:00",
        "qte": "320",
        "months": [
            {
                "month": 1,
                "qte": 0
            },
            {
                "month": 2,
                "qte": 0
            },
            {
                "month": 3,
                "qte": 0
            },
            {
                "month": 4,
                "qte": 0
            },
            {
                "month": 5,
                "qte": 0
            },
            {
                "month": 6,
                "qte": 0
            },
            {
                "month": 7,
                "qte": 0
            },
            {
                "month": 8,
                "qte": 320
            },
            {
                "month": 9,
                "qte": 0
            },
            {
                "month": 10,
                "qte": 0
            },
            {
                "month": 11,
                "qte": 0
            },
            {
                "month": 12,
                "qte": 0
            }
        ],
        "year": "2021"
    },
    {
        "id": "12",
        "ligne": "Lait",
        "produit": "Vitalait 1/2",
        "date": "2021-08-11",
        "heure": "8:00",
        "qte": "379",
        "months": [
            {
                "month": 1,
                "qte": 0
            },
            {
                "month": 2,
                "qte": 0
            },
            {
                "month": 3,
                "qte": 0
            },
            {
                "month": 4,
                "qte": 0
            },
            {
                "month": 5,
                "qte": 0
            },
            {
                "month": 6,
                "qte": 0
            },
            {
                "month": 7,
                "qte": 0
            },
            {
                "month": 8,
                "qte": 379
            },
            {
                "month": 9,
                "qte": 0
            },
            {
                "month": 10,
                "qte": 0
            },
            {
                "month": 11,
                "qte": 0
            },
            {
                "month": 12,
                "qte": 0
            }
        ],
        "year": "2021"
    },
    {
        "id": "10",
        "ligne": "Salami",
        "produit": "Mazraa",
        "date": "2021-08-11",
        "heure": "8:00",
        "qte": "570",
        "months": [
            {
                "month": 1,
                "qte": 0
            },
            {
                "month": 2,
                "qte": 0
            },
            {
                "month": 3,
                "qte": 0
            },
            {
                "month": 4,
                "qte": 0
            },
            {
                "month": 5,
                "qte": 0
            },
            {
                "month": 6,
                "qte": 0
            },
            {
                "month": 7,
                "qte": 0
            },
            {
                "month": 8,
                "qte": 570
            },
            {
                "month": 9,
                "qte": 0
            },
            {
                "month": 10,
                "qte": 0
            },
            {
                "month": 11,
                "qte": 0
            },
            {
                "month": 12,
                "qte": 0
            }
        ],
        "year": "2021"
    },
    {
        "id": "4",
        "ligne": "Yaourt",
        "produit": "Delice",
        "date": "2021-08-11",
        "heure": "12:00",
        "qte": "1020",
        "months": [
            {
                "month": 1,
                "qte": 0
            },
            {
                "month": 2,
                "qte": 0
            },
            {
                "month": 3,
                "qte": 0
            },
            {
                "month": 4,
                "qte": 0
            },
            {
                "month": 5,
                "qte": 0
            },
            {
                "month": 6,
                "qte": 0
            },
            {
                "month": 7,
                "qte": 0
            },
            {
                "month": 8,
                "qte": 1020
            },
            {
                "month": 9,
                "qte": 0
            },
            {
                "month": 10,
                "qte": 0
            },
            {
                "month": 11,
                "qte": 0
            },
            {
                "month": 12,
                "qte": 0
            }
        ],
        "year": "2021"
    }
]

Here the Php Code :

   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
 );


        $stmt = $this->conn->prepare("SELECT SUM(qte) as total , id ,ligne, produit, date, heure FROM production where YEAR(date) = ?  GROUP BY LOWER(ligne) ");
        $stmt->execute([$year]);
       
    while( $row = $stmt->fetch(pdo::FETCH_ASSOC)){
        $date_arrrow = explode("-", $row["date"]);  
        $rowmonth = $date_arrrow[1];
        $ligne = $row["ligne"];
        $produit = $row["produit"];
        $p = new ProductionByMonth($row["id"],$row["ligne"],$row["produit"],$row["date"],$row["heure"],$row["total"],-1,$year);
        $prodmonths = array();
    foreach ($months as $month){
       
        if(($rowmonth == $month) ){
            array_push($prodmonths,new Months($month,(int) $row["total"]));
            // array_push($prods, new ProductionByMonth($row["id"],$row["ligne"],$row["produit"],$row["date"],$row["heure"],$row["total"],$month,$year));
         }else if ($rowmonth != $month){
            array_push($prodmonths,new Months($month,0));
            // array_push($prods, new ProductionByMonth($row["id"],$row["ligne"],$row["produit"],$row["date"],$row["heure"],0,$month,$year));  
          }
            
        }
    $p->months = $prodmonths;
    array_push($prods,$p);
    
    }
   

    echo json_encode($prods);   


   }

My wish is to find a way to correctly extract all the correct qte in the json array for every product grouped by ligne field

can you highlight which product is incorrect? the first one IS correct... — Erik
– Erik, Commented Aug 15, 2021 at 23:15
can you post the data in a table so it can easily be copied? — Erik
– Erik, Commented Aug 16, 2021 at 23:56

Rafael · Accepted Answer · 2021-08-16 13:41:51Z

I recommend grouping and aggregating the data entirely in the application. $grp is the core data structure. However, it needs a second pass for aggregation. Performance-wise, this is okay, as it is O(n).

I created a live example at http://road-to-eng.rlc.ninja/. I also recommend separating the aggregation logic from the presentation logic (unlike my implementation).

Here's the code:

<?php
// DB LOGIC
$pdo = new PDO("mysql:host=<redacted>;dbname=<redacted>", "<redacted>", "<redacted>");

$flig = $_POST["ligne"];

if (isset($flig) && $flig != "all") {
    $sql = "select * from orders where ligne = ?;";
    $params = [$flig];
} else {
    $sql = "select * from orders;";
    $params = [];
}

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$res = $stmt->fetchAll();

// GROUPING LOGIC
$lignes = [];
$grp = [];
foreach ($res as $ord) {
    $mo = (int) substr($ord["date"], 5, 2);
    $ligne = $ord["ligne"];
    $lignes[$ligne] = null;

    if (!isset($grp[$ligne])) {
        $grp[$ligne] = [];
    }

    if (!isset($grp[$ligne][$mo])) {
        $grp[$ligne][$mo] = [];
    }

    array_push($grp[$ligne][$mo], $ord);
}
$lignes = array_keys($lignes);
?>

<form action="" method="post">
    <table>
        <tr>
            <td>
                <select name="ligne">
                    <option value="all" selected>-- all --</option>
<?php foreach ($lignes as $l) { ?>
                    <option value="<?php echo $l ?>"><?php echo $l ?></option>
<?php } ?>
                </select>
            </td>
            <td><input type="submit" /></td>
        </tr>
    </table>
</form>

<pre>
<?php
// PRESENTATION AND AGGREGATION LOGIC (tightly coupled, not good practice)
printf("<strong>YEAR %s</strong>\n", "2021");
printf("\n");
foreach ($grp as $ligne => $ords_of_mo) {
    printf("%s\n", $ligne);
    foreach ($ords_of_mo as $mo => $ords) {
        printf("\t%s\n", date("M", mktime(0, 0, 0, $mo+1, 0, 0)));

        // AGGREGATION LOGIC
        $tot_qte = 0;
        foreach ($ords as $o) {
            $tot_qte += $o["qte"];
        }

        printf("\t\t%5s %10s %15s %15s %12s %10s\n", "id", "ligne", "produit", "date", "heure", "qte");
        printf("\t\t========================================================================\n");
        foreach ($ords as $o) {
            printf("\t\t%5s %10s %15s %15s %12s %10s\n", $o["id"], $o["ligne"], $o["produit"], $o["date"], $o["heure"], $o["qte"]);
        }
        printf("\t\t%5s %10s %15s %15s %12s <strong>%10s</strong>\n", "", "", "", "", "", $tot_qte);
        printf("\n");
    }
}
?>
</pre>

Collectives™ on Stack Overflow

php query is not adding all data to jsonarray

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related