help with php query with datetime()

[Paul Ferrie]

Hi guys,
Cant figure out why this wont work.
I have built a little db on my website to collect system stats.
In my DB i have 4 fields:
sysID=(No)Auto_increment
sysRes= "varchar"
sysIP="varchar"
sysDate="dateTime()"

I am trying to get the number of user visits per day. So we collect all the dates from the DB and then Group them.

SELECT DATE_FORMAT( sysDate, '%Y-%m-%d' )
FROM scStats
GROUP BY DATE_FORMAT( sysDate, '%Y-%m-%d' )
ORDER BY sysDate DESC

Does as supposed to.

Output:
2005-06-15
2005-06-14
2005-06-13
2005-06-12
2005-06-11
2005-06-10
2005-06-09
2005-06-08
2005-06-07
2005-06-06
2005-06-05
2005-06-04
2005-06-03
2005-06-02
2005-06-01

The script was tested via phpmyadmin.

in my php file i have the query as above and then the following to breakdown the array:

$result = mysql_query($query);
$visCount = mysql_num_rows($result);
for ($count = 0; $count < $visCount; $count++){
$dates = mysql_fetch_array($result);
$sysDate = $dates["sysDate"];
echo "<BR>". $count .". sys date = " . $sysDate;
}

$sysDate returns empty.

anyone got any ideas where i am going wrong?

Thanks
Paul

[mmm..pi..3.14..]

Don't know why it's returning nothing, but try gathering the data a different way. I don't trust for loops when using PHP and MySQL.


$result = mysql_query($query);
while($dates = mysql_fetch_array($result)){
$sysDate = $dates["sysDate"];
//also could try selecting the column number instead of the column name
//$sysDate = $dates[0];
echo "<BR>". $count .". sys date = " . $sysDate;
}


Hope that helps...

Eric :)

[freddycodes]

Cause you aren't aliasing the result of DATE_FORMAT


SELECT DATE_FORMAT( sysDate, '%Y-%m-%d' ) as FormattedDate
FROM scStats
GROUP BY FormattedDate
ORDER BY FormattedDate DESC




$result = mysql_query($query);
while($dates = mysql_fetch_array($result)){
$sysDate = $dates["FormattedDate"];
echo "<BR>". $count .". sys date = " . $sysDate;
}

[Paul Ferrie]

lol,
thanks guys,
By the way, i have not been recieving"post notifications"
Anyone of you having this problem?

thanks
Paul

[Paul Ferrie]

I have never encoutered a problem using loops with php.
Anyways...
next problem, returning how many visitors for each date returned.
I am trying

for ($count = 0; $count < $visCount; $count++)
{
// Extract post details from database
$dates = mysql_fetch_array($result);
$allRes = mysql_query("SELECT COUNT(*) AS totalVisits FROM scStats WHERE sysDate =". $dates["FormattedDate"]);
$totalVistors = $allRes['totalVisits'];
$output .= "<BR>&dates" . $count . "sysDate=" .$dates["FormattedDate"];
$output .= "&dates" . $count . "totalVisitors=" .$totalVisitors;

}

totalvisitors returns empty.
http://www.scotlet.co.uk/php/admcms/fetchHits.php

cheers
Paul

[freddycodes]

You didn't cast your date back to the native format or run the format_date on the sysDate for ythe comparison. But you really shouldn't be running 27 queries one for each date.

It should all be in one query.

SELECT count(sysDate) as DateCount, DATE_FORMAT( sysDate, '%Y-%m-%d' ) as FormattedDate
FROM scStats
GROUP BY FormattedDate
ORDER BY FormattedDate DESC



$result = mysql_query($query);
$count = mysql_num_rows($result);
while($dates = mysql_fetch_array($result)){
$sysDate = $dates["FormattedDate"];
$output .= "<BR>&dates" . $count . "sysDate=" .$sysDate;
$output .= "&dates" . $count . "totalVisitors=" .$dates["DateCount"];
echo "<BR>". $count .". sys date = " . $sysDate;
}

[Paul Ferrie]

HI freddy,
Thanks again.
I am still to get up to speed with querying.

Sooooo much cleaner :)

Paul

[freddycodes]

I would starting looking at some books or tutorials on SQL as a language. Especially with MySQL its easy to get by with minimal functionality, buts its actually quite powerful.