Neue DB-Struktur, altes Problem!

[Gerhard0964]

Hallo,

ich habe jetzt Admidio 2.0.7 installiert. Natürlich brauche ich jetzt wie schon im Frühjahr mit der DB von Admidio 1.x, wieder meine Statistikabfragen. Leider ist es mit der neuen DB noch wesentlich schwieriger geworden, diese zu finden. Anders ausgedrückt, ich schaffe es jetzt gar nicht mehr an die Daten zu kommen.

Wollte ich z.B. alle die mindestes 15 Jahre im Verein sind haben, klappte das mit der alten DB mit dieser Abfrage:

SELECT `adm_users`.`usr_id` AS 'Mitglieds-ID', `usr_last_name` AS 'Nachname', `usr_first_name` AS 'Vorname', `usd_value` AS 'Eintrittsdatum', YEAR( CURDATE( ) ) - YEAR( usd_value ) +1 AS 'Mitgliedszeit'
FROM `adm_users`
RIGHT JOIN `adm_user_data` ON `adm_users`.`usr_id` = `adm_user_data`.`usd_usr_id`
WHERE `usd_usf_id` = '7'
AND `usd_value` >0
AND YEAR( CURDATE( ) ) - YEAR( usd_value ) +1 > 15
GROUP BY `usd_value` DESC'

Leider habe ich jetzt aber z.B. die Felder usr_last_name, usr_first_name nicht mehr, da alles in usd_value steht und dort nur über die usd_usf_id definiert wird.
Und das schaffe ich nicht mehr. Dazu reichen meine SQL Kenntnisse nicht aus um damit noch klar zu kommen.

Warum wurde die alte Struktur in der Form eigentlich aufgegeben?

Gibt es hier jemanden, der mir damit helfen kann?

Danke und viele Grüße
Gerhard

[matzman2000]

Hi Gerhard,

in aller Kürze: Füge in der Datei ADMIDIO\adm_program\modules\lists\mylist_prepare.php in Zeile 197 diese Codezeile ein:

Code: Alles auswählen

echo $main_sql; exit();

Wenn Du Dir Deine Abfrage nun mit einer eigenen Liste in Admidio zusammenbaust und die Liste aufruft, wird Dir die SQL Abfrage angezeigt (und nicht die Liste).
Damit dürftest Du auch in der Lage sein auf die entsprechenden Felder zuzugreifen.

Gruss,
Matze

[Gerhard0964]

Upps, das werden ja Monsterabfragen!

Danke Matze!

Da muss ich mich jetzt erst mal mit befassen.



Gruß
Gerhard

[Gerhard0964]

So, ich habe mir jetzt mal eine erste Übersicht verschafft. Aber ich komme nicht wirklich weiter.
Wenn ich nur nach Geburtsdatum und Geschlecht frage, brauche ich für Durchschnittsalter insgesamt und getrennt nach Männlein und Weiblein, erhalte ich folgende Abfrage:

SELECT mem_leader, usr_id, row1id10.usd_value, row4id11.usd_value
FROM adm_roles, adm_categories, adm_members, adm_users
LEFT JOIN adm_user_data row1id10 ON row1id10.usd_usr_id = usr_id
AND row1id10.usd_usf_id =10
LEFT JOIN adm_user_data row4id11 ON row4id11.usd_usr_id = usr_id
AND row4id11.usd_usf_id =11
WHERE rol_id =2
AND rol_valid =1
AND rol_cat_id = cat_id
AND cat_org_id =1
AND mem_rol_id = rol_id
AND mem_valid =1
AND mem_usr_id = usr_id
AND usr_valid =1
ORDER BY usr_id ASC

(Ich habe mir die Abfrage schon so umgebaut, dass sie nach usr_id sortiert)

Wie trenne ich diese Abfrage jetzt so auf, dass ich zum Einen das Durchschnittsalter aller Mitglieder, bzw. getrennt nach Männlein und Weiblein erhalte?

OK Durchschnittsalter alle Mitglieder geht auch so:

SELECT mem_leader, usr_id, row1id10.usd_value
FROM adm_roles, adm_categories, adm_members, adm_users
LEFT JOIN adm_user_data row1id10 ON row1id10.usd_usr_id = usr_id
AND row1id10.usd_usf_id =10
WHERE rol_id =2
AND rol_valid =1
AND rol_cat_id = cat_id
AND cat_org_id =1
AND mem_rol_id = rol_id
AND mem_valid =1
AND mem_usr_id = usr_id
AND usr_valid =1
ORDER BY usr_id ASC

Ich habe jetzt einfach nur die Daten aller Mitglieder. Aber wie erhalte ich davon den Durchschnitt?

Ich bin schlicht überfordert!

Gruß
Gerhard

[Gerhard0964]

Hallo,

mir wurde ja schon mal vorgeworfen, dass ich zu ungeduldig sei, aber hat denn keiner eine Idee, wie ich fogende Daten aus der neuen DB bekomme:

Mitgliederstatistik
Mitglieder
Mitglieder insgesamt:
Männer:
Frauen:

Durchschnittsalter
Mitglieder insgesamt xx Jahre
Männer xx Jahre
Frauen xx Jahre
Das jüngste MItglied ist x Jahre alt.
Das älteste Mitglied ist xx Jahre alt.

Abteilungen
Fußball xx Mitglieder
Handball xx Mitglieder
Senioren xx Mitglieder
Turnen xx Mitglieder
Volleyball xx Mitglieder
Wandern xx Mitglieder

Bisher konnte ich nur diese Daten rausziehen:

Mitglieder
Mitglieder insgesamt:
Männer:
Frauen:

Für Hilfe wäre ich sehr dankbar!

Gruß
Gerhard

[Jochen]

Hallo Gerhard,

ich geh mal davon aus, dass Du für die verschiedenen Abteilungen Rollen angelegt hast. Dann steht die Mitgliederzahl in der Listenübersicht.

Außerdem empfiehlt sich eine Rolle Mitglied, in der alle Mitglieder erfasst sind. Wenn Du dir zu dieser Rolle eine eigene Liste erstellst kannst Du diese nach Geburtsdatum sortieren lassen. Dann steht oben bzw. unten das älteste bzw. jüngste Mitglied. Einmal draufklicken und im Profil steht sogar das Alter ausgerechnet.

Die Durchschnitte berechnest Du glaub ich am schnellsten mit einer nach Exel exportierten Liste.

Gruß Jochen

[matzman2000]

Hi Gerhard,

erstelle Dir eine PHP Datei mit folgendem Code:

Code: Alles auswählen

<?php
include_once($_SERVER['DOCUMENT_ROOT']. "/admidio/adm_program/system/common.php");

$anzahl_mitglieder = 0;
$anzahl_maenner = 0;
$anzahl_frauen = 0;
$alter_mitglieder = 0;
$alter_maenner = 0;
$alter_frauen = 0;
$juengster = 200;
$aeltester = 0;

function Geburtstag($gebdat)
{
	$tag = substr($gebdat,8,2);
	$monat = substr($gebdat,5,2);
	$jahr = substr($gebdat,0,4);
	
	$jetzt = mktime(0,0,0,date("m"),date("d"),date("Y"));
	$gb = mktime(0,0,0,$monat,$tag,$jahr);
	$alter = intval(($jetzt - $gb) / (3600 * 24 * 365));
	
	return $alter;
}

$sql = "SELECT usr_id, row1id1.usd_value, row4id2.usd_value, row7id10.usd_value, row10id11.usd_value 
		FROM ". TBL_ROLES. ", ". TBL_CATEGORIES. ", ". TBL_MEMBERS. ", ". TBL_USERS. "
		LEFT JOIN adm_user_data row1id1 ON row1id1.usd_usr_id = usr_id 
		AND row1id1.usd_usf_id = 1 LEFT JOIN adm_user_data row4id2 ON row4id2.usd_usr_id = usr_id 
		AND row4id2.usd_usf_id = 2 LEFT JOIN adm_user_data row7id10 ON row7id10.usd_usr_id = usr_id 
		AND row7id10.usd_usf_id = 10 LEFT JOIN adm_user_data row10id11 ON row10id11.usd_usr_id = usr_id 
		AND row10id11.usd_usf_id = 11 
		WHERE rol_id = 3 OR rol_id = 4
		AND rol_valid = 1 
		AND rol_cat_id = cat_id 
		AND cat_org_id = 1 
		AND mem_rol_id = rol_id 
		AND mem_valid = 1 
		AND mem_usr_id = usr_id 
		AND usr_valid = 1 
		ORDER BY row1id1.usd_value ASC";

$result = $g_db->query($sql);

while($row = $g_db->fetch_array($result))
{
	$user = new User($g_db, $row['usr_id']);
	
	// Anzahl und Alter der Männer / Frauen
	$geschlecht = $user->getValue("Geschlecht");
	$geburtstag = Geburtstag($user->getValue("Geburtstag"));
	if($geschlecht == 1)
	{
		$anzahl_maenner++;
		$alter_maenner = $alter_maenner+$geburtstag;
		
	}
	else if($geschlecht == 2)
	{
		$anzahl_frauen++;
		$alter_frauen = $alter_frauen+$geburtstag;
	}
	if($geburtstag > $aeltester)
	{
		$aeltester = $geburtstag;
	}
	if($geburtstag < $juengster)
	{
		$juengster = $geburtstag;
	}
}

echo "Anzahl Mitglieder: ". ($anzahl_frauen+$anzahl_maenner);
echo "<br>";
echo "Anzahl Frauen: ". $anzahl_frauen;
echo "<br>";
echo "Anzahl Männer: ". $anzahl_maenner;
echo "<br>";
echo "Durchschnittsalter: ". round((($alter_maenner+$alter_frauen)/($anzahl_frauen+$anzahl_maenner)),2);
echo "<br>";
echo "Durchschnittsalter Frauen: ". round(($alter_frauen/$anzahl_frauen),2);
echo "<br>";
echo "Durchschnittsalter Männer: ". round(($alter_maenner/$anzahl_maenner),2);
echo "<br>";
echo "Jüngstes Mitglied: ". $juengster;
echo "<br>";
echo "Ältestes Mitglied: ". $aeltester;
echo "<br>";

?>

Was musst Du anpassen?
- Den include der common.php auf Dein Verezeichnis anpassen.
- Die IDs der Rollen Deiner Abteilungen in der Where Bedingung mit aufnehmen.
- Die rol_id in der Schleife mit auslesen und Deinen Abteilungen zuordnen, um die Anzahl der Mitglieder je Abteilung zu erfassen.

Hoffe das hilft etwas weiter...

Gruss,
Matze

[Jochen]

Hey Matze,

das richt ja förmlich nach einem neuen Plugin

Gruß Jochen

[Gerhard0964]

Hallo Matze,

super - danke!!!!!

Ich komme zwar heute nicht mehr dazu mich damit zu beschäftigen, aber das wird in den nächsten Tagen geschehen. Die Idee mit dem Plugin finde ich übrigens super, da diese Daten doch sicher noch von anderen gebraucht werden.

Viele Grüße
Gerhard

[matzman2000]

Hey Jochen,

nette Idee. So eine Art Statistik-Plugin.
Aber wo willst Du da anfangen? Den einen interessiert das Alter, den anderen die Anzahl der Personen in ein und derselben Strasse...

Das hier beschränkt sich ja tatsächlich auf Altersauswertungen - und selbst da fallen mir gelich noch eine ganze Hand voll von ein...

Vorschläge sind natürlich willkommen.

Gruss,
Matze

[Gerhard0964]

Hallo Matze,

nach gut drei Stunden bin ich jetzt soweit klar, dass ich im Grundzug verstehe, was Du mit der Abfrage erreichst. Soweit so gut - aaaaber: Das ist ja noch nicht alles, da sind noch einige Abfragen mehr, die ich einfach brauche. Ich muss zum Beispiel auch die Altersstruktur darstellen, Ehrungen nach Mitgliedsjahren, Vereinszugehörigkeit in fünf Jahresabständen etc.

Ich stimme da Jochen zu: Es sollte ein entsprechendes Plugin geben. Ja - ich weiß, dass jeder andere Angaben braucht. In einem Sportverein brauche ich aber jede Menge Angaben alleine für die jeweiligen Verbände, Unfallversicherungen etc. Ganz wichtig ist in dem Zusammenhang auch das Eintrittsdatum und auch die Mitgliedsnummer. Diese Angaben müssen zum Standard gehörenund einfach abzufragen sein. Es müssen ja nicht gleich Pflichtfelder sein.

Es wäre daher zu überdenken, ob die alte DB-Struktur nicht besser, da einfacher auszuwerten, war. Bei der Abfrage, die Du mir erstellt hast, braucht die DB ca 10 -15 Sekunden zum Antworten. In der alten DB hatte ich die Antworten quasi sofort.

Denkt mal bitte darüber nach, ob das nicht einfacher zu machen wäre.

Ansonsten vielen Dank für die Hilfe!
Ich bin mir aber sicher, dass ich nochmal darauf zurückgreifen muss, da ich noch immer nicht richtig mit der komplizierten Abfrage klarkomme.

Viele Grüße und nochmals vielen Dank
Gerhard

[fasse]

Hallo Gerhard,

10-15 Sekunden ist aber schon ziemlich viel und sollte eigentlich nicht durch so ein Statement verursacht werden. Selbst bei vielen Mitgliedern. Evtl. ist deine MySQL auf einem nicht so performaten Rechner.

Die alte Datenstruktur war zwar einfacher für die Stammdaten, allerdings überhaupt nicht flexibel. Viele gewünschte Funktionen (Felder sperren, Pflichtfelder) waren damit nicht möglich. Außerdem hatten wir damals schon die kompliziertere Struktur für die eigenen Felder, was dazu geführt hatte, das der Entwicklungsaufwand höher war, da immer 2 Systeme berücksichtigt werden mussten.

jetzt haben wir eine einheitliche flexible Lösung mit der wir viele Wünsche umsetzen könnten und auch noch weitere umsetzen werden. Ein zurück wird es wohl nicht geben.

Viele Grüße
Fasse

PS: Seit Admidio 2.0.5 kannst du auch Abfragen auf das Alter über unsere eigenen Listen machen >18j <30j liefert alle Mitglieder der Rolle zwischen 18 und 30 Jahren. Probier das mal aus.

[Gerhard0964]

Hallo Fasse,

der Server ist bei 1&1 und an und für sich recht schnell. Ich verstehe das mit der Flexibilität, aber leider habe ich nicht das große SQL-Wissen um mit der neuen DB klar zu kommen. Das Problem ist, dass ich Admidio nicht so verwende, wie Ihr Euch das vorstellt. Es läuft bei mir im Background und wird nur zur Mitgliederverwaltung für den Vorstand genutzt. Ich will daher auch nicht zu viele auf das System zugreifen lassen. Daher möchte ich z.B. Die zu ehrenden Mitglieder per einfacher SQL-Abfrage auslesen. MIt 1.x war das kein Problem - jetzt ist es für mich nahezu unmöglich.

Wenn es aber möglich ist, dann richtet bitte mal als Standard Mitgliedsnummer und Eintrittsdatum ein.

Ansonsten müsste ich Euch bitten mir bei den Abfragen zu helfen. Wenn das möglich ist DANKE!

Gruß
Gerhard

[Jochen]

Hallo,

oben hattest Du diese Daten "gefordert":

Mitgliederstatistik
Mitglieder
Mitglieder insgesamt:
Männer:
Frauen:

Durchschnittsalter
Mitglieder insgesamt xx Jahre
Männer xx Jahre
Frauen xx Jahre
Das jüngste MItglied ist x Jahre alt.
Das älteste Mitglied ist xx Jahre alt.

Abteilungen
Fußball xx Mitglieder
Handball xx Mitglieder
Senioren xx Mitglieder
Turnen xx Mitglieder
Volleyball xx Mitglieder
Wandern xx Mitglieder

Zu denen jetzt meiner Meinung nach auch Lösungen hier stehen. Jetzt klingt es auf einmal so als wäre das doch nicht alles. Was fehlt Dir denn noch?

Wenn es aber möglich ist, dann richtet bitte mal als Standard Mitgliedsnummer und Eintrittsdatum ein.

Das kannst Du bei den eigenen Profilfeldern machen, die Feldtypen Zahl und Datum stehen dort zur Verfügung.

Und noch ein Tipp: Füge in der der Datei adm_progam/modules/lists/lists.php in Zeile 294 mal ein

Code: Alles auswählen

echo $main_sql; exit();

Wenn Du Dir dann eine eigene Liste zusammen stellst bekommst Du das zugehörige SQL-Statement ausgegeben. Die Berechnung der Dauer einer Mitgliedschaft oder dem Durchschnitt eines alters hat mit der Abfrage direkt gar nichts zu tun. Das müsste nachher mit php erfolgen.

Gruß Jochen

[Gerhard0964]

Hallo Jochen,

ich glaube da liegt etwas quer - ich fordere nichts! Ich bitte um Hilfe. Und ja, ich brauche noch einiges. Ich stelle auf unsere HP für den Vorstand eine ständig aktuelle Statistik bereit. Und dazu muss ich nun mal die Daten auch auslesen und verarbeiten können.
Dazu gehören zum Beispiel die Termine, wann Mitglieder für eine bestimmte Vereinszughörigkeit geehrt werden sollen. Dazu hatte ich im alten Programm eine Abfrage erstellt die mir die Mitglieder mit 15, 25 und 50 Jahren ausgibt. Ohne, dass ich irgend etwas machen musste. Einfach nur die Seite öffnen. Weiter ist es in einem Verein schon wichtig zu wissen, wie sich die Alterstruktur darstellt. Auch dazu hatte ich eine Abfrage erstellt. Ich konnte genau sagen wieviele Mitglieder wir in verschiedenen Aölltersstufen haben.
Dann kommen noch die Abfragen, die für die Verbände gebraucht werden. Gesamtzahl der Mitglieder, was machen diese (Handball, Fußball, Turnen) usw.

Das alles war für mich(!) mit der alten DB lösbar. Mit der neuen, die ja alles in Value ausgibt aber leider nicht mehr. Ich brauche für diese Abfragen einfach Hilfe. Und darum bitte ich.

Was die selbst angelegten Felder mit Eintrittsdatum und Mitgliedsnummer angeht - klar kein Problem, habe ich auch gemacht. Ich gehe aber davon aus, dass dies eigentlich in jedem Verein Standartfelder sein dürften un denke, dass man diese nun halt auch grundsätzlich einbaut. Sollte aber auch nur ein Vorschlag sein.

Ich füge Hier mal ein paar Screenshots meiner Abfragen zur Verdeutlichung bei.

Viele Grüße
Gerhard