CODEWITHSUNDEEP
phpmysql
Nasir Hussain
Nasir Hussain

Reputation: 139

Query missing months in which we haven't sold anything?

My query is:

$sql="SELECT  COUNT(`Variant`) AS tsold, MONTHNAME(`sold_date`) AS mname FROM `vehicle_sold` GROUP BY MONTH(`sold_date`) ";
$result = mysqli_query($conn,$sql);

   while($row = mysqli_fetch_array($result)) {  


    echo $row["mname"],"---", $row["tsold"],"<br />";

}

which gives me the below result:

January---1
February---2
March---7
April---11
May---6
July---1

There are no sales in June so what I want is the query to return "June---0" for example. If it also shows the next months up to December it's ok. I'd like the following output:

January---1
February---2
March---7
April---11
May---6
June---0
July---1
Aug---0
Sept---0
Oct---0
Nov---0
Dec---0

Upvotes: 0

Views: 410

Answers (2)

W van Rij
W van Rij

Reputation: 536

I assume you have actually no data in the database if you did not sell anything. Hence its a bit hard to generate a month without info.

You want an array with all the months, corresponding with the amount of sales. I would suggest you make something like this:

Prepare an array with the months with all the values on 0 as default (you can make this array list 'nicer', but just now as example).

$data['March'] = 0;
$data['April'] = 0;

Now you can iterate like you did

while($row = mysqli_fetch_array($result)) {
$data[$row["mname"]] = $row["tsold"];
}

If it does not get filled by the database, the value would still be 0. Otherwise it gets overwritten by the database value.

Upvotes: 1

Sougata Bose
Sougata Bose

Reputation: 31749

Use an array of month names - 

$months = array('January', 'February', ..., 'December');

Generate and array with the data returned from database - 

while($row = mysqli_fetch_array($result)) {
    $data[$row["mname"]] = $row["tsold"];
}

And print them accordingly - 

foreach($months as $value) {
    echo $value.' - '. (array_key_exists($value, $data) ? $data[$value] : 0) . '<br/>';
}

Upvotes: 3

Related Questions