Archiv der Kategorie: MS Access

Ein ehrgeiziges Projekt: Stichwortverzeichnis

Meine beiden mit Abstand am häufigsten gebrauchten Kochbücher sind „Das Bayrische Kochbuch“ und „Joy of Cooking“. Und bei beiden ist das am Meisten genutzte Feature das Stichwortverzeichnis, da findet man alles! Es eignet sich auch hervorragend zum kreuz- und querschmökern, man findet dabei Rezepte die man sonst nie gesehen hätte. Langer Rede kurzer Sinn, ein Stichwortverzeichnis (Volltext-Index) wäre ein schickes Feature für mein Inselfisch-Kochbuch. Und dazu möchte ich nicht das Stichwort-Feature von WordPress nutzen, das ist mir viel zu umständlich zu bedienen und zu schlecht auszuwerten.

Dazu waren einige Vorüberlegungen nötig. Zunächst muss ich mal festlegen, woher ich die Stichwörter nehme. Ich habe in meinem Inselfisch-Kochbuch knappe 400 Rezepte, in denen relevanter Text in zwei Feldern steht, einmal in post_title und einmal in post_content. Der Titel ist meist zwischen 5 und 10 Wörter lang, der Content kann wesentlich länger sein, bis zu einer ganzen DIN A 4 Seite und mehr, also auf jeden Fall mehr als 100 Wörter. Ich möchte meine Stichwortbasis natürlich maschinell erzeugen, dazu muss ich irgendwie einzelne Wörter aus meiner Datenbasis extrahieren. Dazu muss ich meinen Quelltext aufsplitten (ob mit PHP oder VBA wird sich noch zeigen) und in eine Tabelle schreiben. Diese Tabelle (ich nenne sie mal Rohdaten) soll am Ende nur alle Stichwörter und eine ID (Autowert) enthalten. Man sieht schon: mit dem Content, das wird uferlos, das können leicht mehrere Zehntausend Wörter werden. Ich beschränke mich also auf die Wörter aus dem post_title, das ist besser zu handeln. Wir machen das mal als erste Annäherung und schauen uns dann an, was wir mit den extrahierten potentiellen Stichworten anfangen können.

Wie ich das in MS Access angehe: ich importiere mir die gesamte Tabelle wp_posts aus dem Original-Inselfischkochbuch über CSV-Export aus phpmyadmin. Dann schreibe ich mir eine Abfrage,, die nur den post_title enthält und als where-Klausel post_Type = post und post_status = publish bekommt, damit ich nur die echt veröffentlichten Rezepte in der Datenbasis habe. Die Abfrage heißt quelle und sieht so aus:

Screenshot quelle

265 Datensätze a drei bis zehn Wörter, damit kann man arbeiten. Ich lege ausserdem fest, dass ich nur Großgeschriebene Wörter im Stichwortverzeichnis haben möchte. Das ist willkürlich, aber doch recht sinnvoll, weil damit die ganzen kleinen Füllwörter rausfallen.

Dann gehts rund: Ich lege mir eine Tabelle namens ziel an, die nur zwei Felder hat: ID(AutoWert) und Wort(Text). Sie bleibt zunächst leer. Dann bastle ich mir ein VBA-Modul. Hier lege ich zwei Recordsets an, rstquelle das ist die Abfrage Quelle, und rstziel, das ist die Tabelle Ziel.

Jetzt gehe ich zum ersten Datensatz in rstquelle und lese mir den Inhalt des Feldes post_title ein. Dann verwende ich die VBA-Funktion split(), die zerlegt das Feld in einzelne Wörter, die in ein Array geschrieben werden.  Ich laufe durch dieses Array und prüfe zunächst, ob das Wort Groß oder klein geschrieben ist:

Asc(Left(liste(i), 1) >= 65) And (Asc(Left(liste(i), 1)) <= 90)

Mit dieser Funktion bin ich nicht so recht glücklich, weil sie unerklärlicherweise manche Wörter mit Kleinbuchstaben doch durchrutschen läßt, aber ich hab noch nichts besseres gefunden.

Dann werden noch evtl vorhandene Sonderzeichen entfernt, dazu gibts eine Funktion die Folgende Zeichenkette durchläuft:

Const strSonderzeichen As String = „.,:;#+’*?=)(/%$§!~\}][{“

Dann schreibe jeweils ein gefundenes, geputztes Wort in die Zieltabelle. Dann gehe ich zum nächsten Datensatz der Quelltabelle und wiederhole den Vorgang. das mache ich, bis EOF der Quelltabelle erreicht ist.

Hurra! 669 Datensätze, beim ersten Drüberschauen sieht es schon mal ganz gut aus. Noch die Dubletten ausblenden, das geht mit einer Abfrage mit Gruppierung ganz easy. Sortieren, man sieht noch ein bisschen Datenschmutz ganz oben, das bereinigt man per Hand.

abfrage quelle

Es bleiben 496 recht manierliche Stichworte übrig. Damit könnte ich jetzt schon nach MySQL und WordPress gehen und die Webseite aufbauen, aber ich halte mich noch ein wenig in Access auf und teste nochmal, mit der „wackeligen“ Asc-Funktion bin ich nicht zufrieden.

Von Joomla nach WordPress umziehen – der Vollständigkeit halber

Ich habe drüben im Drupal-Blog ausführlich beschrieben, wie man Inhalte aus Drupal in WordPress-Beiträge überführt, nachzulesen hier. Das selbe machen wir jetzt noch mit Joomla, und zwar möchte ich die Beiträge aus einer bestimmten Kategorie nach WordPress exportieren. Meine ausgewählte Kategorie sind natürlich die Rezepte, mit der Kategorie-ID 8 (im Zweifelsfall nachschlagen in der Tabelle #__categories).

Wie kommen wir an Titel und Inhalt der Rezepte?

Aus der Tabelle #__content. Die holen wir uns nach Access rüber und schauen sie mal näher an.

content

content

Die id und den title nehmen wir mit, den alias lassen wir aus. Dann brauchen wir noch den introtext und den fulltext (beide!), weil Joomla den Beitragstext in diese beiden Felder aufteilt, sobald man einen Weiterlesen-Tag eingefügt hat. Das sieht man hier im Screenshot oben bei den ids 4 bis 8, so sieht das dann aus.

Den state müssen wir filtern, -2 bedeutet „marked for deletion“, 1 ist veröffentlicht, wir nehmen nur die 1er. Und die catid filtern wir auch, auf die 8 für Rezepte. (Bei mir ist noch ein Haufen Schrott vom Testen mit drin, ich nehm mal noch die Datensätze 9-47 raus). Übrig bleibt die folgende Tabelle:

content_gefiltert

content_gefiltert

Die specken wir noch ab, die Felder introtext und fulltext werden zu einem Feld namens bodytext zusammengefügt, die catid und der state fliegen raus.

Jetzt fehlen noch die Tags für die Kategorien

Kleine Erinnerung: ich hatte die Rezeptkategorien aus WordPress auf Joomla-Tags ohne Schachtelung abgebildet. Die holen wir uns jetzt wieder zurück, und zwar aus der Tabelle #__tags.

tags

tags

Der erste Eintrag für root fliegt raus, vom Rest übernehmen wir id und title. Parent_id ist überall leer, weil nicht geschachtelt, published = 1 passt auch. Um jetzt die Tags den Rezepten (hier in der Tabelle id_title_bodytext) zuzuordnen, verknüpfen wir über die Tabelle contentitem_tag_map. Beim Importieren nach Access darauf achten, dass content_item_id und tag_id als Integer rüberkommen! Das sieht dann so aus:

tags_beziehungen

tags_beziehungen

Darüber basteln wir uns eine Abfrage, in der zunächst mal jedes Rezept so oft vorkommt, wie es Tags zugeordnet hat:

abfrage_tags

abfrage_tags

Kurzer Blick über die Tabelle: ich habe zu keinem Rezept mehr als 2 Tags zugeordnet, aber das passt schon, ist korrekt.

Zuordnen der Tags zu WordPress-Kategorien

Dafür zieht man sich alle gültigen Tags aus der #–tags ab und importiert sie als Kategorien nach WordPress, wie das geht habe ich in diesem Artikel unter Schritt 1: Anlegen der Kategorien in WordPress im Drupal-Blog ausführlich beschrieben.

Dann habe ich mir die entstandene Tabelle wp_terms nach Access rübergeholt:

wp_terms

wp_terms

Hier kann ich jetzt über den name des Terms auf den title des tags verknüpfen, weil die Schreibweise identisch ist:

join_term_name

join_term_name

Damit bekomme ich die WordPress-Kategorie-IDs zu den Rezepten.

term_id

term_id

Das hatten wir so ähnlich schonmal, jedes Rezept taucht jetzt so oft auf, wie es Kategorien/Tags hat. Jetzt Gruppieren wir noch, und nehmen den ersten und letzten Wert der Term-ID. Diese sind bei den Datensätzen gleich, die nur eine Kategorie zugeordnet haben, das ist schon richtig so.

ersterwert_letzterwert

ersterwert_letzterwert

So, das wars. Diese Tabelle kann man nach WordPress importieren, und zwar genau so wie ich es in diesem Artikel beschrieben habe:

DRUPAL NACH WORDPRESS: BEITRÄGE MIT KATEGORIEN ERZEUGEN

Dabei muss man wahrscheinlich noch eine kleine Abfrage einbauen, was passieren soll wenn erster und letzte Wert der term_id gleich sind, aber prinzipiell funktioniert die Sache so einwandfrei, die Beiträge werden in WordPress angelegt und mit Kategorien versehen.

Rezepte mit 2 Tags importieren, so gehts

Ich hab mir mal eine kleine Export-Tabelle mit 11 Datensätzen gebaut, die sieht so aus:

11_export

11_export

id, titel und content kommen wie gehabt aus WordPress, kat_1 und kat_2 sind die passenden Schlagwort-IDs aus Joomla, die wir im letzten Beitrag via Access zugeordnet haben. Jetzt wird noch das Import-Skript umgebaut, da kommt die Logik für die Tag-Zuordnung innerhalb der Foreach-Schleife nach dem Erzeugen des neuen Artikelobjekts mit rein. Das Ganze sieht dann so aus:

//**************Tags zuordnen
           $roh1 = $zeile->kat_1;
           $roh2 = $zeile->kat_2; 
           
            $tag1 =strval($roh1);
            $tag2 =strval($roh2);
            $neue_id= $article->id;
            
            // Das ist einen Versuch wert!
                $basePath = JPATH_ADMINISTRATOR.'/components/com_content';
                require_once $basePath.'/models/article.php';
                $articlemodel = new ContentModelArticle(array('table_path' => $basePath . '/tables'));

                $params = array(
                    'id' => $neue_id,                // Article being tagged 
                    'tags' => array($tag1, $tag2)   // Tag IDs from #__tags to tag article with
                );
                
                if($articlemodel->save($params)){
                    echo 'Success!';
                }
                    
                    
                    
                    
                    
                    //**************Ende Tags zuordnen

Der Import der Rezepte bleibt genau wie gehabt, nur findet jetzt noch die Tag-Zuordnung statt. Damit erhalten wir nach dem Import eine wohlgefüllte Tagliste:

tagliste_screenshot

tagliste_screenshot

Was, wenn ich mehr als 2 Kategorien übernehmen will?

Dann muss das Tagmapping in Access aufgebohrt werden, wo ich jetzt mit erster Wert/letzter Wert operiert habe, muss eine Kreuztabelle rein, damit sollten auch beliebig viele Kategorien pro Rezept abgebildet werden können. Die Export-Tabelle wird dann halt ziemlich breit, und das Script braucht eine Logik, mit der alle Tag-Felder (kat_1.. kat_n) berücksichtigt werden. Aber das führt mir jetzt entschieden zu weit, da darf jeder selber experimentieren. Ich mach hier einen Break, und eine Denkpause für den nächsten Beitrag.

Tagmapping: da brauchts ein bisschen Vorarbeit

Die grundlegende Mechanik zum programmatischen Einfügen von Tags

Dank eines guten Tipps aus dem deutschen Joomla-Forum habe ich folgendes Codesnippet zum Laufen gekriegt. Es ordnet dem Artikel mit der ID 80 zwei Tags mit den IDs 7 und 9 zu. Der Knackpunkt war, dass man die IDs der zuzuordnenden Tags als Strings übergeben muss – da musste auch erstmal draufkommen!

    $roh1 = 7;
    $roh2 = 13;
    $tag1 =strval($roh1);
    $tag2 =strval($roh2);
    
        $basePath = JPATH_ADMINISTRATOR.'/components/com_content';
        require_once $basePath.'/models/article.php';
        $articlemodel = new ContentModelArticle(array('table_path' => $basePath . '/tables'));

        $params = array(
            'id' => 80,                // Article being tagged 
            'tags' => array($tag1, $tag2)   // Tag IDs from #__tags to tag article with
        );
        
        if($articlemodel->save($params)){
            echo 'Success!';
        }

Jedenfalls, das ist schonmal eine gute Ausgangsbasis. Soweit ich das bislang beim Testen gesehen habe, werden die Tags immer neu zugeordnet, evtl. vorher zum Artikel schon angelegte Tags werden rausgeschmissen. Macht aber nix, wir arbeiten ja auf einer niegelnagelneuen Joomla-Installation, da sind jetzt erstmal noch keine Tags zugeordnet. Bevor wir diese Mechanik aber in unseren Artikelimport aus WordPress einbauen können, ist noch etwas Freiturnen auf der Datenbank angesagt. Ich geh da mal wieder den Weg über Access, weil das doch etwas handlicher ist als MySQL.

Die Ausgangsbasis

Ich hab nochmal eine Einschränkung beschlossen, zu jedem Beitrag aus WordPress werden maximal zwei Kategorien exportiert, sonst ist mir das jetzt zu aufwendig.

Erste Voraussetzung ist schonmal, dass wir alle Kategorien aus WordPress abgefasst und erfolgreich als Schlagwörter nach Joomla importiert haben. Davon ziehe ich mir die #__tags-Tabelle nach Access ab und bastle mir eine Abfrage auf meine Tabelle mit den WordPress-Kategorien, der Join geht hier über den Namen der Kategorie in WordPress auf den Titel des Tags in Joomla. Das funktioniert, weil die Schreibweisen identisch sind. Das Tagmapping sieht dann so aus:

tagmap_screenshot

tagmap_screenshot

Die term_id und der name kommen aus WordPress, title und id aus Joomla, das ist die Basis für unser Mapping.

Zuordnung der Kategorien zu den WordPress-Posts

Man braucht vier Tabellen, um die Zuordnung der Kategorien zu den einzelnen Beiträgen in WordPress abzubilden, das sieht so aus:

wordpress_kat_post

wordpress_kat_post

Meine Tabelle published_posts ist ein schlanker Auszug aus der wp_posts und enthält nur ID, Titel und Content der veröffentlichten Rezepte. So kriege ich erstmal eine Liste aller Rezepte mit den zugeordneten Kategorien, wobei jedes Rezept so oft auftaucht wie es Kategorien hat:

alleposts_allekat

alleposts_allekat

Um das ganze jetzt auf zwei Kategorien pro Rezept einzuschränken, setze ich hierauf noch einmal eine Abfrage, in der jeweils nur der erste und der letzte Wert der Kategorien zu einem Rezept angezeigt wird :

ersterwert_letzterwert

ersterwert_letzterwert

Den PostContent hab ich mal weggelassen, sonst wirds so unübersichtlich. Jetzt kann man noch den ersten und letzten Wert der term_id einblenden:

ersterletzterwert_termid

ersterletzterwert_termid

Hierzu Joine ich mir dann noch meine Mapping-Tabelle von oben und ziehe mir daraus die Joomla-Tag-IDs:

tagmapping_ausfuehrlich

tagmapping_ausfuehrlich

Das sieht soweit ganz gut aus, jetzt können wir die überflüssigen Felder ausblenden, so dass nur der post_title, der post_content und die beiden Joomla-Tag-IDs übrigbleiben. So, das wars. Damit erstelle ich mir meine neue Import-Tabelle. Ach so, den post_content hab ich unterwegs verloren, den holen wir uns jetzt wieder dazu, aber dann ist jetzt wirklich Finish.

mapping_endprodukt

mapping_endprodukt

Mit Access macht sowas richtig Spaß! Für die Import-Mechanik nach Joomla gibts jetzt aber einen neuen Beitrag, der hier ist lang genug.

Beitragskategorien aus der Datenbank abfassen

Für Spitzenklöppler: das WordPress-Kategorienmodell

Um an die Beitragskategorien heranzukommen, muss man sich mit sage und schreibe 5 Tabellen herumschlagen. Das kommt daher, weil die WordPress-Entwickler das Kategorienmodell unbedingt mehrstufig anlegen mussten. Das ist zwar wunderbar verschachtelbar und ergibt ganz eindrucksvolle Baumstrukturen, aber datenverarbeitungstechnisch ist es meiner Meinung nach mit Kanonen auf Spatzen geschossen. Mit einer winzigen Ausnahme (die Backrezepte im Inselfisch-Kochbuch) habe ich noch in keinem meiner Blogs eine Unterkategorien-Ebene gebraucht, und damit das hier nicht allzu unübersichtlich wird, lasse ich es auch bei der einen Ebene. Haben sie ein bißchen Zeit? dann wollen wir mal.

Die relevanten Tabellen

Erstens natürlich die wp_posts, aus der holen wir uns die ID, also den Primärschlüssel der Beiträge. Dann brauchen wir noch:

  • wp_term_relationships
  • wp_term_taxonomy
  • wp_terms
  • (wp_termmeta, nein, die brauchen wir nicht, nur der Vollständigkeit halber erwähnt)

Warum das ganze mit Term und nicht mit Category bezeichnet ist: rein theoretisch kann man sich auch noch eigene Taxonomien anlegen, die dann eben nicht Kategorie heissen sondern einen eigenen Namen kriegen. Sowas hab ich anno Dunnerkeil mal in meinem Bilderblog gemacht, da gabs eine eigene Taxonomie „Jahreszeiten“, die bestand aus genau vier Einträgen, nämlich „Frühling, Sommer, Herbst, Winter“. Bin ich wieder davon abgekommen, ich hab die Jahreszeiten einfach in die erste Kategorieebene mit aufgenommen, geht auch und ist wesentlich einfacher zu verwalten. Aber das nur am Rande bemerkt, wir holen uns die Tabellen jetzt mal ins Access rein und basteln uns die nötigen Verknüpfungen.

Das Datenmodell im Detail

Die ersten beiden Tabellen: wp_term_relationships und wp_posts

Wichtig ist hier erstens die object_id, das ist die ID des Beitrags aus der wp_posts. Einem Beitrag können ja mehrere Kategorien zugeordnet sein, deshalb tauchen die IDs hier auch mehrfach auf. Die term_taxonomy_id verweist auf die Kategorie. Die term_order könnte man für eine benutzerdefinierte Sortierung der Kategorien verwenden, aber das geht jetzt zu weit, das lassen wir weg.

screenshot_term_relationships

screenshot_term_relationships

Wir basteln uns also die erste Verknüpfung mit der object_id auf die wp_posts.

screenshot_beziehnungen_posts_relationships

screenshot_beziehnungen_posts_relationships

Die dritte Tabelle: wp_terms_taxonomy

Hier wird es schon ein bißchen undurchsichtiger. Die ersten drei Felder ID, term_taxonomy_id und term_id haben alle den selben Wert, da kann man nur raten wofür welches gut ist. Da wir nur die term_taxonomy_id aus der wp_term_relationships eindeutig zuordnen können, nehmen wir die halt, und merken uns als Hauptschlüssel die ID. Im Feld taxonomy steht immer der Wert category, wir haben ja nur diese eine Taxonomie, das können wir auch weglassen. Interessanter wäre da noch das Feld „parent“, das die übergeordnete Kategorie im Falle verschachtelter Kategorieebenen enthält, aber auch das lassen wir mal aussen vor, sonst blickt hier kein Schwein mehr durch.

Kleine Kuriosität am Rande: hier wird ein count mitgeführt, der die Anzahl der Beträge zu einer Kategorie wiederspiegelt. Kann man das nicht nachher im Rahmen einer Abfrage berechnen?

screenshot_term_taxonomies

screenshot_term_taxonomies

Unsere Beziehungen sollten jetzt so aussehen:

screenshot_beziehungen_3

screenshot_beziehungen_3

Die vierte Tabelle: wp_terms

Na gottseidank, da blickt man wenigstens halbwegs durch. Die term_id können wir aus der wp_term_taxonomy zuordnen, der name (na endlich!) ist der Name der Kategorie, slug ist auch klar, und die term_group ignorieren wir einfach.

screenshot_wp_terms

screenshot_wp_terms

Jetzt kommt endlich Butter bei die Fische, wir können unsere Beziehungen vervollständigen.

screenshot_beziehungen_4

screenshot_beziehungen_4

Haben wir da nicht noch was vergessen?

Die Tabelle wp_termmeta

Ja, die gibts auch noch, aber ehrlich gesagt weiß ich nicht so ganz genau, wofür man die verwenden könnte, die zieht nur wenn man mit mehreren Taxonomien arbeitet. Bei marketpress gibt es eine gelehrte Abhandlung dazu, ich hab sie allerdings nicht ganz verstanden, muss ich zugeben. Nachdem die Tabelle allerdings bei mir immer leer ist, ignoriere ich sie schlicht und ergreifend.

Sonst noch was? Ach ja, ich hätts beinah vergessen:

Die Tags = Schlagwörter stecken in der selben Logik

Und zwar wird für die Schlagwörter eine eigene Taxonomie „post_tag“ angelegt. Ich verwende prinzipiell keine Schlagwörter, wenn sie es getan haben lassen sie sich nicht irritieren, uns reichen die Beziehungen über die Kategorien.

Und jetzt: die Abfrage der Kategorien über alle vier Tabellen

Anmerkung: damit das hier nicht total uferlos wird, beschränke ich mich hier auf eine Kategorieebene. Sie sehen gleich, warum: Wir holen uns zunächst mal eine Auswahl relevanter Daten aus allen vier Tabellen, der SQL sieht dann so aus:

SELECT wp_posts.ID, wp_posts.post_title, wp_term_relationships.object_id, wp_term_relationships.term_taxonomy_id AS wp_term_relationships_term_taxonomy_id, wp_term_taxonomy.term_taxonomy_id AS wp_term_taxonomy_term_taxonomy_id, wp_term_taxonomy.term_id AS wp_term_taxonomy_term_id, wp_term_taxonomy.taxonomy, wp_term_taxonomy.count, wp_terms.term_id AS wp_terms_term_id, wp_terms.name
FROM wp_terms INNER JOIN ((wp_posts INNER JOIN wp_term_relationships ON wp_posts.[ID] = wp_term_relationships.[object_id]) INNER JOIN wp_term_taxonomy ON wp_term_relationships.[term_taxonomy_id] = wp_term_taxonomy.[term_taxonomy_id]) ON wp_terms.[term_id] = wp_term_taxonomy.[term_id];

Das Ergebnis: eine schon mal relativ übersichtliche Tabelle

Hier taucht jedes Rezept so oft auf, wie es Kategorien zugeordnet hat. Wenn wir jetzt ausser den Kategorien noch Schlagwörter mit drin hätten, müssten wir die herausfiltern, aber haben wir nicht, alles brav nur Kategorien.

screenshot_kategorien_rohdaten

screenshot_kategorien_rohdaten

Ein kleiner Kunstgriff: die Abfrage auf der Abfrage

Damit ich mir keinen Wolf parametrisiere, habe ich einfach auf die eben gezeigten Rohdaten (die Abfrage heisst auch kategorien_rohdaten) eine zweite Abfrage aufgesetzt, die ist schon übersichtlicher:

SELECT Count(kategorien_rohdaten.[ID]) AS AnzahlvonID, kategorien_rohdaten.[name]
FROM kategorien_rohdaten
GROUP BY kategorien_rohdaten.[name];

Auf Deutsch: ich zähle nur die Beitrags-IDs zu jeder genannten Kategorie, und fasse pro Kategorie die Summe zusammen. Das Ergebnis:

screenshot_kategorien_endergebnis

screenshot_kategorien_endergebnis

Na bitte, geht doch 🙂

Nur noch ein kleiner Glitch ist drin: die Summe beim Backwerk stimmt nicht. Das kommt daher, dass ich bei den Backwerken drei Unterkategorien angelegt habe, nämlich „Kuchen und Torten“, „Weihnachtsbäckerei“ und „Pikante Bäckereien“. Allerdings erlaubt es einem WordPress beim Erstellen oder Bearbeiten eines Beitrags, eine Unterkategorie anzugeben ohne auch die übergeordnete Hauptkategorie anzukreuzen, das habe ich offensichtlich einige Male gemacht, und daher stammt der Zählfehler. Die Korrektur ist einfach: ich entferne die Kategorie Backwerk und behandle die drei Unterkategorien wie Kategorien erster Ordnung, mein Excel für das Tortendiagramm kann mit Unterkategorien eh nix anfangen 🙂

Ich überlasse es jedem, der mit mehr als einer Kategorieebene gearbeitet hat selber, da die Logik entsprechend rauszupfriemeln, da muss man in der wp_terms_taxonomy den parent berücksichtigen, das ist mir jetzt hier zuviel Gedöns. Das hier langt jetzt nämlich echt für einen Beitrag, der hier ist lang genug – schließlich ist ja schon fast Feiertag!

 

 

 

 

 

 

 

 

Beitragsstatistik: weil mir das Dashboard da nicht reicht

Da waren die WordPress-Programmierer ein bißchen sparsam

Zugegeben, eine rudimentäre Beitragsstatistik läßt sich auch im Dashboard unter „alle Beiträge“ ablesen. Lesen, wohlgemerkt, wenn man da aussagekräftige Auswertungen fahren will, steht man ganz schön auf dem Schlauch, da kann man sich die Zahlen nur abschreiben, und das ist ein mühselig Spiel. Schon die allereinfachsten Statistiken wie z.B. Anzahl der Beiträge über die Tage gerechnet kann man sich zwar am Bildschirm angucken, aber wie zum Geier kriegt man die Daten da raus und rein ins Excel? Pfiffkas, da braucht man schon wieder Plugins dafür… ähem, Spaß muss sein, wir machen das natürlich anders 🙂

Ran an die Datenbank

Die meisten Daten von Interesse, wenn es um die Beiträge geht, stecken in der WordPress-Haupttabelle, der wp_posts. Die kennen wir ja schon recht gut, und die klemmen wir uns jetzt mal und schubsen sie aus MySQL raus und rein ins Access, da lässt es sich besser werken. Ich nehme wieder die Daten aus dem Inselfisch-Kochbuch, da war am meisten los.

Anmerkung am Rande: eine Access-2010-Lizenz gibts ab um die 30 €, wer öfter mal ein handliches Datenbankerl braucht sollte über diese Investition nachdenken!

Da hat sich ganz schön was angesammelt übers Jahr

Auf gehts, raus aus MySQL via CSV für Excel, Zeichensatz Windows 1250, Erste Zeile enthält Feldnamen anhaken und Schuß! Rein in Access, darauf gucken dass das Feld post_content den Datentyp Memo kriegt, und jetzt fangen wir richtig an.

1543 Datensätze bei 316 Rezepten – ganz schön viel Holz, aber das filtern wir uns gleich mal zurecht. Das ist jetzt alles Wiederholung, ich schalt mal den Schnellgang ein.

  • unser Erfassungszeitraum startet am 29.10.2016. Das Inselfisch-Kochbuch gibts zwar schon länger, aber da habe ich es nach einem katastrophigen Providerwechsel neu aufgebaut.
  • Ende-Datum ist gestern, 21.12.2017.
  • Uns interessieren eigentlich nur die veröffentlichten Rezepte, die erkennt man am post_type = post und post_status = publish.

Daraus stricken wir unsere erste Abfrage, wir nehmen erstmal nur wenige Felder mit, für den Anfang reichen uns das Erstellungsdatum (post_date) und der Titel (post_title). Im Access-Abfrageassistenten sieht das ganz bequem so aus:

Datum und Titel

Datum und Titel

Und hier kommt noch für alle Hardcorer das SQL:
SELECT wp_posts.post_date, wp_posts.post_title
FROM wp_posts
WHERE (((wp_posts.post_status)=“publish“) AND ((wp_posts.post_type)=“post“));

Das Ergebnis hat die erwarteten 316 Datensätze und sieht doch schon mal sehr hübsch aus:

liste_datum_titel

liste_datum_titel

Und wo ist der Rest geblieben?

Immerhin 1227 Datensätze sind bei unserer Abfrage auf der Strecke geblieben, wer oder was sind die? Access zu Hilf!

  • 1192 Datensätze sind vom post_status „inherit“, davon wiederum 1120 vom post_type „revision“. Das sind die Revisionen, die man sich im Beitragseditor zurückholen kann, wenn man mal einen Beitrag verschlimmbessert hat und eine frühere Version wieder herstellen möchte.
  • 16 mal post_type = page, das sind Seiten, keine Beiträge.
  • 72 mal post_type = attachment, das sind die hochgeladenen Bilder
  • und noch ein bißchen Kleinkram, ein paar drafts vom post_type = nav_menu_item und ein Contact-Form-Seven-Formular, das wars dann aber auch.

Wir bleiben jetzt mal bei unseren 316 veröffentlichten Beiträgen = Rezepten. Hier kommt die erste Statistik, die:

Anzahl der veröffentlichten Rezepte pro Tag über den ganzen Erfassungszeitraum

rezepte_pro_tag

rezepte_pro_tag

Was sagt uns dieses zackige Diagramm? Ich habe von Ende Oktober 2016 bis Ende Januar 2017 Rezepte eingehackt wie der Weltmeister, das war erstens die Wiederherstellung aus dem Datenbankbackup und zweitens die gleichzeitige Überarbeitung aller Rezepte im Hinblick auf Barrierefreiheit.  Von April bis Juli hab ich dann noch die Nachzügler aus dem Backup eingehackt, und ab August läuft dann der Normalbetrieb, mit ein bis zwei Rezepten an einem Tag. Dazu nehmen wir mal zum Vergleich die:

Besucherentwicklung über den gleichen Zeitraum

Man kann ganz deutlich sehen, dass die Anzahl der eingestellten Rezepte und die Anzahl der Besucher über den gleichen Zeitraum nahezu nix miteinender zu tun haben.

ifkb_besucher_pro_tag

ifkb_besucher_pro_tag

Ich mach hier mal einen kleinen Kunstgriff (wen es interessiert: mit dem GIMP) und mache eine

Kombination beider Diagramme

Jetzt stimmt natürlich die Y-Achsenbeschriftung nicht mehr, aber das kann ich verschmerzen. Die blaue Linie ist die Anzahl der von mir eingestellten Rezepte, die grüne Linie zeigt den Besucherverlauf.

besucher_gegen_rezepte

besucher_gegen_rezepte

Fazit: meinen Besuchern ist es nicht so wichtig, dass ständig neue Rezepte eingestellt werden. Die kommen, weil sie ein bestimmtes Rezept über Google gefunden haben, und dann kommen sie wieder und wieder zum Schmökern und zum Nachschlagen. Zur Erinnerung: ich habe 2819 Stammkunden (erkenntlich an den individuellen IP-Adressen), die so oft hereinschauen, dass sie den Löwenanteil meiner über 50.000 Hits auf dem Inselfisch-Kochbuch ausmachen.

Ich lerne daraus

Ja, was eigentlich? Dass ich mit dem Inselfisch-Kochbuch auf einem guten Weg bin, ein treues Stammpublikum habe und mir keinen Streß machen muss, weil ich nicht täglich neue Rezepte einstelle. Das läuft prima so wie es ist, die Besucherzahlen haben insgesamt eine leicht steigende Tendenz, das ist ein gesundes Wachstum und sehr erfreulich.

Und damit lass ich es für heute mal gut sein. Morgen gibts noch mehr WordPress-Zahlenschubserei,versprochen!

 

 

Noch mehr Zahlenschubserei: Besucher im schwarzen Pinguin

Ich machs kurz, ich verspreche es! 🙂

Die Besucherstatistik vom schwarzen Pinguin 2017

Vom Start der Zählung am 10.2.2017 bis gestern,  21.12.2017 waren es insgesamt 8704 Besucher an 316 Tagen im letzten Jahr, macht 27,5 Besucher pro Tag. Als Rohdiagramm sieht das so aus:

exceldiagramm besucher pro tag

besucher pro tag

Da ist im September ein Ausreisser dabei, das war 132 mal die selbe IP-Adresse, scheint ein Hack-Versuch gewesen zu sein. Den nehmen wir mal raus, der verzerrt mir die Stat doch sehr. Dasselbe nochmal ohne den Hacker:

ohne_hacker

ohne hacker

Na, das nenne ich doch einen vernünftigen Geschäftsverlauf. Ich hab das Jahr recht ruhig angefangen, dann einen sehr aktiven Sommer hingelegt und so ab September wieder ein bißchen weniger geschrieben. Da wärs jetzt natürlich interessant, wenn man die Besucherzahlen gegen die Anzahl meiner Beiträge gegenrechnen könnte, aber dazu kommen wir später. Jetzt gibts erst noch die Stammkunden, also alle, die öfter als einmal auf der Seite waren, das waren 970 Leutchen. Wie man hier sieht, waren die meisten so etwa zwischen 5 mal und 15 mal da:

stammkunden

stammkunden

Auch das ist eine sehr zufriedenstellende Gauss’sche Glocke, wie es sich gehört. Und damit laß ich es auch schon gut sein, jetzt gehen wir ans WordPress-Eingemachte, und dazu gibt es einen neuen Beitrag.

Alle alten Programmierer lieben Zahlenschubsereien – Besucherstatistik im Inselfisch-Kochbuch

„Trau keiner Statistik, die du nicht selbst gefälscht hast!“

Das ist mein Motto seit den ersten Stat-Vorlesungen an der Uni in den frühen 80er Jahren. Und es ist heissa! Jahresende und Zeit für einen statistischen Jahresrückblick.

Unser gutes altes WordPress sitzt ja auf einer wunderbaren Datenbank, und Datenbanken sind heissbegehrtes Statistikerfutter. Ich nehme mir mal mein Inselfisch-Kochbuch vor, das lebt und ist das ganze Jahr gepflegt worden, wollen mal sehen was wir da an statistischen Auswertungen rauskriegen. Es wird schon lange mal wieder Zeit für ein bißchen Spaß auf der Datenbank!

Was natürlich die SEO-Freaks interessiert: der Besucherzähler

Aber klar doch, den nehmen wir uns als Erstes vor. Ich verwende das leichtgewichtige Visitor Counter Plugin , das ist problemlos in der Installation und Anwendung. Es schreibt die statistischen Werte in eine eigene Tabelle weg, die heißt (Präfix-)vcp_log und ist damit einfach zu lokalisieren. Als CSV für MS Excel exportieren, und der Spaß kann losgehen…  aber halt! Ich schubs die ganze Sache natürlich in mein Leib- und Magen-Datenbankerl rein, das feine alte Microsoft Access, da kann ich Auswertungen fahren bis die Schwarte knackt 🙂

Visitor Counter Plugin: die Rohdaten

Schauen wir uns die Tabelle vcp_log mal kurz an, die ist übersichtlich genug. Es gibt drei Felder:

  1. LogID
  2. IP
  3. Time

und das wars schon. Die LogID ist ein Autoinkrement und interessiert nicht weiter, die IP-Adresse ist selbsterklärend und der Timestamp als Datum und Uhrzeit auch.

Aber ansonsten kanns jetzt mal losgehen: Time in Tagesdatum umwandeln, Gruppieren, Anzahl von IP ausgeben, Excel-Liniendiagramm draus machen und schon haben wir diese hübsche Grafik der Besucher pro Tag:

Excel-Diagramm Neue Besucher pro Tag

Excel-Diagramm Neue Besucher pro Tag

Kurze Analyse des Diagramms

Wow, was war Anfang September los? Der Peak mit den fast 700 Besuchern kommt von der Veröffentlichung des Inselfisch-Kochbuchs im Newsletter des Bayrischen Blinden- und Sehbehinderten-Verbandes, das ist ganz eindeutig zuzuordnen.

Ansonsten ist der Grafik noch zu entnehmen, dass die Anzahl der Besucher sich übers Jahr bei ungefähr 100 – 200 pro Tag eingependelt hat, Tendenz leicht steigend.

Was die Grafik nicht zeigt – nicht zeigen kann – ist die Anzahl der „Wiederholungstäter“. Ich weiss aber aus vielen persönlichen Kontakten, dass das Inselfisch-Kochbuch ein treues Stammpublikum hat, viele kommen immer wieder, auch mehrmals die Woche, und schauen ihre Lieblingsrezepte nach oder gucken obs was Neues gibt. Die gibts aber ein andermal, das sprengt mir hier sonst den Beitrag.

Was wir noch machen können: die Uhrzeit

Dabei kommt eine kleine Überraschung heraus: angeblich hatten wir Nachts um 1 die meisten  Besucher:

Excel-Diagramm Besucher nach Uhrzeit

Excel-Diagramm Besucher nach Uhrzeit

Das glaube ich ja noch nicht ganz! Wollen mal sehen, wenn der Timestamp in GMT (Greenwich Mean Time) angegeben sein sollte, sind wir mit der MEZ eine Stunde voraus, und mit der Sommerzeit sogar zwei Stunden. Dann läge der Peak so bei ca. 11 Uhr abends, das glaub ich ja schon eher, da guckt man nach der Sportschau nochmal in den Computer. Das würde auch den relativ niedrigen Stand zwischen 21 und 23 besser erklären, wenn das in echt 19 – 21 Uhr war, da sind die Leute beim Abendessen, das haut schon eher hin!

Und noch ein bisschen Kleinkram

Seit Beginn der Zählung am 29.10.2016 hatten wir 51789 Besucher insgesamt, nur in 2017 (bis gestern, 21.12.) waren es 45172. Das macht im Durchschnitt rund 123 Besucher pro Tag, in 2017 waren es pro Monat im Schnitt 3771. Spitzenreiter war der September mit 5367 Besuchern insgesamt, s. oben.

Nachgereicht: die „Wiederholungstäter“

Also, gucken wir da noch mal genauer hin: wir hatten insgesamt 51789 Besucher. Wenn ich mir die nach der IP-Adresse gruppiere, bleiben noch 8479 übrig. Das heisst, jeder Besucher ist im Schnitt 6,11 mal im Inselfisch-Kochbuch gewesen. Das geht natürlich noch genauer, ich klemm mir mal alle, die öfter als einmal da waren (das waren 2819 Stammkunden) und bastel mir ein Diagramm:

Besucher öfter als einmal

Besucher öfter als einmal

Na wer sagts denn, das sieht doch einer Gauss’schen Glocke verdammt ähnlich! Die allermeisten Besucher kommen anscheinend so zwischen 30 und 70 mal im Jahr, damit kann ich prima leben. Der Ausreisser am Anfang mit über 400 Hits war übrigens wahrscheinlich ein Hacker, der hat die Seite im Sekundenrythmus angesurft. Den muss man aus der Stat  rausnehmen, dann siehts so aus:

Ohne Hacker

Ohne Hacker

Na bitte, geht doch! Eine wunderbare Normalverteilung, wie aus dem Lehrbuch. Ich lieeebe Statistik! 🙂

Und das genügt jetzt auch mal fürs Erste! Natürlich wollen wir auch noch Stats über die Rezepte und Kategorien und so weiter fahren, aber dazu gibts einen neuen Beitrag.

 

Die bessere Alternative zum -zigfachen Join: Kreuztabelle

Also, zuerst mal dieses: ich habe versucht herauszufinden, ob MySQL Kreuztabellen (Pivot Tables) kann oder nicht, und die Tendenz beim Googlen geht zu eher nicht. Jedenfalls gibt es keinen Transform-Befehl, man muß sich da irgendwie anders behelfen.

Ich habe da die tollsten Konstrukte gefunden, hier ein besonders Hübsches mit einem Case für jedes Feld… na, das ist ja wohl nicht der Weisheit letzter Schluß. Da greife ich doch lieber auf meine Leib- und Magendatenbank zurück, nämlich Microsoft Access. Ich bin ja sonst kein Microsoft-Fan, aber das gute alte Access ist einfach eine tolle Datenbank mit einem sagenhaften Bedienkomfort im Entwurfsmodus. Auch wenn die zugrundeliegende Jet-Engine schon viele Jahre auf dem Buckel hat, im intuitiven Zusammenstellen aller möglichen Arten von Abfragen ist Access echt ungeschlagen.

Was wollen wir erreichen?

Wir haben ja im letzten Artikel gesehen, daß wooCommerce zu jeder Bestellung mindestens 48 Einträge in der wp_postmeta anlegt. Ich hätte jetzt gerne einen tabellarischen Überblick über alle Bestellungen, mit allen 48 Werten aus der wp_postmeta zu jeder Bestellung.

Das heißt im Klartext: eine Tabelle mit den relevanten Daten aus der wp_posts, und allen 48 Meta Keys als Felder in einer Zeile. Auf jeden Fall brauchen wir einen Join von der wp_posts auf die wp_postmeta über die ID der Bestellung, und dann noch die Meta Keys als Feldnamen, befüllt mit den Meta Values. Klingt schaurig kompliziert, ist aber in Access relativ einfach machbar, nämlich mit einer Kreuztabelle.

Der SQL für die Kreuztabelle

…sieht so aus:

TRANSFORM First(postmeta_alle_orders.meta_value) AS ErsterWertvonmeta_value
SELECT postmeta_alle_orders.post_id, postmeta_alle_orders.post_title, postmeta_alle_orders.post_status, postmeta_alle_orders.post_type
FROM postmeta_alle_orders
GROUP BY postmeta_alle_orders.post_id, postmeta_alle_orders.post_title, postmeta_alle_orders.post_status, postmeta_alle_orders.post_type
PIVOT postmeta_alle_orders.meta_key;

Den kann man sich mit Hiilfe des Kreuztabellen-Assistenten erstellen und dann im Entwurfsmodus komfortabel editieren.

Das Ergebnis der Kreuztabelle

Ich habs mal schnell der Übersichtlichkeit halber in ein Formular gepackt (auch hierfür hat Access einen prima Assistenten), da sieht dann z.B. unser Datensatz mit der Nummer 42 so aus:

formular_kreuztabelle

formular_kreuztabelle

Na, da hat man wenigstens mal alle Felder im Blick. Wenn man jetzt noch wüßte, für was die alle gut sind… aber da lasse ich jeden selber raten, anhand der Feldnamen müßte man da relativ weit kommen.

Wie kann man es übersichtlicher machen?

Wir haben jetzt wenigstens ein bißchen den Überblick gewonnen, mit 7 Zeilen Jet-SQL statt 48 mal Join und Case. Die Krux ist halt, dass es von vorne herein ein Unding ist, einem Datensatz so viele Werte redundant zuzuordnen.

Wenn sie nur mal die vielen Felder anschauen, die etwas mit Adressen zu tun haben, die gehören ausgelagert! Da gehört ein Fremdschlüssel auf die Kundennummer rein, und zu den Kunden-Basisdaten in einer eigenen Tabelle legen wir uns noch eine schnuckelige zweite Tabelle mit Rechnungsadresse und Lieferadresse (falls abweichend) des Kunden an, das wars dann und ist sauber gelöst.,

Damit können die ganzen Adressfelder aus den Bestellungen rausfliegen, das macht so über den Daumen gepeilt schon mal etwas mehr als 20 Felder weniger. Genauso sieht es mit den Zahlungsmodalitäten und den Steuerinformationen aus, auch die gehören normalisiert und ausgelagert, das wären dann noch mal ein rundes Dutzend Felder weniger.

Aber… die wooCommerce-Entwickler wissen ja anscheinend nicht, wie man Daten normalisiert und Detailtabellen anlegt, deshalb dieser unüberschaubare Datenwust. Das ist einfach nur grottenschlecht programmiert, da beißt die Maus kein Faden ab. Es kann sich ja jeder selber überlegen, wie ein sauberes Datenmodell für die Bestellungen aussehen könnte, so als Fingerübung zum Entspannen 😉

Ich mach hier mal Schluß, und überleg mir ein neues Thema. See you demnächst, ich wünsche gute Erholung vom wooCommerce-Datenchaos!