In my earlier post I shared the code to collect the website visitors’ device technical specifications. That data is a very helpful in web design as it allows us to target most popular screen sizes, browser types, devices etc, allowing majority of visitors to have best possible user experience without going for a complex and time-consuming responsive layouts.

Today I am posting a code that creates a simple info-graphic visualising most important data – device type, browser, screen size and resolution.

Once you have your MySQL database up and running for some time (I think around 20–30k views should be a reasonable amount), we need to make sense from a raw data collected.

I created those simple queries and php loops to filter and organise the data and then employed Google Charts to do the visualisation work.

All you need to do is create an empty php document, copy and paste the code below.

<?php
// fill with details of your db
$dbhost = "";
$dbuser = "";
$dbpass = "";
$dbname = "";

$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
if(! $conn ) die('Could not connect: ' . mysql_error());

function get_client_ip() {
    $a = '';
    if ($_SERVER['HTTP_CLIENT_IP']) $a = $_SERVER['HTTP_CLIENT_IP'];
    else if($_SERVER['HTTP_X_FORWARDED_FOR']) $a = $_SERVER['HTTP_X_FORWARDED_FOR'];
    else if($_SERVER['HTTP_X_FORWARDED']) $a = $_SERVER['HTTP_X_FORWARDED'];
    else if($_SERVER['HTTP_FORWARDED_FOR']) $a = $_SERVER['HTTP_FORWARDED_FOR'];
    else if($_SERVER['HTTP_FORWARDED']) $a = $_SERVER['HTTP_FORWARDED'];
    else if($_SERVER['REMOTE_ADDR']) $a = $_SERVER['REMOTE_ADDR'];
    else $a = 'UNKNOWN';
    return $a;
}

// comma separated list of ip's
$ignore_ip = "

"
;

$query_1 = mysqli_query($conn,"
    SELECT client,COUNT(*)
    AS count
    FROM hitdata
    WHERE address_ip NOT
    IN (
    "
.$ignore_ip."
    )
    GROUP BY client
    ORDER BY count DESC
"
);

while($row = mysqli_fetch_array($query_1)) {
  $arr[] = array( $row['client'],  $row['count'] );
}


//  / / / / / / / / / / / / / /

// Browser & OS

//  / / / / / / / / / / / / / /


$search = array(
    'Windows',
    'Macintosh',
    'iPhone',
    'iPad',
    'Android',
);

$total = 0;
$total_known = 0;
$chart_str1 = '';

foreach( $search as $sought ){
    $count = 0;
    foreach ($arr as $item) {
        if ( strpos($item[0],$sought) !== false ) $count = $count + $item[1];
    }
    $chart_str1 .= "['".$sought . "', " . $count . "],"; // string inserted in Google chart
    $total_known = $total_known + $count;
}
foreach( $arr as $i ){
    $total = $total + $i[1];
}

$chart_str1 .= "['other'," . ($total - $total_known) . "]"; // string inserted in Google chart



//  / / / / / / / / / / / / / /

//    IE versions breakdown (if anyone cares)

//  / / / / / / / / / / / / / /

/*

$search2 = array(
    'rv:11',
    'MSIE 10',
    'MSIE 9',
    'MSIE 8',
    'MSIE 7',
    'MSIE 6',
    'MSIE 5',
);

$chart_str2 = '';

foreach( $search2 as $sought ){
    $count = 0;
    foreach ($arr as $item) {
        if ( strpos($item[0],$sought) !== false ) $count = $count + $item[1];
    }
    $chart_str2 .= "['".$sought . "', " . $count . "],";
}

*/


//  / / / / / / / / / / / / / / /

//   Desktop &amp; laptop browsers

//  / / / / / / / / / / / / / / /


$search3 = array(
    'rv:11',
    'MSIE 10',
    'MSIE 9',
    'MSIE 8',
    'MSIE 7',
    'MSIE 6',
    'MSIE 5',
    'Chrome',
    'Firefox',
    'Safari',
);

$chart_data = array();
$total = 0;


foreach ($arr as $item) {
    if( strpos($item[0],'Windows') !== false || strpos($item[0],'Macintosh') !== false ){
        $total = $total + $item[1];
        foreach( $search3 as $sought ){
            if ( strpos($item[0],$sought) !== false )
                $chart_data[$sought] = $chart_data[$sought] + $item[1];
        }
    }
}


$chart_str3 =  "['IE 9 and higher', " . ($chart_data['rv:11'] + $chart_data['MSIE 10'] + $chart_data['MSIE 9']) . "],";
$chart_str3 .=  "['IE 8 and lower', " . ($chart_data['MSIE 8'] + $chart_data['MSIE 7'] + $chart_data['MSIE 6'] + $chart_data['MSIE 5']) . "],";
$chart_str3 .= "['Chrome', " . $chart_data['Chrome'] . "],";
$chart_str3 .= "['Firefox', " . $chart_data['Firefox'] . "],";
$chart_str3 .= "['Safari', " . ($chart_data['Safari'] - $chart_data['Chrome']) . "],"; // need to substract as Chrome identifies itself as Safari compatibile
$chart_str3 .= "['other', " . ($total - ( array_sum( $chart_data ) - $chart_data['Chrome'] )) . "]"; // same as above


//  / / / / / / / / / / / / / / /

//     Screen pixel density

//  / / / / / / / / / / / / / / /

unset( $arr );
unset( $chart_data );

$query_2 = mysqli_query($conn,"
    SELECT resolution,COUNT(*)
    AS count
    FROM hitdata
    WHERE address_ip NOT
    IN (
    "
.$ignore_ip."
    )
    GROUP BY resolution
    ORDER BY resolution DESC
"
);


while($row = mysqli_fetch_array($query_2)) {
    $arr[] = array( $row['resolution'],  $row['count'] );
}

foreach ($arr as $item) {
    switch ($item[0]){
        case( $item[0] >= 2 ):
            $chart_data['x2 + (retina display)'] = $chart_data['x2 + (retina display)'] + $item[1];
            break;
        case( $item[0] >= 1.5 ):
            $chart_data['x1.5 - x2'] = $chart_data['x1.5 - x2'] + $item[1];
            break;
        case( $item[0] > 1 ):
            $chart_data['x1 - x1.5'] = $chart_data['x1 - x1.5'] + $item[1];
            break;
        default:
            $chart_data['x1 (standard display)'] = $chart_data['x1 (standard display)'] + $item[1];
    }
}

$chart_str4 ='';
foreach ($chart_data as $key => $value) {
    $chart_str4 .= "['". $key . "'," . $value . "],";
}

//  / / / / / / / / / / / / / / / / / /

//   Screen pixel size (resolution)

//  / / / / / / / / / / / / / / / / / /


unset( $arr );
unset( $chart_data );

$query_3 = mysqli_query($conn,"
    SELECT width,COUNT(*)
    AS count
    FROM hitdata
    WHERE address_ip NOT
    IN (
    "
.$ignore_ip."
    )
    GROUP BY width
    ORDER BY width ASC
"
);

while($row = mysqli_fetch_array($query_3)) {
    $arr['w'][] = array( $row['width'],  $row['count'] );
}

$query_4 = mysqli_query($conn,"
    SELECT height,COUNT(*)
    AS count
    FROM hitdata
    WHERE address_ip NOT
    IN (
    "
.$ignore_ip."
    )
    GROUP BY height
    ORDER BY height ASC
"
);


while($row = mysqli_fetch_array($query_4)) {
    $arr['h'][] = array( $row['height'],  $row['count'] );
}


$min = 250;
$max = 2000;
$incr = 15; // increase to have a rougher graph (some values might cause Google Charts to crash, experiment)

$i = $min;
foreach ($arr['w'] as $item) {
    if ( $item[0] < $min  ) continue;
    if ( $item[0] > $i ) $i = $i + $incr;
    if ( $item[0] > $max  ) break;
    $chart_data[$i]['w'] = $chart_data[$i]['w'] + $item[1];
}

$i = $min;
foreach ($arr['h'] as $item) {
    if ( $item[0] < $min ) continue;
    if ( $item[0] > $i ) $i = $i + $incr;
    if ( $item[0] > $max  ) break;
    $chart_data[$i]['h'] = $chart_data[$i]['h'] + $item[1];
}

foreach( $chart_data as $size => $count ){
    $chart_str7 .= "['". $size . "'," . $count['w'] . "," . $count['h'] . "],";
}              

?>
<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://mattr.co.ukwww.google.com/jsapi"></script>
    <script type="text/javascript">

        // Load the Visualization API and the piechart package.
        google.load('visualization', '1.0', {'packages':['corechart']});

        // Set a callback to run when the Google Visualization API is loaded.
        google.setOnLoadCallback(drawChart);
        // Callback that creates and populates a data table,
        // instantiates the pie chart, passes in the data and
        // draws it.


        function drawChart() {

            // Chart 1

            // Create the data table 1.
            var data = new google.visualization.DataTable();
            data.addColumn('string', 'Topping');
            data.addColumn('number', 'Slices');
            data.addRows([ <? echo $chart_str1 ?> ]);

            // Set chart options 1
            var options = {'title':'Device / OS',
                         'width':700,
                         'height':400,
                         'is3D':true
                         };

            // Instantiate and draw our chart 1 , passing in some options.
            var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
            chart.draw(data, options);


            // Chart 3


            var data3 = new google.visualization.DataTable();
            data3.addColumn('string', 'Topping');
            data3.addColumn('number', 'Slices');
            data3.addRows([ <? echo $chart_str3 ?> ]);

            var options3 = {'title':'Desktop &amp; laptop browser (~60% of all hits)',
                         'width':700,
                         'height':400,
                         'is3D':true
                         };

            var chart3 = new google.visualization.PieChart(document.getElementById('chart_div3'));
            chart3.draw(data3, options3);


            // Chart 4


            var data4 = new google.visualization.DataTable();
            data4.addColumn('string', 'Topping');
            data4.addColumn('number', 'Slices');
            data4.addRows([ <? echo $chart_str4 ?> ]);

            var options4 = {'title':'Display pixel density',
                         'width':600,
                         'height':300,
                         'is3D':true
                         };

            var chart4 = new google.visualization.PieChart(document.getElementById('chart_div4'));
            chart4.draw(data4, options4);


            // Chart 7


            var data7 = google.visualization.arrayToDataTable([
                ['Size (px)', 'Width hits', 'Height hits'],
                <? echo $chart_str7; ?>
            ]);

            var options7 = {
                title: 'Display widths &amp; heights frequency',
                hAxis: {title: 'Size (px)',  titleTextStyle: {color: '#333'}, },
                vAxis: {minValue: 0, viewWindowMode: 'maximized' },
                series: {
                    0: {type: "steppedArea"},
                    1: {type: "area"}
                }
            };

            var chart7 = new google.visualization.ComboChart(document.getElementById('chart_div7'));
                chart7.draw(data7, options7);

        }
    </script>

<style type="text/css">
    div.chart{

        float:left;
    }
</style>    

  </head>

  <body>
<h1>website visitors user-agent statistics</h1>
<p><small>your ip: <? echo get_client_ip(); ?></small></p>

<div class="chart" id="chart_div" style="width:800; height:500"></div>
<div class="chart" id="chart_div3" style="width:700; height:300"></div>
<div class="chart" id="chart_div4" style="width:600; height:300"></div>

<br style="clear:both" />

<div id="chart_div7" style="width: 99%; height: 500px;"></div>

    </body>
    </html>

Leave a comment