Van twee naar één query

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

George van Baasbank

George van Baasbank

27/03/2015 14:27:14
Quote Anchor link
Hallo allemaal,

Voor een analyse van mijn logfile heb ik de volgende vraag:

Ik wil bij een IP-adres de naam van het land van herkomst tonen.

Hiervoor heb ik twee werkende queries maar omdat ik dit in één tabel wil tonen moet ik één query maken/hebben.
Hoe voeg ik deze queries nu samen?
Moet ik gebruik maken van de JOIN-functie of een SUBQUERY?

Query 1, toont de basisgegevens:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
            l.id AS id,
            l.ipadres AS ipadres,
            l.browser AS browser,
            l.omschrijving AS omschrijving,
            l.tablet AS tablet,
            l.mobiel AS mobiel
        FROM
            sys__logfile AS l
        WHERE
            l.robot = 0 AND
            YEAR(l.datum) = '$nJaar' AND MONTH(l.datum) = '$nMaand' AND DAY(l.datum) = '$nDag'
        ORDER BY
            datum


Query 2, toont het land van herkomst o.b.v. van een IP-adres:
Bron query: http://www.ip2nation.com/ip2nation/Sample_Scripts/Output_Full_Country_Name
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
                c.country
            FROM
                ip2nationCountries c,
                ip2nation i
            WHERE
                i.ip < INET_ATON('l.ipadres')
                AND
                c.code = i.country
            ORDER BY
                i.ip DESC
            LIMIT 0,1


Wie kan/wil mij helpen?

George
Gewijzigd op 27/03/2015 14:28:23 door George van Baasbank
 
PHP hulp

PHP hulp

24/11/2020 13:01:12
 
Eddy E

Eddy E

27/03/2015 14:47:43
Quote Anchor link
probeer deze eens:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT     l.id AS id,
    l.ipadres AS ipadres,
    l.browser AS browser,
    l.omschrijving AS omschrijving,
    l.tablet AS tablet,
    l.mobiel AS mobiel,
    c.country AS country
FROM     sys__logfile AS l
LEFT JOIN ip2nationCountries AS c
     ON (l.ipadres = c.ip)
LEFT JOIN ipnation AS i
     ON (c.code = i.country)
WHERE l.robot = 0
AND YEAR(l.datum) = '$nJaar'
AND MONTH(l.datum) = '$nMaand'
AND DAY(l.datum) = '$nDag'
ORDER BY datum


Wel raar dat je apart jaar, maand en dag moet opgeven, waarom niet gewoon een datum vergelijken met een datum?
 
George van Baasbank

George van Baasbank

27/03/2015 15:54:03
Quote Anchor link
Eddy,

Bedankt voor je suggestie. Helaas werkt het niet omdat jij velden hebt gebruikt die niet in de tabel voorkomen. Zoals c.ip
De reden voor de splitsing van de datum komt omdat het veld DATUM een DATETIME-veld is.n

Toevoeging op 27/03/2015 16:31:48:

Omdat het idee van Eddy niet werkt ben ik maar eens de query stap voor stap gaan opbouwen.
Bij het eerste toevoegen middels de LEF JOIN gaat het al fout.
Het gaat in principe om ca. 24 records uit de logfile die na de JOIN zo'n 1,1 miljoen records toont

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
            l.id AS id,
            l.ipadres AS ipadres,
            l.browser AS browser,
            l.omschrijving AS omschrijving,
            l.tablet AS tablet,
            l.mobiel AS mobiel,
            i.country AS landcode
        FROM
            sys__logfile AS l
        LEFT JOIN
            ip2nation AS i
        ON
            i.ip < INET_ATON(l.ipadres)
        WHERE
            l.robot = 0 AND
            YEAR(l.datum) = '2015' AND MONTH(l.datum) = '3' AND DAY(l.datum) = '24'
        ORDER BY
            i.ip DESC


Volgens mij ligt het aan de ON-clausule i.ip < INET_ATON(l.ipadres)

Wie ziet de manco??

Toevoeging op 27/03/2015 16:41:48:

en volgende stap toont het juiste aantal records maar is overal de landcode hetzelfde

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
            l.id AS id,
            l.ipadres AS ipadres,
            l.browser AS browser,
            l.omschrijving AS omschrijving,
            l.tablet AS tablet,
            l.mobiel AS mobiel,
            i.country
        FROM
            sys__logfile AS l
        LEFT JOIN
            ip2nation AS i
        ON
            i.ip < INET_ATON(l.ipadres)
        WHERE
            l.robot = 0 AND
            YEAR(l.datum) = '2015' AND MONTH(l.datum) = '3' AND DAY(l.datum) = '24'
        GROUP BY
            l.ipadres
        ORDER BY
            i.ip DESC
Gewijzigd op 27/03/2015 16:50:16 door George van Baasbank
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

27/03/2015 17:23:00
Quote Anchor link
Gezien de opbouw van die tabel (alleen de start van de ip_range) wordt het een draak van een query
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    l.id,
    l.ipadres,
    l.browser,
    c.country
FROM
    sys__logfile l
JOIN
    ip2nation i
    ON i.ip < INET_ATON(l.ipadres)
JOIN
    ip2nationCountry c
    ON i.country = c.code
LEFT JOIN
    ip2nation p
    ON i.ip > p.ip AND p.ip < INET_ATON(l.ipadres)
WHERE p.ip IS NULL

Ik ga er even vanuit dat die iptabel volledig is (wat ik betwijfel want ik heb er één met bijna 2x zoveel records)

Ik heb je er in een ander topic ook al op geattendeerd dat het vergelijken op functies over kolommen moet zien te vermijden. Hetzelfde geldt ook voor de join voorwaarden.
De juiste indexen en het kunnen gebruiken ervan zijn essentieel voor een goede performance.
Gewijzigd op 28/03/2015 18:32:29 door Ger van Steenderen
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

31/03/2015 14:08:33
Quote Anchor link
Hmz bovenstaande query levert niet het juiste resultaat op, deze wel:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT SQL_NO_CACHE
    l.id,
    l.ipadres,
    c.country,
    INET_NTOA(i.ip) ip
FROM
    sys__logfile l
JOIN
    ip2nation i
    ON i.ip < INET_ATON(l.ipadres)
JOIN
    ip2nationcountries c
    ON i.country = c.code
WHERE i.ip = (SELECT MAX(ip) FROM ip2nation p WHERE p.ip < INET_ATON(l.ipadres))
AND l.datum BETWEEN '2015-03-27 00:00:00' AND '2015-03-27 23:59:59'

>> 183 rows in 8.872 sec
Gewijzigd op 31/03/2015 14:09:48 door Ger van Steenderen
 
George van Baasbank

George van Baasbank

06/04/2015 20:17:25
Quote Anchor link
Bericht aan Ger van Steenderen:
In jouw eerste reactie vroeg jij jezelf af of e.e.a. wel goed zou werken omdat jouw tabel eens zo groot was.
Ik kom er nu achter dat de versie die ik gebruik inderdaad niet de juiste landen weergeeft. Waarom weet ik niet maar er gaat e.e.a. mis.
Daarom de vraag aan jou: Waar heb jij de tabellen vandaan die wellicht wel beter zijn? Ik heb de tabellen gevonden op: http://www.ip2nation.com/ip2nation/Sample_Scripts/Output_Full_Country_Name

George
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

07/04/2015 11:55:30
Quote Anchor link
Ik heb mijn tabellen gebaseerd op de data van:
http://lite.ip2location.com/database-ip-country#ipv4-mysql
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.