Hi i want to the totals of the date range even if there is no date in that date range for mysql
some weeks have no signups
week 1 15
week 2 0
week 3 5
week 4 12
in the current query because there is no data for week 2 that week will not be shown. i need to include week 2 with values of zero
right now the returned data is:
week 1 15
week 3 5
week 4 12
which makes the graphs go from 15 down to 5 when it should go from 15 down to zero then up to 5.
what is the sql syntax for this please
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
hi, mankowitz,
Thanks for the help. I thought it might be possible because i found the soulution in a ms access thread:
http://www.experts-exchang
Id rather find a solution that didnt include altering the database. Maybe it might be better for me to look for a solution that includes php.
ok, got it with php.
function clean_weeks_array($date_fr
$i = $date_from;
while( $i < $date_to){
$i = strtotime(date("Y-m-d", $i) . " +1 week");
$weeks[date("Y - W", $i)] = $i;
}
return $weeks;
}
use the above function to create a list of all the weeks between the start and the finish dates, then use the SQL to create another array of all the weeks with data, then use array_merge() to merge the 2 sets of data.
Because the arrays keys are formatted weeks instead of numerical keys the array merge overwrites the weeks that have data instead of adding the values to the end of the array.
SQL:
SELECT band_time,
DATE_FORMAT(FROM_UNIXTIME(
while loop:
while ($row = mysql_fetch_array($result)
$weeks2[$row["week"]] = $row["band_time"];
Business Accounts
Answer for Membership
by: mankowitzPosted on 2008-08-24 at 14:30:51ID: 22302103
unfortunately, the only way to do this is make another table that has all the days you want in the range and then make a left outer join with that table.
so....
suppose you have a table that has all the available band days in it
table band_days: band_time datetime;
You would do a query like this
SELECT DATE_FORMAT(FROM_UNIXTIME(
DATE_FORMAT(FROM_UNIXTIME(
count(j.band_id) as users,
sum( case when(j.band_quota = '-3') then 1 else 0 end) as fan3,
sum( case when(j.band_quota = '-2') then 1 else 0 end) as fan2,
sum( case when(j.band_quota = '-1') then 1 else 0 end) as fan1,
sum( case when(j.band_quota = '1') then 1 else 0 end) as musician1,
sum( case when(j.band_quota = '2') then 1 else 0 end) as musician2,
sum( case when(j.band_quota = '3') then 1 else 0 end) as musician3
FROM band_days b LEFT OUTER JOIN jamroom_band_info j
ON (week(b.band_time) = week(from_unixtime(j.band_
WHERE j.band_active = 1
and j.band_time BETWEEN $date_from AND $date_to
GROUP by week