Creating group and array with mysql query

[Adem]

Hello,

I want to set the number of months in the following array key fields and have 0 zero value in the value field for the non sales months.

How is this done?

    $sql = $db->prepare( " SELECT product, COUNT(*), MONTH(date) FROM table WHERE YEAR(date)='2022' GROUP BY product, MONTH(date) ORDER BY MONTH(date) ASC, COUNT(*) DESC " );
    $sql->execute();
    $sql->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_COLUMN);

With the above query the following server is giving

Array
(
    [product_1] => Array
        (
            [0] => 17
            [1] => 33
            [2] => 14
            [3] => 36
            [4] => 26
            [5] => 17
            [6] => 18
            [7] => 24
            [8] => 23
            [9] => 51
            [10] => 30
            [11] => 23
        )

    [product_2] => Array
        (
            [0] => 10
            [1] => 20
            [2] => 11
            [3] => 7
            [4] => 6
            [5] => 9
            [6] => 3
            [7] => 2
            [8] => 10
            [9] => 8
            [10] => 6
            [11] => 8
        )

    [product_3] => Array
        (
            [0] => 5
            [1] => 4
            [2] => 4
            [3] => 6
            [4] => 1
            [5] => 2
            [6] => 2
            [7] => 6
            [8] => 4
            [9] => 2
            [10] => 2
            [11] => 3
        )

    [product_4] => Array
        (
            [0] => 2
            [1] => 3
            [2] => 2
            [3] => 3
            [4] => 2
            [5] => 3
            [6] => 1
            [7] => 2
            [8] => 2
            [9] => 4
            [10] => 4
            [11] => 3
        )

    [product_5] => Array
        (
            [0] => 1
            [1] => 3
            [2] => 3
            [3] => 2
            [4] => 6
            [5] => 4
            [6] => 1
            [7] => 2
            [8] => 1
            [9] => 4
            [10] => 4
            [11] => 3
        )

    [product_6] => Array
        (
            [0] => 1
            [1] => 3
        )

    [product_7] => Array
        (
            [0] => 2
        )

)

Sample array

    [product_6] => Array
        (
            [1] => 0
			[2] => 1
			[3] => 0
			[4] => 0
			[5] => 0
			[6] => 3
			[7] => 0
			[8] => 0
			[9] => 0
			[10] => 0
			[11] => 0
            [12] => 0
        )

[phdr]

You can do this in a query, by creating a table with the month numbers, then using a LEFT JOIN between the month table and your data, which will result in null values where there is no sales data, that you can treat as zeros when you use the values, or you can do this in the php code when you fetch the data.

For the php code version, you would fill a month array with zeros, for each product, then store the actual data into the month array using the month number as the array index.

Since you are operating on the rows of data as numerically indexed arrays, rather than associative indexed rows, I recommend that you SELECT the data in order of precedence for clearity, e.g. product, month, count.

The following should (untested) work -

$sql = "SELECT product, MONTH(date), COUNT(*)
 FROM table
 WHERE YEAR(date)='2022'
 GROUP BY product, MONTH(date)";
// note: because this is grouping by MONTH(date), there will only be one row per MONTH(date) per product. therefore, the ORDER BY ... COUNT(*) DESC term does nothing and i have removed it from the query
// also, since you are grouping the data by the product and MONTH(date), the data will actually be sorted by those values, and it is not necessary to have an ORDER BY term in the query if that is the order that you want the result to be in
$stmt = $pdo->prepare($sql);
$stmt->execute();
// fetch data as numerically indexed arrays, grouped by the first column - product
$rows = $stmt->fetchAll(PDO::FETCH_NUM | PDO::FETCH_GROUP);

// index/pivot the data for each product, filling the starting array with zeros, and using the month as the array index
$data = [];
foreach($rows as $product=>$arr)
{
	$data[$product] = array_fill_keys(range(1,12), 0);
	foreach($arr as $el)
	{
	$data[$product][$el[0]] = $el[1];		
	}
}
// examine the result
echo '<pre>'; print_r($data);

[Adem]

Thank you very much for your help

This is a process that requires a lot of expertise, and it is very difficult for novices like us to do it.

Thank you again