Zur Navigation

SQL Anzahl Jahr gruppiert

1 uwe

Hallo,
ich habe folgende Daten in einem Counter mit dem Feldern:
Wert (textfeld 255 Zeichen),
Monat monat int(6),
timestamp int(14)

Mit der Abfrage
<?php

$mysqlhost="xx"; // MySQL-Host angeben

$mysqluser="xxx"; // MySQL-User angeben

$mysqlpwd="sssssssssss"; // Passwort angeben

$mysqldb="aaaaaaaaaa"; // Gewuenschte Datenbank angeben

 

$connection=mysql_connect($mysqlhost, $mysqluser, $mysqlpwd) or die

("Verbindungsversuch fehlgeschlagen");

 

mysql_select_db($mysqldb, $connection) or die("Konnte die Datenbank nicht waehlen.");

 

$sql = "SELECT wert, monat, timestamp FROM chc_referrers";

 

$adressen_query = mysql_query($sql) or die("Anfrage nicht erfolgreich");

 

$anzahl = mysql_num_rows($adressen_query);

echo "Anzahl der Datensätze: $anzahl";

?>

 

<table cellpadding="1" cellspacing="3" border="1">

    <tr>
     <td>Monat</td>
     <td>timestamp</td> 
     <td>wert</td>
                     
    </tr>

 

<?php

while ($adr = mysql_fetch_array($adressen_query)){


?>

    <tr>
<td><?=$adr['monat']?></td>
<td><?=$adr['timestamp']?></td>
<td><?=$adr['wert']?></td>



       
    </tr>

<?php

}

?>

 

</table>

ergibt folgendes:

Monat timestamp wert
200804 1207139270 http://www.google.com/search?sourceid=navclient&hl=de&ie=UTF-8&rls=DVXE,DVXE:2006-14,DVXE:de&q=barrierefreie+ferienh%c3%a4user+nordsee
200804 1209566635 www.google.com
200804 1209158863 http://www.rollstuhl-urlaub.de/ns-dorum-muehlenferienhaus.htm
200804 1209158863 www.rollstuhl-urlaub.de
200804 1207143955 http://www.google.de/search?hl=de&q=Urlaub+f%C3%BCr+menschen+mit+handicap+nordsee&btnG=Google-Suche&meta=
200804 1209586050 www.google.de
200804 1207145695 http://www.google.de/search?hl=de&q=rollstuhlgerechter+urlaub+adressen&meta=
200804 1207152753 http://www.google.de/search?q=urlaub+mit+gehbehinderung&hl=de&start=10&sa=N
200804 1207155659 http://www.deichgedanken.de/bannerwerbung/index.php
200804 1208932508 www.deichgedanken.de
200804 1207157075 http://www.google.de/search?hl=de&rlz=1T4GGIC_deDE227DE227&sa=X&oi=spell&resnum=1&ct=result&cd=1&q=urlaub+behindertengerecht&spell=1
200804 1207158893 http://www.google.de/search?q=muehlenferienhaus.de&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:de:official&client=firefox-a
200804 1207170430 http://www.google.de/search?hl=de&q=Ferienwohnungen+Behindertengerecht+Nordsee&btnG=Suche&meta=
200804 1207170909 http://www.google.de/search?hl=de&cr=countryDE&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=rollstuhlgeeignete+ferienh%C3%A4user+nordsee&spell=1
200804 1208932508 http://www.deichgedanken.de/bannerwerbung/index.php?kat=Urlaub
200804 1207206775 http://www.google.de/search?hl=de&q=Ferienhaus+rollstuhl&meta=
200804 1207224709 http://www.google.de/search?hl=de&client=firefox-a&rls=org.mozilla:de:official&hs=vY3&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=ferienhaus+nordsee+rollstuhl&spell=1
200804 1209388555 http://www.google.de/search?q=urlaub+behindertengerecht&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:de:official&client=firefox-a
200804 1207258850 http://www.google.de/search?hl=de&q=rollstuhlgeeignete+ferienh%C3%A4user+nordsee&btnG=Google-Suche&meta=cr%3DcountryDE
200804 1207283951 http://www.google.de/search?hl=de&client=firefox-a&channel=s&rls=org.mozilla%3Ade%3Aofficial&q=Gehbehindert+Urlaub&btnG=Suche&meta=
200804 1207313116 http://www.google.de/search?q=ferienwohnung+duhnen+mit+garten&hl=de&start=140&sa=N
200804 1208434591 http://www.greuelglasmacher.com/paravan.htm
200804 1208434591 www.greuelglasmacher.com
200804 1207338818 http://www.google.de/search?hl=de&q=behi
200805 1212079539 suche.t-online.de
200805 1210149875 http://www.google.de/search?hl=de&q=Behindertengerecht+Urlaub&btnG=Google-Suche&meta=
200805 1210154811 http://www.metareha.com/search.cgi?query=urlaub%20nordsee;isValidated=Yes;nh=2
200805 1211099944 www.metareha.com
200805 1210158694 http://www.google.de/search?hl=de&q=behindertengerecht+urlaub+machen&meta=
200805 1210166001 http://www.google.de/search?sourceid=navclient&hl=de&ie=UTF-8&rlz=1T4DADE_deDE214DE214&q=ferienhaus+nordsee+behindertengerecht
200805 0 http://suche.t-online.de/fast-cgi/tsc?classification=internet-t

Ich möchte jetzt eine Gruppierung nach JAHR und Suchmaschine sortiert nach höchsten zuerst.
So sollte es dann aussehen:

2008
http://www.Google.de 1399
http://www.deichgedanken.de 333
http://www.metareha.com 99
http://suche.t-online.de 55

2009
http://www.Google.de 1011
http://www.deichgedanken.de 222
http://www.metareha.com 66
www.greuelglasmacher.com 23

Eine Idee war, das Jahr aus dem $timestamp zu filtern
$jahrtimestamp = date("Y",$timestamp);
und dann als Hilfsfeld zu verwenden.

Ein weiteres "Problem" sind die unterschiedlichen Angaben in dem Feld
Wert. Hier will ich nur die Internetadresse haben. Also bis einschließlich
.de oder .com

Dank schon jetzt für eure Hilfe

Gruss Uwe

03.01.2010 18:27

2 uwe

Hallo,
ich bin ein Stückchen weiter. Aber wie das Jahr gruppiert mit Summe ausgeben?:

Gruss Uwe

$sql = "SELECT wert, timestamp FROM chc_referrers";

 

$adressen_query = mysql_query($sql) or die("Anfrage nicht erfolgreich");

 

$anzahl = mysql_num_rows($adressen_query);

echo "Anzahl der Datensätze: $anzahl";

?>

 

<table cellpadding="1" cellspacing="3" border="1">

    <tr>
     <td>Adresse</td>
     <td>Jahr</td> 
 
                     
    </tr>

 


<?php

while ($adr = mysql_fetch_array($adressen_query)){
$wert1 = getHost($adr['wert']); // Gives example.com

$wert3   = date("Y", $adr['timestamp']); 

?>

    <tr>

<td><?=$wert1 ?></td>

<td><?=$wert3 ?></td>


       
    </tr>

<?php

}

function getHost($Address) {
   $parseUrl = parse_url(trim($Address));
   return trim($parseUrl[host] ? $parseUrl[host] : array_shift(explode('/', $parseUrl[path], 2)));
}
?>

 

</table>

03.01.2010 19:51

3 Jörg Kruse

Eine Gruppierung nach Jahren kann man vielleicht schon in der Datenbankabfrage realisieren, wenn man den Monats-String mithilfe von SUBSTRING() kürzt und nach dem Ergebnis gruppiert; z.B. (ungetestet):

SELECT wert, SUBSTRING(monat, 1, 4) AS jahr, timestamp FROM chc_referrers GROUP BY jahr ORDER BY jahr

Wenn du in PHP deine Funktion getHost() verwendest, kannst du dir ja in einer Schleife, in der du die Datenbsankergebnisse ausgibst, ein neues Array zusammenzimmern; so in etwa könntest du vielleicht vorgehen:

$referers = array()

while (list($adr) = mysql_fetch_row($adressen_query)){
    $host = getHost($adr);
    if (! isset($referers[$host])) {
        $referers[$host] = 1;
    } else {
        $referers[$host] = $referers[$host] + 1;
    }
}

Das Array $referers kannst du dann anschließend sortieren (z.B. mit asort()) und in einer foreach Schleife wieder ausgeben

03.01.2010 23:17 | geändert: 03.01.2010 23:17

4 uwe (Gast)

Hallo,
danke, das mit die dem Monat geht.
Aber wie bekomme ich die Summen in der Tabellenanzeige?

Gruss
Uwe

03.01.2010 23:34

5 Jörg Kruse

Wenn mein Vorschlag mit dem Array $referers funktioniert, dann sollte nach dem Sortieren so etwas drin stehen:

array(
'google.com' => 111,
'bing.com' => 11,
'yahoo.com' => 8
)

Das kannst du dann folgendermaßen ausgeben:

foreach ($referers as $host => $hits) {
    echo $host . ': ' . $hits . '<br/>;
}

03.01.2010 23:39 | geändert: 03.01.2010 23:39

6 Uwe (Gast)

Hallo,
ich bekomme noch eine Fehlermeldung.

Parse error: syntax error, unexpected T_WHILE in /home/www/ssssssss/sssssssss/abfrage.php on line 30


Gruss
Uwe

$sql = "SELECT wert, SUBSTRING(monat, 1, 4) AS jahr, timestamp FROM chc_referrers GROUP BY jahr ORDER BY jahr";


$adressen_query = mysql_query($sql) or die("Anfrage nicht erfolgreich");

$referers = array()

while (list($adr) = mysql_fetch_row($adressen_query)){
    $host = getHost($adr);
    if (! isset($referers[$host])) {
        $referers[$host] = 1;
    } else {
        $referers[$host] = $referers[$host] + 1;
    }
}


$anzahl = mysql_num_rows($adressen_query);

echo "Anzahl der Datensätze: $anzahl";
foreach ($referers as $host => $hits) {
    echo $host . ': ' . $hits . '<br/>;
}

?>

04.01.2010 06:01

7 Jörg Kruse

Welche Zeile davon ist denn Zeile 30?

Edit:

Hier fehlt noch ein Semikolon:

$referers = array();

04.01.2010 08:26

8 Uwe (Gast)

Hallo,
wieder ein Stückchen weiter:
Fatal error: Call to undefined function getHost() in /home/www/s0ssss/html/abfrage.php on line 29


Gruss
Uwe


    $sql = "SELECT wert, SUBSTRING(monat, 1, 4) AS jahr, timestamp FROM chc_referrers GROUP BY jahr ORDER BY jahr";


$adressen_query = mysql_query($sql) or die("Anfrage nicht erfolgreich");

$referers = array()     ;

while (list($adr) = mysql_fetch_row($adressen_query)){

//hier kommt dann der Fehler:
    $host = getHost($adr);
    if (! isset($referers[$host])) {
        $referers[$host] = 1;
    } else {
        $referers[$host] = $referers[$host] + 1;
    }
}


$anzahl = mysql_num_rows($adressen_query);

echo "Anzahl der Datensätze: $anzahl";
foreach ($referers as $host => $hits) {
    echo $host . ': ' . $hits . '<br/>';
}

?>

06.01.2010 00:07

9 Jörg Kruse

Ja, du musst die Definition deiner Funktion aus Beitrag 2 auch integrieren

06.01.2010 01:30

10 Uwe (Gast)

Hallo,
mit folgendem Code
$sql = "SELECT wert, SUBSTRING(monat, 1, 4) AS jahr, timestamp FROM chc_referrers GROUP BY jahr ORDER BY jahr"; 

$adressen_query = mysql_query($sql) or die("Anfrage nicht erfolgreich");

 

$anzahl = mysql_num_rows($adressen_query);

echo "Anzahl der Datensätze: $anzahl";
echo "<br>";

?>
<?php
function getHost($Address) {
   $parseUrl = parse_url(trim($Address));
   return trim($parseUrl[host] ? $parseUrl[host] : array_shift(explode('/', $parseUrl[path], 2)));
}


 $referers = array()     ;

while (list($adr) = mysql_fetch_row($adressen_query)){
    $host = getHost($adr);
    if (! isset($referers[$host])) {
        $referers[$host] = 1;
    } else {
        $referers[$host] = $referers[$host] + 1;
    }
}


$anzahl = mysql_num_rows($adressen_query);

foreach ($referers as $host => $hits) {
    echo $host . ': ' . $hits . '<br/>';
}

?>

erscheint

Anzahl der Datensätze: 3
www.google.com: 1
www.rollstuhl-urlaub.de: 1
www.google.de: 1

Es sind aber fast 8000 Datensätze drin

Gruss
Uwe

06.01.2010 19:37