2 replies to this topic

[lightangelbr]

Hi folks!

I have a problem to solve and I don't know how to solve it.


I have a SQL Query that looks like this:

$tC = mysql_query("SELECT * FROM cdr WHERE accountcode!=\"\" ".

			 (($searchArgs != "") ? "AND {$searchArgs}" : "")."");

Where $searchArgs always is something like this (but it change everytime I make a query)->

calldate > '2010-04-04 00:00:00' AND calldate < '2010-07-09 23:59:59'

So the Query return to me something like this:

10/04/2010 10:15:00	

10/05/2010 11:59:00	

10/05/2010 09:50:00	

10/06/2010 08:55:00	

10/07/2010 07:00:00	

10/07/2010 08:00:00	

10/07/2010 09:00:00	

24/08/2010 15:00:00

I need some function that return to me the number of times that a 'month-year' occur.

To be more specific, I wanna the output like this:

/04/2010 - 1 times

/05/2010 - 2 times

/06/2010 - 1 times

/07/2010 - 3 times

/08/2010  -1 times

I know that I could do this with the following code:

$sql = mysql_query("SELECT COUNT(id) as total,date,DATE_FORMAT(date,'%m/%Y') as date_formated FROM mytable GROUP BY month(date),YEAR(date)");

while($data = mysql_fetch_assoc($sql)){

echo $data['date_formated'].' - '.$data['total'].'<br>';

}

But this Query need that the date be already on the table, but as you could see, the range of the date are dynamic.

Somebody could help me with this?

[BlaineSch]

I'd be happy to help. I'm a little bit confused about the table structure so if you could post that it would be great.

If you can get it to select them before it probably would not be hard to format it correctly using the last query you posted.

You could always use PHP and arrays, since you can put anything in the array key you could basically see if it's set, if it's not set to zero (0) if it's already been set you can just increment it. Then output it using foreach.

[lightangelbr]

Oh thanks!

The table is basically


accountcode -> Integer (looks like 254)
calldate -> datetime (looks like 2010-03-28 23:59:23)