SQL Abfrage mit Zeit aus einer anderen Spalte

Felix28

Benutzer
Beiträge
6
Hallo.

Ich habe folgendes Problem:

Ich habe einmal die Tabelle houses und die Tabelle players.
In houses gibt es die Spalte pid und in players die Spalte playerid, welche beide gleich sind (also ein Vergleichswert beinhalten).

Jetzt möchte ich alle Einträge in houses löschen, wenn last_seen (in players) von der playerid X 70 Tage her ist.

Im Moment löse ich das ganze über zwei Abfragen (in PHP).
PHP:
$query = $pdo_arma->prepare("SELECT * FROM houses");
        $query->execute();
        
        while($row = $query->fetch()) {
            $pid = $row['pid'];
            
            $query_select = $pdo_arma->prepare("SELECT * FROM players WHERE playerid = '" . $pid . "'");
            $query_select->execute();
            
            if($query_select->rowCount() > 0) {
                $fetch = $query_select->fetch();
                
                if(calculate_days_between_dates($fetch['last_seen']) >= 70) {
                  // CODE BEI ERFOLG
                }
            }
        }

Die Funktion ist schon alt und stammt noch aus den Anfängen meiner PHP Kenntnisse.

Ich hoffe ihr könnt mir helfen und ich freue mich auf eure Unterstützung :)
 
Werbung:
Code:
SELECT * FROM houses INNER JOIN players ON players.playerid=houses.pid WHERE players.last_seen >= DATE_ADD(NOW(), INTERVAL -60 DAY);
Leider haut er mir da auch Ergebnisse raus, welche von gestern sind...
 
normal bei Deiner Berechnung ;-)

players.last_seen >= DATE_ADD(NOW(), INTERVAL -60 DAY);

ziehe von heute 60 ab, ist 2020-02-07. Du selektierst nach Werten größer als dieses Datum.

Davon abgesehen ist nicht klar, wie die Tabellen zusammenhängen. Das soll ja offenbar eine relationale Beziehung über diese ID sein. Welche Tabelle hat diese Spalte als PK und welche als FK? Können mehrere Werte mit der id in houses sein, aber mit unterschlichem Datum? Wir können nur raten ...
 
In houses können mehrere Ergebnisse mit einer pid sein. In players nur eine mit playerid (playerid = pid).
Die last_seen Spalte befindet sich in players und ist ein TIMESTAMP.

(Generell ist das Problem, dass es ein System ist, was als Framework herausgegeben wird und nicht wir es nicht bearbeitet haben.

HOUSES TABLE
Screenshot_52.png

PLAYERS TABLE
Screenshot_53.png
Screenshot_54.png
(Ausschnitt)

Aber danke für den Hinweis mit größer und kleiner (hatte es nur aus dem oberen Code kopiert und nicht nachgedacht, dass ich die Werte getauscht hatte)
 
Code:
test=*# \d players;
                Table "public.players"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 id        | integer |           | not null |
 name      | text    |           |          |
 last_seen | date    |           |          |
Indexes:
    "players_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "houses" CONSTRAINT "houses_pid_fkey" FOREIGN KEY (pid) REFERENCES players(id)

test=*# \d houses ;
               Table "public.houses"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 pid    | integer |           |          |
 name   | text    |           |          |
Foreign-key constraints:
    "houses_pid_fkey" FOREIGN KEY (pid) REFERENCES players(id)

\d: extra argument ";" ignored
test=*# select * from players ;
 id | name  | last_seen  
----+-------+------------
  1 | Name1 | 2020-01-01
  2 | Name2 | 2020-02-01
  3 | Name3 | 2020-03-01
  4 | Name4 | 2020-04-01
(4 rows)

test=*# select * from houses ;
 pid |  name   
-----+---------
   1 | house 1
   1 | house 1
   1 | house 1
   2 | house 2
   3 | house 3
   4 | house 4
(6 rows)

test=*# alter table houses drop constraint houses_pid_fkey, add constraint houses_pid_fkey foreign key (pid) references players on delete cascade;
ALTER TABLE

test=*# delete from players where last_seen < (current_date - 60);
DELETE 2

test=*# select * from players ;
 id | name  | last_seen  
----+-------+------------
  3 | Name3 | 2020-03-01
  4 | Name4 | 2020-04-01
(2 rows)

test=*# select * from houses ;
 pid |  name   
-----+---------
   3 | house 3
   4 | house 4
(2 rows)
 
  • deine Tabellen stehen in einer FK-Beziehung. Leider bildest Du das nicht ab
  • deine Tabellen sind über ein gemeinsammes Feld verbunden. Leider verwendest Du da verschiedene Datentypen.

Allerdings hatte ich Dein Problem wohl auch etwas falsch verstanden.

Code:
test=# select * from players ;
 id | name  | last_seen  
----+-------+------------
  1 | Name1 | 2020-01-01
  2 | Name2 | 2020-02-01
  3 | Name3 | 2020-03-01
  4 | Name4 | 2020-04-01
(4 rows)

test=*# select * from houses ;
 pid |  name   
-----+---------
   1 | house 1
   1 | house 1
   1 | house 1
   2 | house 2
   3 | house 3
   4 | house 4
(6 rows)

test=*# select id from players where last_seen < (current_date - 60);
 id
----
  1
  2
(2 rows)

test=*# delete from houses where pid in (select id from players where last_seen < (current_date - 60));
DELETE 4
test=*# select * from houses ;
 pid |  name   
-----+---------
   3 | house 3
   4 | house 4
(2 rows)

test=*# select * from players ;
 id | name  | last_seen  
----+-------+------------
  1 | Name1 | 2020-01-01
  2 | Name2 | 2020-02-01
  3 | Name3 | 2020-03-01
  4 | Name4 | 2020-04-01
(4 rows)

test=*#
 
Ich habe einmal versucht anhand der richtigen Tabelle deine Befehle einzugeben.

Leider führte select * from players where last_seen < (current_date - 60); zu keinem Eintrag.

Ich habe das ganze jetzt einmal angepasst (last_seen ist ein timestamp und kein Datum).
Code:
select * from houses where pid in (select playerid from players where last_seen < (CURRENT_TIMESTAMP() - INTERVAL 60 DAY))

SELECT CURRENT_TIMESTAMP() - INTERVAL 60 DAY habe ich jetzt einmal probiert und gibt mir auch einen richtigen Timestamp aus.
Leider funktioniert das ganze in Kombination mit dem SQL Befehl nicht.
 
Werbung:
Zurück
Oben