Jump to content

Analytics System slow


Fratozzi

Recommended Posts

Hi everyone, I have a data analysis system which, however, takes too long to insert data into the db. Can you help me understand where I went wrong? thank you

DB MYSQL:

1 analytics_number bigint(20) AUTO_INCREMENT
2 visitor_visitor_id varchar(50) utf8mb4_general_ci
3 visitor_session_id varchar(50) utf8mb4_general_ci
4 visitor_pageview_id varchar(50) utf8mb4_general_ci
5 visitor_visitor_sessions bigint(20)
6 visitor_visitor_session bigint(20)
7 visitor_visitor_pageviews bigint(20)
8 visitor_visitor_pageview bigint(20)
9 visitor_visitor_time bigint(20)
10 visitor_visitor_pages text utf8mb4_general_ci
11 visitor_visitor_first_page text utf8mb4_general_ci
12 visitor_visitor_last_page varchar(50) utf8mb4_general_ci
13 visitor_session_pageviews bigint(20)
14 visitor_session_pageview bigint(20)
15 visitor_session_time bigint(20)
16 visitor_session_pages text utf8mb4_general_ci
17 visitor_session_landingpage varchar(500) utf8mb4_general_ci
18 visitor_session_exitpage varchar(500) utf8mb4_general_ci
19 visitor_session_first_page varchar(50) utf8mb4_general_ci
20 visitor_session_last_page varchar(50) utf8mb4_general_ci
21 visitor_enter_timestamp varchar(50) utf8mb4_general_ci
22 visitor_enter_id varchar(50) utf8mb4_general_ci
23 visitor_enter_year int(11)
24 visitor_enter_month int(11)
25 visitor_enter_weekday int(11)
26 visitor_enter_day int(11)
27 visitor_enter_hour int(11)
28 visitor_enter_minute int(11)
29 visitor_leave_timestamp varchar(50) utf8mb4_general_ci
30 visitor_leave_id varchar(50) utf8mb4_general_ci
31 visitor_country varchar(50) utf8mb4_general_ci
32 visitor_country_code varchar(50) utf8mb4_general_ci
33 visitor_region varchar(100) utf8mb4_general_ci
34 visitor_region_code varchar(50) utf8mb4_general_ci
35 visitor_city varchar(100) utf8mb4_general_ci
36 visitor_zip int(11)
37 visitor_lat varchar(100) utf8mb4_general_ci
38 visitor_lon varchar(100) utf8mb4_general_ci
39 visitor_timezone varchar(100) utf8mb4_general_ci
40 visitor_isp varchar(100) utf8mb4_general_ci
41 visitor_language varchar(50) utf8mb4_general_ci
42 visitor_device varchar(100) utf8mb4_general_ci
43 visitor_device_brand varchar(100) utf8mb4_general_ci
44 visitor_device_model varchar(100) utf8mb4_general_ci
45 visitor_os varchar(100) utf8mb4_general_ci
46 visitor_os_version varchar(100) utf8mb4_general_ci
47 visitor_browser varchar(100) utf8mb4_general_ci
48 visitor_browser_version varchar(100) utf8mb4_general_ci
49 visitor_resolution varchar(50) utf8mb4_general_ci
50 visitor_viewport varchar(50) utf8mb4_general_ci
51 visitor_document varchar(50) utf8mb4_general_ci
52 visitor_referrer_url varchar(100) utf8mb4_general_ci
53 visitor_referrer_domain varchar(100) utf8mb4_general_ci
54 visitor_referrer_type varchar(100) utf8mb4_general_ci
55 visitor_referrer_name varchar(100) utf8mb4_general_ci
56 visitor_url varchar(500) utf8mb4_general_ci
57 visitor_domain varchar(100) utf8mb4_general_ci
58 visitor_page_path varchar(500) utf8mb4_general_ci
59 visitor_pageview_time bigint(20)
60 visitor_leave_url varchar(50) utf8mb4_general_ci
61 visitor_leave_domain varchar(100) utf8mb4_general_ci
62 visitor_leave_page varchar(50) utf8mb4_general_ci
63 visitor_leave_type varchar(100) utf8mb4_general_ci
64 visitor_leave_name varchar(100) utf8mb4_general_ci
65 visitor_pageview_update varchar(100) utf8mb4_general_ci

---------------------------------------------------------------------------

CLASS PHP

Analytics.php

class analytics {

    public $search_sites;
    public $social_sites;
    private $_db;
    
    function __construct($db){
        $this->search_sites = array("google" => "google", "goo" => "google", "bing" => "bing", "yahoo" => "yahoo", "baidu" => "baidu", "ask" => "ask", "aol" => "aol", "wow" => "wow", "webcrawler" => "webcrawler", "mywebsearch" => "mywebsearch", "infospace" => "infospace", "duckduckgo" => "duckduckgo", "yandex" => "yandex");
        $this->social_sites = array("facebook" => "facebook", "fb" => "facebook", "twitter" => "twitter", "t.co" => "twitter", "youtube" => "youtube", "instagram" => "instagram", "snap" => "snapchat", "snapchat" => "snapchat", "reddit" => "reddit", "linkedin" => "linkedin", "xing" => "xing", "pinterest" => "pinterest", "tumblr" => "tumblr", "vine" => "vine", "meetup" => "meetup", "quora" => "quora");
        $this->_db = $db;
    }

    public function get_analytics_enter($visitor_ip, $visitor_visitor_id, $visitor_session_id, $visitor_pageview_id, $visitor_referrer, $visitor_url, $visitor_resolution, $visitor_viewport, $visitor_document){

        try{

            $GET_array = array("visitor_ip" => $visitor_ip, "visitor_visitor_id" => $visitor_visitor_id, "visitor_session_id" => $visitor_session_id, "visitor_pageview_id" => $visitor_pageview_id, "visitor_referrer" => $visitor_referrer, "visitor_url" => $visitor_url, "visitor_resolution" => $visitor_resolution, "visitor_viewport" => $visitor_viewport, "visitor_document" => $visitor_document);
             
            require_once('analytics-db.php');
            
            $analytics = array();

            $query = "SELECT * FROM analytics WHERE visitor_visitor_id = '".$visitor_visitor_id."'";
            $visitor_data = $this->_db->query($query)->fetchAll();

            $visitor_ip_info = get_data_visitor($visitor_ip);

            $visitor_visitor_pageview = 0;
            $visitor_session_pageview = 0;

            $session_buffer = array();
            $visitor_visitor_session = 0;

            $visitor_visitor_pages = array();
            $visitor_session_pages = array();

            $analytics["visitor_session_landingpage"] = NULL;
            $analytics["visitor_session_exitpage"] = NULL;

            foreach ($visitor_data as $visitor) {
                $visitor_visitor_pageview += 1;

                if (!in_array($visitor["visitor_page_path"], $visitor_visitor_pages)) {
                    array_push($visitor_visitor_pages, $visitor["visitor_page_path"]);
                }

                if ($visitor["visitor_session_id"] == $visitor_session_id) {
                    $visitor_session_pageview += 1;
                    $analytics["visitor_session_exitpage"] = $visitor["visitor_page_path"];

                    if ($visitor_session_pageview == 1) {
                        $analytics["visitor_session_landingpage"] = $visitor["visitor_page_path"];
                    }

                    if (!in_array($visitor["visitor_page_path"],$visitor_session_pages)) {
                        array_push($visitor_session_pages,$visitor["visitor_page_path"]);
                    }
                }

                if (!in_array($visitor["visitor_session_id"],$session_buffer)) {
                    array_push($session_buffer,$visitor["visitor_session_id"]);
                    $visitor_visitor_session += 1;
                }
            }

            if (!in_array($visitor_session_id,$session_buffer)) {
                $visitor_visitor_session += 1;
            }

            foreach($GET_array as $key => $value) {     
                if (in_array($key, $database_structure["analytics"])) {    
                    $analytics[$key] = $value;
                }
            }  

            $analytics["visitor_visitor_sessions"] = $visitor_visitor_session;
            $analytics["visitor_visitor_session"] = $visitor_visitor_session;

            $analytics["visitor_visitor_pageviews"] = $visitor_visitor_pageview + 1;
            $analytics["visitor_visitor_pageview"] = $visitor_visitor_pageview + 1;

            $analytics["visitor_visitor_time"] = "0";

            $analytics["visitor_session_pageviews"] = $visitor_session_pageview + 1;
            $analytics["visitor_session_pageview"] = $visitor_session_pageview + 1;

            $analytics["visitor_enter_timestamp"] = date("d.m.Y, H:i:s");
            $analytics["visitor_enter_id"] = date("YmdHis");
            $analytics["visitor_enter_year"] = date("Y");
            $analytics["visitor_enter_month"] = date("m");
            $analytics["visitor_enter_weekday"] = date("w");
            $analytics["visitor_enter_day"] = date("d");
            $analytics["visitor_enter_hour"] = date("H");
            $analytics["visitor_enter_minute"] = date("i");

            $analytics["visitor_leave_timestamp"] = date("d.m.Y, H:i:s");
            $analytics["visitor_leave_id"] = date("YmdHis");

            $analytics["visitor_pageview_time"] = "0";

            $analytics["visitor_country"] = $visitor_ip_info["country"];
            $analytics["visitor_country_code"] = $visitor_ip_info["countryCode"];
            $analytics["visitor_region"] = $visitor_ip_info["regionName"];
            $analytics["visitor_region_code"] = $visitor_ip_info["region"];
            $analytics["visitor_city"] = $visitor_ip_info["city"];
            $analytics["visitor_zip"] = $visitor_ip_info["zip"];
            $analytics["visitor_lat"] = $visitor_ip_info["lat"];
            $analytics["visitor_lon"] = $visitor_ip_info["lon"];
            $analytics["visitor_timezone"] = $visitor_ip_info["timezone"];
            $analytics["visitor_isp"] = $visitor_ip_info["isp"];
            $analytics["visitor_language"] = substr($_SERVER["HTTP_ACCEPT_LANGUAGE"], 0, 2);

            $analytics["visitor_referrer_url"] = $visitor_referrer;
            $analytics["visitor_referrer_domain"] = $this->get_domain($visitor_referrer);
            $analytics["visitor_referrer_type"] = $this->visitor_referrer_type($this->get_domain($visitor_referrer));
            $analytics["visitor_referrer_name"] = $this->visitor_referrer_name($this->get_domain($visitor_referrer));

            $analytics["visitor_domain"] = $this->get_domain($visitor_url);
            if ($this->get_page($visitor_url) != "" && $this->get_page($visitor_url) != " " && $this->get_page($visitor_url) != " ") {
                $analytics["visitor_page_path"] = $this->get_page($visitor_url);
            }else{
                $analytics["visitor_page_path"] = "/";
            }

            if (!in_array($analytics["visitor_page_path"], $visitor_visitor_pages)) {
                array_push($visitor_visitor_pages,$analytics["visitor_page_path"]);
            }

            if (!in_array($analytics["visitor_page_path"], $visitor_session_pages)) {
                array_push($visitor_session_pages,$analytics["visitor_page_path"]);
            }

            $analytics["visitor_visitor_pages"] = json_encode($visitor_visitor_pages);
            $analytics["visitor_session_pages"] = json_encode($visitor_session_pages);


            if ($analytics["visitor_visitor_pageview"] == 1) {
                $analytics["visitor_visitor_first_page"] = "true";
            }

            if ($analytics["visitor_session_pageview"] == 1) {
                $analytics["visitor_session_first_page"] = "true";
            }

            $sql_1_1 = "UPDATE analytics SET " . "visitor_visitor_last_page" . "='" . "" . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";
            $sql_1_2 = "UPDATE analytics SET " . "visitor_session_last_page" . "='" . "" . "' WHERE " . "visitor_session_id" . "='" . $visitor_session_id . "'";

            $sql_1 = $sql_1_1."; ".$sql_1_2.";";
            $this->_db->exec($sql_1);

            $analytics["visitor_visitor_last_page"] = "true";
            $analytics["visitor_session_last_page"] = "true";

            $analytics["visitor_device"] = visitor_device();
            $analytics["visitor_device_brand"] = visitor_device();
            $analytics["visitor_device_model"] = visitor_device();
            $analytics["visitor_os"] = visitor_os();
            $analytics["visitor_os_version"] = visitor_os();
            $analytics["visitor_browser"] = visitor_browser();
            $analytics["visitor_browser_version"] = visitor_browser();

            $analytics["visitor_pageview_update"] = date("YmdHis");

            $sql_2_1 = "INSERT INTO analytics (";
            foreach ($analytics as $key => $value) {$sql_2_1 .= $key . ",";}
            $sql_2_1 = rtrim($sql_2_1,",") . ") VALUES (";
            foreach ($analytics as $key => $value) {$sql_2_1 .= "'" . $value . "',";}
            $sql_2_1 = rtrim($sql_2_1,",") . ")";

            $this->_db->exec($sql_2_1);

            $sql_2_2 = "UPDATE analytics SET " . "visitor_visitor_sessions" . "='" . $analytics["visitor_visitor_sessions"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";

            $sql_2_3 = "UPDATE analytics SET " . "visitor_visitor_pageviews" . "='" . $analytics["visitor_visitor_pageviews"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";

            $sql_2_4 = "UPDATE analytics SET " . "visitor_visitor_pages" . "='" . $analytics["visitor_visitor_pages"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";

            $sql_2_5 = "UPDATE analytics SET " . "visitor_session_pageviews" . "='" . $analytics["visitor_session_pageviews"] . "' WHERE " . "visitor_session_id" . "='" . $visitor_session_id."'";

            $sql_2_6 = "UPDATE analytics SET " . "visitor_session_pages" . "='" . $analytics["visitor_session_pages"] . "' WHERE " . "visitor_session_id" . "='" . $visitor_session_id."'";

            $sql_2_7 = "UPDATE analytics SET " . "visitor_session_landingpage" . "='" . $analytics["visitor_session_landingpage"] . "' WHERE " . "visitor_session_id" . "='" . $visitor_session_id."'";

            $sql_2_8 = "UPDATE analytics SET " . "visitor_session_exitpage" . "='" . $analytics["visitor_session_exitpage"] . "' WHERE " . "visitor_session_id" . "='" . $visitor_session_id."'";

            $sql_2 = $sql_2_2.";".$sql_2_3.";".$sql_2_4.";".$sql_2_5.";".$sql_2_6.";".$sql_2_7.";".$sql_2_8.";";
            $this->_db->exec($sql_2);

        } catch(PDOException $e) {
        }
    }

    public function get_analytics_update($visitor_visitor_id, $visitor_session_id, $visitor_pageview_id, $visitor_pageview_time, $visitor_leave_url){

        try{
            $GET_array = array("visitor_visitor_id" => $visitor_visitor_id, "visitor_session_id" => $visitor_session_id, "visitor_pageview_id" => $visitor_pageview_id, "visitor_pageview_time" => $visitor_pageview_time, "visitor_leave_url" => $visitor_leave_url);
        
            require_once('analytics-db.php');
            
            $query = "SELECT MAX(" . "visitor_visitor_time" . ") FROM analytics WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "' ORDER BY " . "visitor_enter_id" . " DESC LIMIT 1";
            
            $visitor_visitor_time = $this->_db->query($query)->fetchColumn();

            $query = "SELECT MAX(" . "visitor_session_time" . ") FROM analytics WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "' AND " . "visitor_session_id" . "='" . $visitor_session_id . "' ORDER BY " . "visitor_enter_id" . " DESC LIMIT 1";

            $visitor_session_time = $this->_db->query($query)->fetchColumn();

            $query = "SELECT MAX(" . "visitor_pageview_time" . ") FROM analytics WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "' AND " . "visitor_session_id" . "='" . $visitor_session_id . "' AND " . "visitor_pageview_id" . "='" . $visitor_pageview_id . "' ORDER BY " . "visitor_enter_id" . " DESC LIMIT 1";
                
            $visitor_pageview_time = $this->_db->query($query)->fetchColumn();

            $analytics = array();

            foreach($GET_array as $key => $value) {
                if ($key != "visitor_visitor_id" && $key != "visitor_session_id" && $key != "visitor_pageview_id" && in_array($key, $database_structure["analytics"])) {    
                    if(is_array($value)) {
                        $analytics[$key] = json_encode($value);
                    }else{
                        $analytics[$key] = $value;
                    }
                }
            }  

            $analytics["visitor_leave_timestamp"] = date("d.m.Y, H:i:s");
            $analytics["visitor_leave_id"] = date("YmdHis");

            $analytics["visitor_visitor_time"] = $visitor_visitor_time + ($visitor_pageview_time - $visitor_pageview_time);
            $analytics["visitor_session_time"] = $visitor_session_time + ($visitor_pageview_time - $visitor_pageview_time);

            $analytics["visitor_pageview_update"] = date("YmdHis");

            if (isset($visitor_leave_url)) {
                $analytics["visitor_leave_domain"] = $this->get_domain($visitor_leave_url);
                $analytics["visitor_leave_page"] = $this->get_page($visitor_leave_url);
                $analytics["visitor_leave_type"] = $this->visitor_leave_type($this->get_domain($visitor_leave_url));
                $analytics["visitor_leave_name"] = $this->visitor_leave_name($this->get_domain($visitor_leave_url));
            }

            $sql_1_1 = "UPDATE analytics SET ";
            foreach ($analytics as $key => $value) {$sql_1_1 .= $key . "='" . $value . "',";}
            $sql_1_1 = rtrim($sql_1_1,",");
            $sql_1_1 .= " WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "' AND " . "visitor_session_id" . "='" . $visitor_session_id . "' AND " . "visitor_pageview_id" . "='" . $visitor_pageview_id . "'";
        
            $sql_1_2 = "UPDATE analytics SET " . "visitor_visitor_time" . "='" . $analytics["visitor_visitor_time"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";
        
            $sql_1_3 = "UPDATE analytics SET " . "visitor_session_time" . "='" . $analytics["visitor_session_time"] . "' WHERE " . "visitor_session_id" . "='" . $visitor_session_id . "'";
            
            $sql_1 = $sql_1_1."; ".$sql_1_2."; ".$sql_1_3.";";
            $this->_db->exec($sql_1);

        } catch(PDOException $e) {
        }
    }
        
    function get_domain($url) {
          $pieces = parse_url($url);
          $domain = isset($pieces['host']) ? $pieces['host'] : '';
          if (preg_match('/(?P<domain>[a-z0-9][a-z0-9\-]{0,63}\.[a-z\.]{1,6})$/i', $domain, $regs)) {
            return $regs['domain'];
          }else{
              return false;
          }
    }
    
    function get_page($url) {
        return trim(str_replace($this->get_domain($url),"",str_replace("www.","",str_replace("http://","",str_replace("https://","",$url)))),"/");
    }

    function visitor_referrer_type($referrer) {
        $search_sites = $this->search_sites;
        $social_sites = $this->social_sites;

        if ($referrer == "") {
            return "direct";
        }

        $referrer = explode(".",$referrer);
        $referrer = strtolower($referrer[0]);

        if (array_key_exists($referrer,$search_sites)) {
            return "search";
        }else if (array_key_exists($referrer,$social_sites)) {
            return "social";
        }else {
            return "website";
        }
        
    }

    function visitor_referrer_name($referrer) {
        $search_sites = $this->search_sites;
        $social_sites = $this->social_sites;

        if ($referrer == "") {
            return "";
        }

        $referrer = explode(".",$referrer);
        $referrer = strtolower($referrer[0]);

        if (array_key_exists($referrer,$search_sites)) {
            return $search_sites[$referrer];
        }else if (array_key_exists($referrer,$social_sites)) {
            return $social_sites[$referrer];
        }else {
            return $referrer;
        }
    }

    function visitor_leave_type($leave_url)
    {
        $url_paz = $_SERVER['HTTP_HOST'];

        if ($leave_url == "") {
            return "unknown";
        }

        if (strpos($leave_url, $url_paz) !== false) {
            return "internal";
        }else {
            return "external";
        }
    }

    function visitor_leave_name($leave_url)
    {
        if ($leave_url == "") {
            return "";
        }

        $leave_url = $this->get_domain($leave_url);

        $leave_url = explode(".",$leave_url);
        $leave_url = strtolower($leave_url[0]);
        
        return $leave_url;
    }

}

?>

analytics-db.php

<?php
    
    $database_structure = array();
    $database_structure["analytics"] = array();

    // Dataset IDs

    array_push($database_structure["analytics"],"visitor_visitor_id");
    array_push($database_structure["analytics"],"visitor_session_id");
    array_push($database_structure["analytics"],"visitor_pageview_id");

    // Dataset Visitor Data

    array_push($database_structure["analytics"],"visitor_visitor_sessions");
    array_push($database_structure["analytics"],"visitor_visitor_session");
    array_push($database_structure["analytics"],"visitor_visitor_pageviews");
    array_push($database_structure["analytics"],"visitor_visitor_pageview");
    array_push($database_structure["analytics"],"visitor_visitor_time");
    array_push($database_structure["analytics"],"visitor_visitor_pages");
    array_push($database_structure["analytics"],"visitor_visitor_first_page");
    array_push($database_structure["analytics"],"visitor_visitor_last_page");

    // Dataset Session Data

    array_push($database_structure["analytics"],"visitor_session_pageviews");
    array_push($database_structure["analytics"],"visitor_session_pageview");
    array_push($database_structure["analytics"],"visitor_session_time");
    array_push($database_structure["analytics"],"visitor_session_pages");
    array_push($database_structure["analytics"],"visitor_session_landingpage");
    array_push($database_structure["analytics"],"visitor_session_exitpage");
    array_push($database_structure["analytics"],"visitor_session_first_page");
    array_push($database_structure["analytics"],"visitor_session_last_page");

    // Dataset Pageview Data

    // Dataset Enter Time

    array_push($database_structure["analytics"],"visitor_enter_timestamp");
    array_push($database_structure["analytics"],"visitor_enter_id");
    array_push($database_structure["analytics"],"visitor_enter_year");
    array_push($database_structure["analytics"],"visitor_enter_month");
    array_push($database_structure["analytics"],"visitor_enter_weekday");
    array_push($database_structure["analytics"],"visitor_enter_day");
    array_push($database_structure["analytics"],"visitor_enter_hour");
    array_push($database_structure["analytics"],"visitor_enter_minute");

    // Dataset Leave Time

    array_push($database_structure["analytics"],"visitor_leave_timestamp");
    array_push($database_structure["analytics"],"visitor_leave_id");

    // Dataset Geolocation

    array_push($database_structure["analytics"],"visitor_country");
    array_push($database_structure["analytics"],"visitor_country_code");
    array_push($database_structure["analytics"],"visitor_region");
    array_push($database_structure["analytics"],"visitor_region_code");
    array_push($database_structure["analytics"],"visitor_city");
    array_push($database_structure["analytics"],"visitor_zip");
    array_push($database_structure["analytics"],"visitor_lat");
    array_push($database_structure["analytics"],"visitor_lon");
    array_push($database_structure["analytics"],"visitor_timezone");
    array_push($database_structure["analytics"],"visitor_isp");
    array_push($database_structure["analytics"],"visitor_language");

    // Dataset Technology

    array_push($database_structure["analytics"],"visitor_device");
    array_push($database_structure["analytics"],"visitor_device_brand");
    array_push($database_structure["analytics"],"visitor_device_model");
    array_push($database_structure["analytics"],"visitor_os");
    array_push($database_structure["analytics"],"visitor_os_version");
    array_push($database_structure["analytics"],"visitor_browser");
    array_push($database_structure["analytics"],"visitor_browser_version");
    array_push($database_structure["analytics"],"visitor_resolution");
    array_push($database_structure["analytics"],"visitor_viewport");
    array_push($database_structure["analytics"],"visitor_document");

    // Dataset Referrer

    array_push($database_structure["analytics"],"visitor_referrer_url");
    array_push($database_structure["analytics"],"visitor_referrer_domain");
    array_push($database_structure["analytics"],"visitor_referrer_type");
    array_push($database_structure["analytics"],"visitor_referrer_name");

    // Dataset Page

    array_push($database_structure["analytics"],"visitor_url");
    array_push($database_structure["analytics"],"visitor_domain");
    array_push($database_structure["analytics"],"visitor_page_path");

    // Dataset Time

    array_push($database_structure["analytics"],"visitor_pageview_time");

    // Dataset Leave

    array_push($database_structure["analytics"],"visitor_leave_url");
    array_push($database_structure["analytics"],"visitor_leave_domain");
    array_push($database_structure["analytics"],"visitor_leave_page");
    array_push($database_structure["analytics"],"visitor_leave_type");
    array_push($database_structure["analytics"],"visitor_leave_name");

    // Dataset Update

    array_push($database_structure["analytics"],"visitor_pageview_update");
        
?>

analytics-enter.php

<?php

if(isset($_POST["visitor_ip"])){
    
    $analytics->get_analytics_enter($_POST["visitor_ip"], $_POST["visitor_visitor_id"], $_POST["visitor_session_id"], $_POST["visitor_pageview_id"], $_POST["visitor_referrer"], $_POST["visitor_url"], $_POST["visitor_resolution"], $_POST["visitor_viewport"], $_POST["visitor_document"]);
    print "done";
    
}else{
    
    print "error";
    
}

?>

analytics-update.php

<?php

$analytics->get_analytics_update($_POST["visitor_visitor_id"], $_POST["visitor_session_id"], $_POST["visitor_pageview_id"], $_POST["visitor_pageview_time"], $_POST["visitor_leave_url"]);

?>

JS FILE

$(function(){

    function create_cookie(name,value,days) {
        if (days) {
            var date = new Date();
            date.setTime(date.getTime()+(days*24*60*60*1000));
            var expires = "; expires="+date.toGMTString();
        }else {
            var expires = "";
        }

        var host = window.location.hostname;
        var domain = host.substring(host.lastIndexOf(".", host.lastIndexOf(".") - 1) + 1);

        document.cookie = name+"="+value+expires+"; path=/; domain=." + domain;
    }

    function read_cookie(name) {
        var nameEQ = name + "=";
        var ca = document.cookie.split(';');
        for (var i = 0; i < ca.length; i++) {
            var c = ca;

            while (c.charAt(0) == ' ') {
                c = c.substring(1,c.length);
            }

            if (c.indexOf(nameEQ) == 0) {
                return c.substring(nameEQ.length,c.length);
            }
        }
        return null;
    }

    function random_string(length, chars) {
        var result = '';
        for (var i = length; i > 0; --i) result += chars[Math.round(Math.random() * (chars.length - 1))];
        return result;
    }

    function create_id() {
        var result = random_string(16, "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ");
        var date_time = new Date();
        result += date_time.getFullYear();
        result += date_time.getMonth();
        result += date_time.getDate();
        result += date_time.getHours();
        result += date_time.getMinutes();
        result += date_time.getSeconds();
        return btoa(result);
    }

    function create_visitor_visitor_id() {
        if (visitor_visitor_id() == null) {    
            create_cookie("visitor_id", create_id(), 3650)
        }
    }

    function visitor_visitor_id() {
        return read_cookie("visitor_id");
    }

    function create_visitor_session_id() {
        if (visitor_session_id() == "") {
            sessionStorage.setItem("visitor_session_id", create_id());
        }
    }

    function visitor_session_id() {
        return sessionStorage.getItem("visitor_session_id") == null ? "" : sessionStorage.getItem("visitor_session_id");
    }


    function get_ip() {    
        return $.getJSON("https://api.ipify.org?format=jsonp&callback=?").then(function(data){
            return {
                visitor_ip: data.ip
            }
        });    
    }

    if (new URL(window.location.href).searchParams.get("analytics") != "true") {
        create_visitor_visitor_id();
        create_visitor_session_id();

        var analytics_script_location = "/analytics/controllers/";

        var screen_width = screen.width;
        var screen_height = screen.height;

        var viewport_width = window.innerWidth || document.documentElement.clientWidth || document.body.clientWidth;
        var viewport_height = window.innerHeight || document.documentElement.clientHeight || document.body.clientHeight;

        var document_width = window.innerWidth || document.documentElement.clientWidth || document.body.clientWidth;
        var document_height = document.documentElement.scrollHeight;

        var visitor_ip;
        get_ip().then(function(returndata){ visitor_ip = returndata.visitor_ip});

        var visitor_visitor_id = visitor_visitor_id();
        var visitor_session_id = visitor_session_id();
        var visitor_pageview_id = create_id();

        var visitor_enter_time = new Date();
        var visitor_referrer = document.referrer;
        var visitor_url = window.location.href;

        var visitor_resolution = screen_width + "x" + screen_height;
        var visitor_viewport = viewport_width + "x" + viewport_height;
        var visitor_document = document_width + "x" + document_height;

        var visitor_leave_url = "NULL";

        var current_time = 0;

        var scroll_sum = 0;
        var scroll_count = 0;

        var visitor_enter_sent = false;
        var visitor_leave_sent = false;

        var current_viewport_width = window.innerWidth || document.documentElement.clientWidth || document.body.clientWidth;
        var current_viewport_height = window.innerHeight || document.documentElement.clientHeight || document.body.clientHeight;
        var current_document_width = window.innerWidth || document.documentElement.clientWidth || document.body.clientWidth;
        var current_document_height = document.documentElement.scrollHeight;

        var current_viewport = current_viewport_width + "x" + current_viewport_height;
        var previous_viewport = current_viewport_width + "x" + current_viewport_height;

        var current_document = current_document_width + "x" + current_document_height;
        var previous_document = current_document_width + "x" + current_document_height;

        function track_a_href(event) {
             visitor_leave_url = event.target.href;
             analytics_visitor_leave();
        }

        function analytics_visitor_enter() {    
            if (visitor_enter_sent == false) {

                $.ajax({
                    type: "POST",
                    url: analytics_script_location + "analytics-enter.php",
                    data: {
                        visitor_ip: visitor_ip,
                        visitor_visitor_id: visitor_visitor_id,
                        visitor_session_id: visitor_session_id,
                        visitor_pageview_id: visitor_pageview_id,
                        visitor_referrer: visitor_referrer,
                        visitor_url: visitor_url,
                        visitor_resolution: visitor_resolution,
                        visitor_viewport: visitor_viewport,
                        visitor_document: visitor_document
                    },
                    success: function(data){
                        if(data == "done"){
                            visitor_enter_sent = true;
                        }else{
                            visitor_enter_sent = false;
                        }
                    }
                });
            }
        }

        function analytics_visitor_update() {
            var visitor_pageview_time = Math.round(((new Date() - visitor_enter_time)/1000)%60);

            $.ajax({
                type: "POST",
                url: analytics_script_location + "analytics-update.php",
                data: {
                    visitor_visitor_id: visitor_visitor_id,
                    visitor_session_id: visitor_session_id,
                    visitor_pageview_id: visitor_pageview_id,
                    visitor_pageview_time: visitor_pageview_time,
                    visitor_leave_url: visitor_leave_url
                }
            });
            
        }

        window.setInterval(function() {
            if(visitor_enter_sent == true){
                analytics_visitor_update();
            }else{
                analytics_visitor_enter();
            }
        }, 15000);

        function analytics_visitor_leave() {    
            if (visitor_leave_sent == false) {   
                analytics_visitor_update();
                visitor_leave_delay(250);
                visitor_leave_sent = true;
            }
        }

        function visitor_leave_delay(time) {
            var start = +new Date;
            while ((+new Date - start) < time);
        }

        $(window).load(function() { analytics_visitor_enter(); });
        $(window).on('pagehide', function () { analytics_visitor_leave(); });
        $(window).on('beforeunload', function () { analytics_visitor_leave(); });
        $(window).unload( function() { analytics_visitor_leave(); });

    }

});

Link to comment
Share on other sites

The first impression I get when I see a table definition like that is "Spreadsheet!". Relation database tables are not spreadsheets and require data normalization.

Do not store derived data (Year, Month, Day, Weekday, Hour are all derived from the datetime)

Do not use "SELECT *". Specify the required columns.

5 hours ago, Fratozzi said:

$sql_1_1 = "UPDATE analytics SET " . "visitor_visitor_last_page" . "='" . "" . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";
 $sql_1_2 = "UPDATE analytics SET " . "visitor_session_last_page" . "='" . "" . "' WHERE " . "visitor_session_id" . "='" . $visitor_session_id . "'";

 $sql_1 = $sql_1_1."; ".$sql_1_2.";";
 $this->_db->exec($sql_1);

 That doesn't work, only the $sql_1_1 query will be executed (thankfully as it would open up a whole new vista of SQL injection possibilities).

5 hours ago, Fratozzi said:

$sql_2_2 = "UPDATE analytics SET " . "visitor_visitor_sessions" . "='" . $analytics["visitor_visitor_sessions"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";

            $sql_2_3 = "UPDATE analytics SET " . "visitor_visitor_pageviews" . "='" . $analytics["visitor_visitor_pageviews"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";

            $sql_2_4 = "UPDATE analytics SET " . "visitor_visitor_pages" . "='" . $analytics["visitor_visitor_pages"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";

You can update more than one column in an update query, you don't need a separate one for each.

Use prepared queries, do not insert data values directly into query strings.

Link to comment
Share on other sites

At some point in your learning curve you will realize that you don't need to preface every column name with the table name.  It will save an awful lot of future typing..

And your query statements do not have to be broken up as you did above.  This:

	$sql_2_2 = "UPDATE analytics SET visitor_visitor_sessions = '{$analytics["visitor_visitor_sessions"]}'
	 WHERE visitor_visitor_id = '$visitor_visitor_id'";
	

will work just fine.

As Barand has said, you should really write your queries to use prepared statements and avoid placing the variables directly into the query.

Edited by ginerjm
Link to comment
Share on other sites

On 12/22/2019 at 9:15 AM, Fratozzi said:

$sql_2_2 = "UPDATE analytics SET " . "visitor_visitor_sessions" . "='" . $analytics["visitor_visitor_sessions"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";

$sql_2_3 = "UPDATE analytics SET " . "visitor_visitor_pageviews" . "='" . $analytics["visitor_visitor_pageviews"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";

 $sql_2_4 = "UPDATE analytics SET " . "visitor_visitor_pages" . "='" . $analytics["visitor_visitor_pages"] . "' WHERE " . "visitor_visitor_id" . "='" . $visitor_visitor_id . "'";

becomes...

$sql_2_2 = "UPDATE analytics 
            SET 
                visitor_visitor_sessions = ?,
                visitor_visitor_pageviews = ?,
                visitor_visitor_pages = ?
            WHERE visitor_visitor_id = ? ";
$stmt = $this->db->prepare($sql_2_2);
$stmt->execute( [
                    $analytics['visitor_visitor_sessions'],
                    $analytics['visitor_visitor_pageviews'],
                    $analytics['visitor_visitor_pages'],             
                    $visitor_visitor_id
                ]);           

 

Edited by Barand
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.