Query voor herkomst bezoeken

Door George van Baasbank op 25-10-2013 11:45 gewijzigd op 25-10-2013 12:12

2.277 views

Hallo allemaal,

Voor mijn webshop heb ik de volgende doelstelling:

Toon in een grafiek de top-5 van herkomst-websites.

Nu zal die grafiek wel lukken alleen zit ik met de opzet van een query die de gegevens hiervoor moet gaan aanleveren.

Ik heb een logfile (sys__logfile) waarin e.e.a is opgeslagen. Zo ook de herkomst van een log. Die wordt verkregen door de regel:


<?php
$cHerkomst = $_SERVER['HTTP_REFERER'];
?>

Die levert (veelal) het volgende resultaat op:
http://www.google.nl/url?sa=t&rct=j&q=&esrc=s&source=web&cd=7&ved=0CFEQFjAG&url=http%3A%2F%2Fwww.jqz

Daar heb ik niet zoveel aan als ik het aantal records wil tellen, van bijv. de herkomst door Google.
Ik wil alleen maar de website weten en niet de toegevoegde parameters.

Nu heb ik de volgende vragen:
1. Is er naast $_SERVER['HTTP_REFERER'] nog een mogelijkheid om alleen de websitenaam te achterhalen
2. Hoe zou ik van het verkregen resultaat alleen de websitenaam kunnen filteren

Voor de eerste opzet van de query denk ik aan het volgende:


<?php
$sql = SELECT 
   COUNT(herkomst) AS aantal,
   herkomst AS herkomst
FROM 
   sys__logfile
WHERE
   herkomst NOT LIKE '%jqz.nl%' AND
   herkomst NOT LIKE '%janquintuszwart.nl%' AND
   herkomst NOT LIKE '%tuliprecords.nl%' AND 
   herkomst != ''
GROUP BY
   herkomst
ORDER BY
   aantal DESC
LIMIT
   10"
?>

Dit levert een resultaat op waarbij bijv. de herkomst van Google diverse malen voorkomt omdat de parameters telkens anders zijn.

Als voorbeeld geeft ik de herkomst van de volgende site:

http://www.janquintuszwart.nl/tulip_index.php
http://www.janquintuszwart.nl/shop_cdinfo.php?id=5

Beiden komen dus van de site www.janquintuszwart.nl maar geven twee regels omdat de bijbehorende parameters verschillend zijn.

Dit is het resultaat van de query
206 https://www.google.nl/
163 http://www.orgelnieuws.nl/wcms/
36 http://www.google.nl/url?sa=t&rct=j&q=&esrc=s&frm=...
34 https://www.facebook.com/
29 http://isearch.avg.com/search?cid={D6A3394B-B716-4...
28 http://www.hollandkoor.nl/Webwinkel-Page-178153/Li...
23 http://dirigent.uwpagina.nl/
16 http://www.google.com
15 http://www.refdag.nl/muziek/nieuw-verschenen/jaap_...
14 http://www.hcmk.nl/links

Hoe krijg ik nu deze parameters los van de websitenaam?

Wie wil er meedenken aan de ontwikkeling van deze uitdagende query?

George

Margreet Keijzer

25-10-2013 13:15

Websitenaam:

$p = parse_url($_SERVER['HTTP_REFERER']);
$host = explode(':', $p['host']);
$cHerkomst = $host[0];

Ger van Steenderen

25-10-2013 14:34

@George,

Heb je die volledige url nodig in je database?
Want anders kan je beter alleen de host erin opslaan, zoals Magreet zegt (maar dan iets eenvoudiger:


<?php
$host = parse_url($_SERVER['HTTP_REFERER'],PHP_URL_HOST);
?>

George van Baasbank

27-10-2013 12:24

Jammer dat parse_url in een query niet werkt. Dan kon ik ook het verleden gebruiken.
In de actuele situatie werkt dit overigens wel goed.


<?php
$sql = "UPDATE sys__logfile SET herkomstsite = parse_url(herkomst,PHP_URL_HOST)";
?>

George

- SanThe -

27-10-2013 12:43

En zo?
<?php
$sql = "UPDATE sys__logfile SET herkomstsite = '".parse_url(herkomst,PHP_URL_HOST)."'";
?>

George van Baasbank

27-10-2013 13:12 gewijzigd op 27-10-2013 13:14

San The

Helaas, dit is de uitkomst van jouw query


<?php
UPDATE sys__logfile SET herkomstsite = ''
?>

Dit is de code die ik run:


<?php
$verbinding = mysqli_connect("my_server","my_user","my_pasword","my_database");

// Check connection
if (mysqli_connect_errno($verbinding)) {
    echo "Geen verbinding met SQL-database. Foutnummer: " . mysqli_connect_error();
}

mysqli_set_charset($verbinding,"utf8");

$sql = "UPDATE sys__logfile SET herkomstsite = '".parse_url(herkomst,PHP_URL_HOST)."'";
$cResult = mysqli_query($verbinding,$sql);
echo $sql;

mysqli_close($verbinding);
?>

Ger van Steenderen

27-10-2013 13:30

Even een sql fuctie gemaakt:


DELIMITER $$

CREATE FUNCTION getHost(url CHAR(255))
RETURNS CHAR(50)
BEGIN
	declare start_pos INT;
	declare first_slash INT;
	SET start_pos := LOCATE('://', url) + 3;
	SET first_slash := LOCATE('/', url, start_pos);
	IF first_slash = 0 THEN return substring(url, start_pos);
	ELSE return substring(url, start_pos, first_slash - start_pos);
	END IF;

END$$

DELIMITER ;

Reageren

Inloggen om te reageren