Statistics query

Need some help :)

[omoutop]

Hi to all!

I have this query to display some statistics...

<?
$query11 = "select * from reservations where Logtime <= '$date2' and Logtime >= '$date1' and URL='$url' and StateCountry LIKE '$StateCountry'";
$result11 = mysql_query($query11);
/////////looping to get results
while ($row11=mysql_fetch_assoc($result11))
{
$logtime = $row11 ['Logtime'];
list($string1,$string2) = explode(" ",$logtime);
$new_string = $string1.$string2;
$logtime2 = $string1;
$logtime3 = $string2;

$query50 = "select count(idc) as idc from reservations where Logtime LIKE '{$logtime2}%' and Logtime <= '$date2' and Logtime >= '$date1' and URL='$url' and StateCountry LIKE '$StateCountry'";
$result50 = mysql_query($query50);
$resurl50=mysql_fetch_assoc($result50);
$totalurl50 = $resurl50['idc'];

?>
Date : <strong><? echo $logtime2?></strong>, Time(s): <strong><? echo $totalurl50?></strong><br>

<?
};

?>
/////////////which returns in my browser this:

Date : 2005-05-10, Time(s): 2
Date : 2005-05-10, Time(s): 2
Date : 2005-05-09, Time(s): 2
Date : 2005-05-09, Time(s): 2
Date : 2005-05-05, Time(s): 2
Date : 2005-05-05, Time(s): 2
Date : 2005-04-17, Time(s): 1
Date : 2005-04-05, Time(s): 1
Date : 2005-05-17, Time(s): 1
Date : 2005-05-24, Time(s): 1
Date : 2005-05-30, Time(s): 2
Date : 2005-05-30, Time(s): 2
Date : 2005-05-31, Time(s): 1
.............................
As you can see the date 2005-05-10(and others) is displayed like :
Date : 2005-05-10, Time(s): 2
Date : 2005-05-10, Time(s): 2

instead of:
Date : 2005-05-10, Time(s): 2

and so on and so forth....
Date : 2005-05-30, Time(s): 3
Date : 2005-05-30, Time(s): 3
Date : 2005-05-30, Time(s): 3

instead of :
Date : 2005-05-30, Time(s): 3 (one time)
////////////////////////////////////////
This happens cause Logtime is stored in the db as Date and time....if it was only the date then i would select distinct logtimes and then count the rows for each one to display num of times...but in my case I cant....Is there any way to display the results in the style of :Date : 2005-05-30, Time(s): 3 (one time)?

Sorry if my post/request is big or hard to understand...any help is appreciated....

[coopster]

Why not just use the date portion of the DATETIME value? Could you use that to get your distinct listing directly in the result set returned?

[omoutop]

Thx for your reply coopster,

No i havent tryied that and I have no idea how to do it...is there any way to embbed it in my query and get only the date portion of the DateTime?

$query11 = "select distinct Logtime from reservations where Logtime <= '$date2' and Logtime >= '$date1' and URL='$url' and StateCountry LIKE '$StateCountry'";

Is there any way to be embbeded up there?

thx in advance!

[coopster]

To get only the date portion of a DATETIME column you can use the DATE keyword (MySQL >= 4.1.1). Otherwise, you could use a workaround (DATE_FORMAT, etc.).

Resource:
MySQL Date and Time Functions [dev.mysql.com]