MySQL count and display all dates in a range

Question

I have Problems with a MySQL query. I have the following table:

[id]
[from] DATE (Y-m-d)
[days] INT
[...]

and i have the two PHP variables $start and $end. I want to have this result for example:

$start = 2012-19-03
$end = 2012-24-03
$result = array(
[0] => COUNT of rows WHERE '2012-19-03' BETWEEN from AND from + INTERVAL days DAY
[1] => COUNT of rows WHERE '2012-20-03' BETWEEN from AND from + INTERVAL days DAY
[2] => COUNT of rows WHERE '2012-21-03' BETWEEN from AND from + INTERVAL days DAY 
[4] => COUNT of rows WHERE '2012-22-03' BETWEEN from AND from + INTERVAL days DAY
[5] => COUNT of rows WHERE '2012-23-03' BETWEEN from AND from + INTERVAL days DAY 
[6] => COUNT of rows WHERE '2012-24-03' BETWEEN from AND from + INTERVAL days DAY 
}

Till now I was doing this in a while loop with a query for each day but there should be a better way. My Problem is the GROUP BY, where the days that not exist in the data.

More detailed example:

Sample data:

id, from,       days
1,  2012-15-03, 2
2,  2012-15-03, 5
3,  2012-13-03, 20
4,  2012-16-03, 1

Desired result:

$start = 2012-11-03
$end = 2012-19-03
$result = array(
[0] => 0 (ROWS IN DB WHERE '2012-11-03' BETWEEN from AND from+days -- no match in data),
[1] => 0 (ROWS IN DB WHERE '2012-12-03' BETWEEN from AND from+days -- no match in data),
[2] => 1 (ROWS IN DB WHERE '2012-13-03' BETWEEN from AND from+days -- id #3 matches),
[3] => 1 (and so on),
[4] => 3,
[5] => 4,
[6] => 3,
[7] => 2
)

like this..

hing hours without finding a way I hope you can help me, Greetings

to get this sort of array ordered and grouped by days between the PHP variables $start and $end — Feryaz
– Feryaz, Commented Mar 19, 2012 at 5:49
can you post the array you are getting from the db also the select sql query — Khurram Ijaz
– Khurram Ijaz, Commented Mar 19, 2012 at 6:09
@Feryaz: Generate a range of dates when those dates are not present in the data? — Salman Arshad
– Salman Arshad, Commented Mar 19, 2012 at 6:46

John Woo · Accepted Answer · 2012-03-19 06:11:59Z

1

try this:

SELECT `from`, COUNT(*) TotalRows
FROM tableName
WHERE `from` <= date('$start') AND `from` >= date('$end')
GROUP BY `from`
ORDER BY `from`

answered Mar 19, 2012 at 6:11

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Feryaz Over a year ago

I dont want them ordered by from, they should be ordered by the days from the PHP variabls.

John Woo Over a year ago

ok, so what do you mean by days from the PHP variable? can you elaborate further? thanks..

Feryaz Over a year ago

if $start is '2012-19-03' and $end is '2012-20-03'. the first element of array should have the value "COUNT of rows WHERE '2012-19-03' BETWEEN from AND from + INTERVAL days DAY" and the second "COUNT of rows WHERE '2012-20-03' BETWEEN from AND from + INTERVAL days DAY". That means its get ordered by the days between $start and $end.

John Woo Over a year ago

by removing ORDER BY from, does it satisfy your requirements?

Salman Arshad Over a year ago

Wrong. It does not take into account the BETWEEN from AND from + INTERVAL days part.

|

score 0 · Accepted Answer · 2012-03-19 06:14:57Z

0

I am not sure about what exactly you asked

But Seems you can use sum with if /else and group by to achive :

like below :

select sum(if( '2012-19-03' BETWEEN `from` AND date_Add(`from` , INTERVAL days DAY)),1,0)) as firstdaycount,
sum(if( '2012-20-03' BETWEEN `from` AND date_Add(`from` , INTERVAL days DAY)),1,0)) as secondaycount,
..
....
.....
......
.....
where [...]
Group By ...

Let me know if you need further assistance.

edited Mar 19, 2012 at 6:14

answered Mar 19, 2012 at 6:09

user319198

2 Comments

Feryaz Over a year ago

That would mean i need to build the querie in a loop, isnt there a more flexible one? the $start and $end variables are not always the same and havnt always the same amount of days between.

user319198 Over a year ago

Tried @johntotetwoo solution . NOt exacly you want ?

Collectives™ on Stack Overflow

MySQL count and display all dates in a range

2 Answers 2

8 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related