Query voor herkomst bezoeken
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:
Code (php)
1
2
3
2
3
<?php
$cHerkomst = $_SERVER['HTTP_REFERER'];
?>
$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:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?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"
?>
$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
Gewijzigd op 25/10/2013 12:12:26 door George van Baasbank
$p = parse_url($_SERVER['HTTP_REFERER']);
$host = explode(':', $p['host']);
$cHerkomst = $host[0];
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:
In de actuele situatie werkt dit overigens wel goed.
Code (php)
1
2
3
2
3
<?php
$sql = "UPDATE sys__logfile SET herkomstsite = parse_url(herkomst,PHP_URL_HOST)";
?>
$sql = "UPDATE sys__logfile SET herkomstsite = parse_url(herkomst,PHP_URL_HOST)";
?>
George
Code (php)
1
2
3
2
3
<?php
$sql = "UPDATE sys__logfile SET herkomstsite = '".parse_url(herkomst,PHP_URL_HOST)."'";
?>
$sql = "UPDATE sys__logfile SET herkomstsite = '".parse_url(herkomst,PHP_URL_HOST)."'";
?>
Helaas, dit is de uitkomst van jouw query
Code (php)
1
2
3
2
3
<?php
UPDATE sys__logfile SET herkomstsite = ''
?>
UPDATE sys__logfile SET herkomstsite = ''
?>
Dit is de code die ik run:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?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);
?>
$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);
?>
Gewijzigd op 27/10/2013 13:14:23 door George van Baasbank
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 ;
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 ;