Benutzer nach Registrierungsdatum filtern

Hartmut Kunst · 27. November 2015

Hallo,

kaum hier und schon die erste Frage...

Ich habe eine MySQL-Datenbank mit verschiedenen Tabellen:

Tabelle1 = xyz_users
Hieraus brauche ich die User-ID (id) und das Registrierungsdatum (registerDate)
T
abelle 2 = xyz_user_posts
Hieraus beziehe ich ebenfalls die User-ID (userid) und die Anzahl der Postings im Forum (posts)

id und userid sind zu jedem Benutzer identisch

Nun möchte ich in eine dritte Tabelle alle Benutzer auflisten, die langer als 90 Tage registriert sind UND die noch keinen Post haben.

Das Problem, dass ich dabei habe, ist der Ausdruck das Registrierungsdatum und das Interval >= 90 Tage in Zusammenhang zu bringen, d.h. es sollen nur die Benutzer angezeigt werden, die länger als 90 Tage registriert sind und 0 Posts haben.

Nur die User mit 0 Posts kriege ich hin, die stehen ja in einer Tabelle, aber das Registrierungsdatum steht in der anderen und nach 90 Tagen zu filtern, na ja..., ich bin eben Anfänger

Kann mir jemand helfen?

cottton · 27. November 2015

Kommt draus an, wie Dein bisheriger Code aussieht.

PHP

<?php
// davon ausgegangen, dass registerDate im format Y-m-d H:i:s vorliegt
// 90 zuvor per php berechnen: 
$daysBack = 90;
$daysBefore = date('Y-m-d H:i:s', strtotime('-' . abs((int)$daysBack) . ' day', time()));
$sql = "select ... where registerDate < :days_before"; // < oder <= 
//(jetzt per mysqli_ oder PDO die Query ->prepare(), Parameter übergeben/binden, execute)




// 90 zuvor per sql berechnen:
$daysBack = 90;
$sql = "select ... where registerDate < DATE_SUB(NOW(), INTERVAL :days_back DAY)"; // < oder <= 
//(jetzt per mysqli_ oder PDO die Query ->prepare(), Parameter übergeben/binden, execute)

Alles anzeigen

Hartmut Kunst · 29. November 2015

Hallo cottton,

das klappt schon mal, vielen Dank.

Mein Code sieht so aus:

SQL

SELECT
	j.registerDate, k.userid, j.username, j.id, k.posts, j.lastvisitDate, j.email
FROM 
	hwf35_users j INNER JOIN hwf35_kunena_users k ON (j.id=k.userid) 
WHERE 
	j.registerDate <= DATE_SUB(CURDATE(), INTERVAL 180 DAY) 
AND 
	j.lastvisitDate <= DATE_SUB(CURDATE(), INTERVAL 179 DAY) 
AND 
	k.posts<1 
ORDER BY
	j.lastvisitDate

Alles anzeigen

Ich habe anstelle DATE_SUB(NOW), DATE_SUB(CURDATE) gewählt, da ich die Minuten und Sekunden nicht berücksichtigen muss

Damit werden die Benutzer, auf die das zutrifft, nun zwar gefiltert und in einer temporären Tabelle im phpmyAdmin angezeigt, aber perfekt wäre es natürlich, wenn ich die auch gleich in eine permanente Tabelle eintragen könnte, um sie dann anschließend komplett löschen zu können
Da habe ich aber keine Idee, wie ich das hinbekomme, geht das überhaupt?

Hast Du da einen Hinweis für mich?

cottton · 29. November 2015

Wenn es eine wirklich temporärer Tabelle ist, dann empfehle ich Memory-Table (ram / daten beachten).
https://dev.mysql.com/doc/refm…emory-storage-engine.html

Wenn nicht genug RAM ... dann temp table
http://dev.mysql.com/doc/refman/5.7/en/create-table.html
(beachte, dass diese tbl nach der Session weg sind. Im Zweifel nen normalen Table)

Und da die Tabelle ähnlich aufgebaut sein müsste, mach CREATE TABLE LIKE Sinn.
https://dev.mysql.com/doc/refm…en/create-table-like.html

bsp:

SQL

-- create mem table like
CREATE TABLE 
    hwf35_users_temp_mem -- bissl lang, nenne sie wie du willst =)
LIKE 
    hwf35_users -- aus deiner query sehe ich, dass du die daten von dieser tabelle brauchst.
    -- und anstatt k.userid kannst du ja auch j.id select-en (oder auch nicht, denn die sind ja gleich - siehe ON (j.id=k.userid) )
ENGINE = MEMORY;




-- oder create temp table like
CREATE TEMPORARY TABLE
    hwf35_users_temp
LIKE
    hwf35_users;







-- oder create table like
CREATE TABLE 
    hwf35_users_temp
LIKE
    hwf35_users;

Alles anzeigen

Und dann kannst Du die selektierten Daten in die neue Tabelle schieben:
http://dev.mysql.com/doc/refman/5.7/en/insert-select.html

SQL

-- insert into select
INSERT INTO 
    hwf35_users_temp -- die neue tabelle (tbl, temp tbl, oder mem tbl)
SELECT
	j.registerDate, k.userid, j.username, j.id, k.posts, j.lastvisitDate, j.email
FROM 
	hwf35_users j INNER JOIN hwf35_kunena_users k ON (j.id=k.userid) 
WHERE 
	j.registerDate <= DATE_SUB(CURDATE(), INTERVAL 180 DAY) 
AND 
	j.lastvisitDate <= DATE_SUB(CURDATE(), INTERVAL 179 DAY) 
AND 
	k.posts<1 
ORDER BY
	j.lastvisitDate

Alles anzeigen

Evtl musst Du die Tabelle doch selbst definieren, wenn zB ein Feld X einen Wert benötigt, aber Du diesen nicht im SELECT selektierst.

Hartmut Kunst · 29. November 2015

Hallo cottton,

irgendwie komme ich nicht weiter. Das Erzeugen der Tabelle hat geklappt, ich habe die Spalten entsprechend der Abfrage angepasst.

Wenn ich aber die Abfrage starte, erhalte ich:

Zitat

Fehler
SQL-Befehl: [Blockierte Grafik: https://www.strato.de/apps/phpmyadmin/strato-app/myadmin4/themes/dot.gif]
INSERT INTO
hwf35_users_for_delete -- die neue tabelle
SELECT
j.registerDate, j.username, j.id, k.posts, j.lastvisitDate, j.email
FROM
hwf35_users j INNER JOIN hwf35_kunena_users k ON (j.id=k.userid)
WHERE
j.registerDate <= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
AND
j.lastvisitDate <= DATE_SUB(CURDATE(), INTERVAL 179 DAY)
AND
k.posts<1
ORDER BY
j.id

MySQL meldet:
#1062 - Duplicate entry '2011' for key 'PRIMARY'

Alles anzeigen

Dann habe ich mal ausschließlich aus hwf35_users abgefragt, das Ergebnis ist das gleiche:

Zitat

Fehler
SQL-Befehl: [Blockierte Grafik: https://www.strato.de/apps/phpmyadmin/strato-app/myadmin4/themes/dot.gif]
INSERT INTO
hwf35_users_for_delete -- die neue tabelle
SELECT
registerDate, username, id, lastvisitDate, email
FROM
hwf35_users
WHERE
registerDate <= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
AND
lastvisitDate <= DATE_SUB(CURDATE(), INTERVAL 179 DAY)
ORDER BY
id

MySQL meldet:
#1062 - Duplicate entry '2010' for key 'PRIMARY'

Alles anzeigen

Also hat es, nach meiner Logik, nichts mit dem Abfragen aus zwei Tabellen zu tun...

Es ist aber nur 'id' als Primärschlüssel gesetzt, da gibt es keine '2011' .

Da stehe ich auf dem Schlauch

Ich hoffe auf Dich

Gruß
Hartmut

cottton · 29. November 2015

Die Reihenfolge ist hier das Problem, glaub ich.
Du selektierst
regDate, userName, id, ...
Also schiebt er sicherlich das regDate in die erste Column -- id.

Dann setze einfach die Values
wie in etwa

SQL

INSERT INTO
hwf35_users_for_delete -- die neue tabelle
(registerDate, username, id, posts, lastvisitDate, email) -- die rehenfolge "bekannt machen"
SELECT
j.registerDate, j.username, j.id, k.posts, j.lastvisitDate, j.email
FROM
hwf35_users j INNER JOIN hwf35_kunena_users k ON (j.id=k.userid)
WHERE
j.registerDate <= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
AND
j.lastvisitDate <= DATE_SUB(CURDATE(), INTERVAL 179 DAY)
AND
k.posts<1
ORDER BY
j.id

Alles anzeigen

Hartmut Kunst · 29. November 2015

Hallo cottton,

BINGO! Das war's tatsächlich.

Eigentlich doch logisch, da muss ich "meine Logik" noch mal überdenken

Vielen Dank!

Wenn ich aber nun einen User aus dieser Tabelle lösche, wird er in der Originaltabelle noch nicht gelöscht, oder?

Kann man das verknüpfen?

wolf · 29. November 2015

Ja, du kannst dem Server sagen das er bei bestimmten events ein hinterlegtes SQL ausführt..
Aber genauer weiß ich es gerade nicht

cottton · 29. November 2015

Ja, was wolf sagt -- Trigger (von mir nicht empfohlen), oder Fremdschlüssel (foreign key).
Das müsste man bei der Erstellung der neuen Tabelle beachten.

Weiß ja nicht, was die neue Tabelle für einen Zweck erfüllt.
Ich tippe mal: Du willst sicherheitshalber erst alle Datensätze in einer extra Table haben, um sie zu kontrollieren.
Dann, wenn alles ok ist, sollen diese gefilterten/gefundenen Datensätze in der originalen Tabelle gelöscht werden.

Wenn dem so ist, und Du GANZ sicher gehen musst, dann
- erstelle neue (nicht temporäre) Tabelle tbl_name_29112015 (mit alles Column/Feldern die wichtig sind (oder allen Feldern =)))
- lege dabei eine zusätzliche Column an: deleted_at oder delete_date not null, datetime format, default '0000-00-00 00:00:00'
- kontrolliere die gefilterten Datensätze der neuen Tabelle
- wenn gelöscht werden kann, dann setze delete_date auf NOW() und lösche in den originalen Tabellen

Vorteil:
Du hast Backups den Datensätze, welche als gelöscht markiert sind.
Im Notfall könntest Du diese wiederherstellen.
EDIT: Du könntest auch die kompletten Tabellen kopieren. Das kann aber bissl blöd sein, wenn dort sehr viel Datensätze vorhanden sind.

DELETE:
Du hattest 2 Tabellen. Evtl musst Du dann auch auf beiden löschen:
siehe "Multiple-Table Syntax" http://dev.mysql.com/doc/refman/5.7/en/delete.html

Fremdschlüssel (ON DELETE CASCADE) könnten hier auch helfen. Aber dazu müsste man erstmal wissen
- wozu die neue Tabelle eigtl dient
- ob die vorhandenen Tabellen in der Struktur geändert werden dürfen

Hartmut Kunst · 30. November 2015

Hallo cottton,

Was ist der Zweck dieser Tabelle?

Eigentlich geht es mir darum, wie Du schon schriebst, aus meinem Benutzerstamm die Benutzer herauszufiltern, die sich lediglich
registriert, aber sonst seitdem nie irgendetwas gepostet haben. Hier in der vorhandenen Tabelle herumzustochern, ist wirklich mühsam.

Dann diese noch einmal kontrollieren, und dann in der bestimmten Tabelle (hwf35_users) löschen können; denn dann wären Sie auch aus der hwf35_kunena_users entfernt, da diese offenbar miteinander verknüpft sind(wenn ich manuell einen aus hwf35_users lösche, ist der auch in hwf35_kunena_users verschwunden)

Das Herausfiltern hat ja mit Deiner Hilfe auch schon geklappt, und ich suche schon den ganzen Tag (na ja, fast..) aber da finde ich nichts, was mir weiterhilft.

Ich suche nach einer Möglichkeit, bestimmte Kriterien (in diesem Fall wäre wohl die id am geeignetsten, username u. email gingen aber auch) miteinander zu vergleichen und bei Übereinstimmung in hwf35_users zu löschen.
Die Strukturen der Tabellen zu ändern halte ich für gefährlich, da ein ganzes CMS dran hängt und ich natürlich nicht weiß, was dann irgendwann einmal passiert.

cottton · 30. November 2015

Dann gibt es bei den Tabellen sicherlich schon die Verknüpfung per Fremdschlüssel.
Das ist gut.

Was Du jetzt egitl nur machen musst:

SQL

DELETE FROM hwf35_users WHERE ID IN (
    SELECT id FROM hwf35_users_for_delete 
);

Weiß grad nicht, ob ich auf die Schnelle was vergessen hab. Aber im Prinzip war es das schon.

Würde allerdings - wenn benötigt - noch etwas "verbessern":

SQL

-- die tmp tbl updaten - davon ausgegangen, du hast ein feld date_delete angelegt
-- und davon ausgegangen, dass alle im tmp tbl ok sind - also gelöscht werden sollten/können
UPDATE hwf35_users_for_delete  SET date_delete = NOW() WHERE date_delete = '0000-00-00 00:00:00';




-- die sind also nun alle als zu löschen/gelöscht makiert ...
-- dann löschen wir die auch aus der orig table:
DELETE FROM hwf35_users WHERE ID IN (
    SELECT id FROM hwf35_users_for_delete WHERE date_delete != '0000-00-00 00:00:00'
);

Alles anzeigen

Evtl noch anpassen.

Hartmut Kunst · 1. Dezember 2015

Hallo cottton,

DAS IST DIE WUCHT IN TÜTEN

Ich habe das alles jetzt mal so zusammengefügt:

SQL

-- ZUERST Daten aus der Benutzertabelle hwf35_users auswählen (unter Berücksichtigung der Posts aus hwf35_kunena_users)
INSERT INTO
hwf35_users_for_delete -- die neue tabelle
(id, username, email, registerDate, lastvisitDate, posts) -- die Reihenfolge "bekannt machen"
SELECT 
	j.id, j.username, j.email, j.registerDate, j.lastvisitDate, k.posts
FROM 
	hwf35_users j INNER JOIN hwf35_kunena_users k ON (j.id=k.userid) 
WHERE 
	j.registerDate <= DATE_SUB(CURDATE(), INTERVAL 150 DAY) 
AND 
	j.lastvisitDate <= DATE_SUB(CURDATE(), INTERVAL 149 DAY) 
AND 
	k.posts<1;

-- Dann Daten aus der Benutzertabelle hwf35_users löschen
DELETE FROM 
	hwf35_users 
WHERE ID IN (
	SELECT id FROM hwf35_users_for_delete 
);-- Die Daten sind in der Tabelle hwf35_users_for_delete weiterhin enthalten (mit timestamp 0000-00-00 00:00:00)




-- Nun den timestamp der gerade gelöschen Benutzer auf NOW() setzen
UPDATE 
	hwf35_users_for_delete  
SET 
	date_delete = NOW() 
WHERE 
	date_delete = '0000-00-00 00:00:00';

Alles anzeigen

Beim nächsten Durchlauf werden zu löschende Benutzer lediglich zu 'hwf35_users_for_delete' hinzugefügt und dann in 'hwf35_users' gelöscht.
Das funzt schon mal richtig gut und der Vorteil ist, dass die User in der tabelle 'hwf35_users_for_delete' erhalten bleiben, also auch notfalls wieder hergestellt werden könnten.

Das verfeinere ich nun noch ein wenig, so dass ich am Ende vielleicht dahin komme, alle Spalten aus 'hwf35_users' vor dem Löschen zu sichern.

Ich bedanke mich recht herzlich für Deine Unterstützung

(aber ich komme bestimmt in Kürze mit einer neuen Frage

Viele Grüße
Hartmut

cottton · 1. Dezember 2015

Hab da noch paar Sachen für Dich:
- transactions
- optimize table
- query log

Transaktionen:
Das Prinzip ist einfach. Du willst 2 Aktionen durchführen: löschen, und updaten.
Wenn das eine nicht geht, soll das andere nicht sein.
Dafür gibts Rollback.

Wenn Du ohne PHP arbeitest (also die Queries direkt auf dem Server ausführst), dann siehe hier: http://dev.mysql.com/doc/refman/5.7/en/commit.html
ROLLBACK siehe: http://stackoverflow.com/a/9327843

Falls Du mysqli_ nutzt, dann siehe hier: http://php.net/manual/de/mysqli.begin-transaction.php
Und im Falle PDO: http://php.net/manual/de/pdo.begintransaction.php

Optimize table kann ich gerade nur von innodb sprechen
Wenn Du viele (hunderte/tausende) Datensätze gelöscht hast, dann ~~kann~~ wird es sein, dass die Tabelle größer wird, oder gleich groß bleibt.
Warum? Es wurde ja gelöscht.
Ja, aber innodo Tabelle sammeln Daten zur Wiederherstellung, falls was schief geht (und das ist gut).
Wendest Du nun "OPTIMIZE TABLE mytablename;" an, dann reorganisiert der Server die Tabelle. Die nicht mehr benötigten Daten werden entfernt.
(hatte das Problem mal mit einer Tabelle die ~10 GB groß war, und nach dem Löschen um die 30 GB :D)

Query log
Du sagtest, dass Du aus einer Tabelle löschst, und in der anderen Tabelle wird gleich mit gelöscht.
Um genau zu sehen, was da alles passiert, kannst Du die Query log einschalten.
Dadurch siehst Du jede Query, die ausgeführt wird.
Aber Vorsicht beim laufenden Betrieb -- wenn Du nen Server hast, der 20 Queries per sec bekommt, dann bekommst Du 20 Queries (oder mehr) geschrieben.
Am besten auf einem Testsystem nutzen, auf dem nur Deine Aktionen ausgeführt werden

SQL

-- vorbereiten
	SET global general_log = 1; -- einschalten
	SET global log_output = 'table'; -- output auf einen table setzen (ohne, wird in file geschrieben)
	truncate mysql.general_log; -- die table gleich mal leeren 




-- jetzt deine delete query ausführen ...




	select * from mysql.general_log; -- alle mitgeschriebenen queries anzeigen




-- vor dem nächsten test wieder leeren
	truncate mysql.general_log;




-----------
-- wenn fertig, query log wieder abschalten !
	SET global general_log = 0;
	truncate mysql.general_log;

Alles anzeigen

So kannst Du schön sehen, was noch alles passiert, wenn Du einen User löschst. Evtl siehst Du dann, was Du noch in die neue Tabelle schreiben/kopieren musst -- als Backup.

Hartmut Kunst · 2. Dezember 2015

Hallo cottton,

SUPER!

Vielen Dank für die Tipps.

Da werde ich mich mal heranwagen

Ich dachte mir ohnehin schon, dass ich mehr Daten in die neue tabelle hwf35_users_for_delete erfassen muss, z.B. schreibe ich die zu löschenden Benutzer vorher an, und da sind ann einige dabei, die reagieren tatsächlich und bitten darum, trotz 0 Posts nicht gelöscht zu werden. Dieser Bitte komme ich natürlich nach, also muss ich im Backend des CMS eine Notiz hinterlegen, diese aus der entsprechenden Tabelle ebenfalls in die neue Tabelle einpflegen und dann das Löschen vom "Nichtvorhandensein" einer entsprechenden Notiz abhängig machen.
Also, es gibt noch einges zu tun und, vor Allem, zu lernen

Dabei sind diese Tipps hier natürlich wirklich Klasse.

Viele Grüße
Hartmut
Hallo,

ich komme irgendwie nicht weiter:

Ich habe, wie erwähnt, im Backend des CMS eine neue Kategorie für Notizen angelegt, wo ich eintrage, dass der User nicht gelöscht werden möchte

Diese Kategorie frage ich also ebenfalls ab:

SQL

INSERT INTO
hwf35_users_for_delete -- die neue tabelle
(id, username, email, registerDate, lastvisitDate, posts) -- die rehenfolge "bekannt machen"
SELECT 
	j.id, j.username, j.email, j.registerDate, j.lastvisitDate, k.posts, n.catid, 
FROM 
	hwf35_users j INNER JOIN hwf35_kunena_users k ON (j.id=k.userid) AND INNER JOIN hwf35_user_notes ON (j.id=n.user_id)
WHERE 
	j.registerDate <= DATE_SUB(CURDATE(), INTERVAL 130 DAY) 
AND 
	j.lastvisitDate <= DATE_SUB(CURDATE(), INTERVAL 129 DAY) 
AND 
	k.posts<1
AND	
	n.catid = 66;

Alles anzeigen

Das funzt schon mal... (ist ja auch nicht so schwer gewesen, die eine Bedingung hinzuzufügen

Ich habe im Backend des CMS die Möglichkeit, eine Massenmail an Benutzer einer bestimmten Kategorie zu schicken. Hierzu, so dachte ich mir, wäre es hilfreich, eine weitere Benutzergrußße anzulegen, und den "Löschkandidaten" diese Gruppe -zumindest vorübergehend- zuzuweisen. Also habe ich eine neue Gruppe erstellt.

Nun haben ja in meiner Tabelle 'hwf35_users_for_delete' alle neue hinzugefügten Benutzer in der Spalte 'date_delete' '0000-00-00 00:00:00' stehen.

Jetzt möchte ich diesen Benutzern in der Tabelle '..._user_usergroup_map' die group_id 10 zuweisen:

Zunächst ändere ich also die group_id in der Tabelle '...users_for_delete'

SQL

UPDATE 
	hwf35_users_for_delete  
SET 
	group_id = 10 
WHERE 
	date_delete = '0000-00-00 00:00:00';

Nun möchte ich group_id in der '...user_usergroup_map' für eben diese Benutzer ebenfalls aktualisieren:

SQL

UPDATE 
	hwf35_user_usergroup_map
SET
	group_id = 10
WHERE 
	group_id IN (
	SELECT group_id FROM hwf35_users_for_delete = '10';
)

...und das funzt nicht
Fehler: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'
)' at line 7

Folgendes probiert:

SQL

UPDATE 	hwf35_user_usergroup_mapSET	group_id = 10WHERE 	group_id = 10 IN (	SELECT group_id FROM hwf35_users_for_delete);

Da gibt es keine Fehlermeldung, aber es wird nichts gemacht.

Ich habe auch noch einige andere Varianten durch, aber es gibt immer Fehlermeldungen oder es passiert einfach nichts.

SQL

UPDATE 	hwf35_user_usergroup_mapSET	group_id = 10WHERE(SELECT id  FROM hwf35_users_for_delete WHERE id = hwf35_user_usergroup_map.user_id AND(SELECT idFROM hwf35_users_for_delete WHERE date_delete = '0000-00-00 00:00:00' ))MySQL meldet:   #1242 - Subquery returns more than 1 row

Liege ich so dermaßen daneben?

Gruß
Hartmut

Benutzer nach Registrierungsdatum filtern

Jetzt mitmachen!

Teilen