How to create analytical chart

[vizz]

I need to create chart for log

Database fields are,

*** host
* pageurl
* VisitPage
* VisitorIP
* VisitDate
* VisitorTimeZone
* VisitTime
* browser
* operatingsystem
* referer**

I need chart for Toatl Visits, Uniquie Visits, Impressions, Online Users, Pageviews,
%New Visits, % Uniquie Visits, %New Visitor, %Returning Visitor

There is problem with VisitTime and VisitorTimeZone it is not showing accurate values

Here is log.php

<?php
$page_name = $_SERVER['PHP_SELF']; // PAGE NAME 
$parse_page_name = basename ($page_name); // PARSE THE EXACT PAGE NAME
// Get the URL
$host = $_SERVER['SERVER_NAME'];
// Get the IP Address
$ipAddress =$_SERVER['REMOTE_ADDR'];
//format the URL
//$visitURL = $url.$scriptName;
$today = date("j, F, Y");  
$tmzone=date_default_timezone_get();
$now=strftime("%I:%M:%S");
$stz=gmstrftime("%Z");
$visitortimezone=("$tmzone, ($stz)");
function getBrowser() 
{ 
    $u_agent = $_SERVER['HTTP_USER_AGENT']; 
    $bname = 'Unknown';
    $platform = 'Unknown';
    $version= "";
    //First get the platform?
    if (preg_match('/linux/i', $u_agent)) {
        $platform = 'linux';
    }
    elseif (preg_match('/macintosh|mac os x/i', $u_agent)) {
        $platform = 'mac';
    }
    elseif (preg_match('/windows|win32/i', $u_agent)) {
        $platform = 'windows';
    }
    // Next get the name of the useragent yes seperately and for good reason
    if(preg_match('/MSIE/i',$u_agent) && !preg_match('/Opera/i',$u_agent)) 
    { 
        $bname = 'Internet Explorer'; 
        $ub = "MSIE"; 
    } 
    elseif(preg_match('/Firefox/i',$u_agent)) 
    { 
        $bname = 'Mozilla Firefox'; 
        $ub = "Firefox"; 
    } 
    elseif(preg_match('/Chrome/i',$u_agent)) 
    { 
        $bname = 'Google Chrome'; 
        $ub = "Chrome"; 
    } 
    elseif(preg_match('/Safari/i',$u_agent)) 
    { 
        $bname = 'Apple Safari'; 
        $ub = "Safari"; 
    } 
    elseif(preg_match('/Opera/i',$u_agent)) 
    { 
        $bname = 'Opera'; 
        $ub = "Opera"; 
    } 
    elseif(preg_match('/Netscape/i',$u_agent)) 
    { 
        $bname = 'Netscape'; 
        $ub = "Netscape"; 
    } 
    // finally get the correct version number
    $known = array('Version', $ub, 'other');
    $pattern = '#(?<browser>' . join('|', $known) .
    ')[/ ]+(?<version>[0-9.|a-zA-Z.]*)#';
    if (!preg_match_all($pattern, $u_agent, $matches)) {
        // we have no matching number just continue
    }
    // see how many we have
    $i = count($matches['browser']);
    if ($i != 1) {
        //we will have two since we are not using 'other' argument yet
        //see if version is before or after the name
        if (strripos($u_agent,"Version") < strripos($u_agent,$ub)){
            $version= $matches['version'][0];
        }
        else {
            $version= $matches['version'][1];
        }
    }
    else {
        $version= $matches['version'][0];
    }
    // check if we have a number
    if ($version==null || $version=="") {$version="?";}
    return array(
        'userAgent' => $u_agent,
        'name'      => $bname,
        'version'   => $version,
        'platform'  => $platform,
        'pattern'    => $pattern
    );
}
$ua=getBrowser();
$browser="$ua[name] $ua[version]";
$os="$ua[platform]";
$referer = $_SERVER['HTTP_REFERER'];

$sql="INSERT INTO visitslog (host,pageurl,VisitPage,VisitorIP,VisitDate,VisitorTimeZone,VisitTime,browser,operatingsystem,referer)       VALUES('$host','$page_name','$parse_page_name','$ipAddress','$today','$visitortimezone','$now','$browser','$os','$referer')";
$result=mysql_query($sql);
?>

Please help

[broj1]

Where do you do any calculations for the chart especially for the values in question? If you want to know what is good library for drawing charts I highly recommend jQplot.

[vizz]

thanks

I need help for calculations too

[broj1]

What exactly would you like to show for VisitTime and VisitorTimeZone? I can guess the timezones can be counted something like:

$tzQuery = 'SELECT VisitorTimeZone, COUNT(*) AS tz_count 
            FROM visitslog GROUP BY VisitorTimeZone';

[mattster]

For an old project, I used this
JPowered

Which does the graph work, all you have to do is echo some information into the script, it does all of the maths, and it works really well and easily

[vizz]

$visitortimezone=("$tmzone, ($stz)"); and $now=strftime("%I:%M:%S");
is not working well

[broj1]

You are not saying much :-). Can you please explain what and how is not working well (what you expect v.s. what comes out, error messages).

$visitortimezone=("$tmzone, ($stz)");

Seems like a function name is missing here? What you intended to do?

[vizz]

visitortimezone give Europe/Berlin, (India Standard Time) for localhost

VisitTime give 05:29:37 for 10 AM IST for localhost

I need visitors exact time related info.

Also please help to find, Uniquie Visits, Impressions, Online Users, Pageviews,
%New Visits, % Uniquie Visits, %New Visitor, %Returning Visitor

[broj1]

visitortimezone give Europe/Berlin, (India Standard Time) for localhost
VisitTime give 05:29:37 for 10 AM IST for localhost

Will this link help?

http://stackoverflow.com/questions/3905193/convert-time-and-date-from-one-time-zone-to-another-in-php

[AleMonteiro]

I used and liked very much this JS chart API: http://www.highcharts.com/

Hope it helps.

[diafol]

Just a note on the use of DateTime - following on from broj1's link (nice one broj +1). IMO, it's the killer object for dealing with almost every date issue. However, to my horror, my beautifully crafted DateTime extension class failed to work as expected because I was using a VC6 php build as opposed to the VC9. So, lesson learned - if you want to use DateTime (and you should) - do check which version and build of php you're running - it prompted me to update php.

I used and liked very much this JS chart API: http://www.highcharts.com/

The high* JS series of scripts are fantastic. A big me too, on that (+1).

[broj1]

my beautifully crafted DateTime extension class failed to work as expected because I was using a VC6 php build as opposed to the VC9

Took me some time to figure out what VC stands for :-) I have never used VS and rarely use Windows these days. Thanks for sharing this info.

[diafol]

Thanks for sharing this info.

No problem. This is relatively simple to sort out on your own PC, but ensuring latest php version on your shared hosting account, may be a different matter! Could all end in tears, especially if you're developing a solution for a client, paying or otherwise! But as mentioned, this particular problem seems only to affect Windows.

[vizz]

Need help to detect
Uniquie Visits, Impressions, Online Users, Pageviews,Returning Visitor

[broj1]

Just a few ideas. Can't say they are 100% OK, since I do not know much about your app. Do some tests.

// Unique visits by host
SELECT COUNT(*) AS hostcount, host FROM log GROUP BY host

// Unique visits by VisitorIP
SELECT COUNT(*) AS visitoripcount, VisitorIP FROM log GROUP BY VisitorIP 

// Pageviews
SELECT COUNT(*) AS pagevievcount, VisitPage FROM log GROUP BY VisitPage

// Returning visitors by VisitorIP
SELECT COUNT(*) AS returningcount, VisitorIP**Bold Text Here**  FROM log GROUP BY VisitorIP HAVING COUNT(*) > 1

Impressions? What is this?

[broj1]

For online users you have to do some guessing. You do not log the logout time so you can't compare it with login time. And this method is not reliable since the user might just close the browser. You can also check sessions or write a fancy javascript such as this.