Home Tutorials Forums Articles Blogs Movies Library Employment Press
Old 06-15-2005, 05:52 PM   #1
Paul Ferrie
Scottish and mad fer it!!
 
Paul Ferrie's Avatar
 
Join Date: Jun 2002
Location: uk/scotland/glasgow
Posts: 3,606
Default help with php query with datetime()

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.
PHP Code:
SELECT DATE_FORMATsysDate'%Y-%m-%d' 
FROM scStats
GROUP BY DATE_FORMAT
sysDate'%Y-%m-%d' 
ORDER BY sysDate DESC 
Does as supposed to.
Quote:
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:
PHP Code:
$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
__________________
.:To me AS is like LEGO, Only for the big Kids :.
- InnovativeDesigns - Scotflash - About me
Paul Ferrie is offline   Reply With Quote
Old 06-15-2005, 06:25 PM   #2
mmm..pi..3.14..
/(bb|[^b]{2})/
 
mmm..pi..3.14..'s Avatar
 
Join Date: May 2004
Location: Denver, CO
Posts: 2,740
Default

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.

PHP Code:
$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
__________________
Eric Hainer
Unleaded Software
970.581.3387
Email: Eric followed by the AT symbol, unleaded software (one word) DOT com
Denver Web Design
mmm..pi..3.14.. is offline   Reply With Quote
Old 06-15-2005, 06:37 PM   #3
freddycodes
Master of Nothing
 
Join Date: Dec 2002
Location: San Diego, CA
Posts: 2,468
Default

Cause you aren't aliasing the result of DATE_FORMAT

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

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

freddycodes is offline   Reply With Quote
Old 06-15-2005, 06:50 PM   #4
Paul Ferrie
Scottish and mad fer it!!
 
Paul Ferrie's Avatar
 
Join Date: Jun 2002
Location: uk/scotland/glasgow
Posts: 3,606
Default

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

thanks
Paul
__________________
.:To me AS is like LEGO, Only for the big Kids :.
- InnovativeDesigns - Scotflash - About me
Paul Ferrie is offline   Reply With Quote
Old 06-15-2005, 07:03 PM   #5
Paul Ferrie
Scottish and mad fer it!!
 
Paul Ferrie's Avatar
 
Join Date: Jun 2002
Location: uk/scotland/glasgow
Posts: 3,606
Default

I have never encoutered a problem using loops with php.
Anyways...
next problem, returning how many visitors for each date returned.
I am trying
PHP Code:
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
__________________
.:To me AS is like LEGO, Only for the big Kids :.
- InnovativeDesigns - Scotflash - About me
Paul Ferrie is offline   Reply With Quote
Old 06-15-2005, 08:31 PM   #6
freddycodes
Master of Nothing
 
Join Date: Dec 2002
Location: San Diego, CA
Posts: 2,468
Default

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.
Code:
SELECT count(sysDate) as DateCount, DATE_FORMAT( sysDate, '%Y-%m-%d' ) as FormattedDate
FROM scStats 
GROUP BY FormattedDate
ORDER BY FormattedDate DESC
PHP Code:
$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

freddycodes is offline   Reply With Quote
Old 06-16-2005, 12:08 PM   #7
Paul Ferrie
Scottish and mad fer it!!
 
Paul Ferrie's Avatar
 
Join Date: Jun 2002
Location: uk/scotland/glasgow
Posts: 3,606
Default

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

Sooooo much cleaner

Paul
__________________
.:To me AS is like LEGO, Only for the big Kids :.
- InnovativeDesigns - Scotflash - About me
Paul Ferrie is offline   Reply With Quote
Old 06-16-2005, 03:23 PM   #8
freddycodes
Master of Nothing
 
Join Date: Dec 2002
Location: San Diego, CA
Posts: 2,468
Default

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.
freddycodes is offline   Reply With Quote
Reply


Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT. The time now is 06:15 AM.

///
Follow actionscriptorg on Twitter

 


Powered by vBulletin® Version 3.8.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Ad Management plugin by RedTyger
Copyright 2000-2013 ActionScript.org. All Rights Reserved.
Your use of this site is subject to our Privacy Policy and Terms of Use.