Sortieren über 2 Tabellen

[Thom-]

Guten Tag,

nach längerer Suche im inzwischen kompletten Internet wäre ich den Usern dieses Forums sehr zum Dank verpflichtet wenn diese mir bei folgendem Problem helfen könnten:

Für einen Teil meiner Projektarbeit gehört das Coden eines Forums dazu.
Auf der Seite mit der Auflistung der momentanen Themen sollen diese nach dem "last poster" sortiert werden.

Ich habe meine Tabellen wie folgt aufgebaut:

Tabelle topic:

id      forum_id      closed      pinned
1       1               0             1
2       1               1             1
3       1               0             0

[...]

Tabelle post:

id      topic_id      user_id      created                         title              text
1       1              1              2005-02-07 00:00:00       muh              blablubb
2       2              1              2005-02-07 00:00:00       knubbelkekse     JA WILLISCH !!!!!

Wie man bereits sieht, ist eine Ausgabe aller Topics, sortiert nach dem Datum des letzten Post leider nicht so einfach möglich.

Mein momentaner Code ohne die Sortierung lautet so:

PHP-Code:

    $getunpinned = $DB->query("SELECT * FROM topic WHERE pinned=0 AND forum_id=$show");

    while( $row = mysql_fetch_object($getunpinned) )
    {        
        echo 'DEBUG:Topicid: '.$row->id.' | <br />';
        $posts = $DB->anzahl("SELECT * FROM post WHERE topic_id=$row->id");
        
        $getfirstpost = $DB->query("SELECT p.title,u.username FROM post p,user u WHERE p.topic_id=$row->id AND p.user_id=u.id ORDER BY p.created");
        $firstpost = mysql_fetch_object($getfirstpost);
        
        $getlastpost = $DB->query("SELECT p.created,p.title,u.username FROM post p,user u WHERE p.topic_id=$row->id AND p.user_id=u.id ORDER BY created DESC");
        $lastpost = mysql_fetch_object($getlastpost);

[...]

}

[...] 


Idealerweise würde die Abfrage so lauten:
"SELECT * FROM topic ORDER BY [Letzter Post].created"

Nach einigem Rumsuchen habe ich folgende Methode probiert:
"SELECT post.* FROM post INNER JOIN topic ON post.topic_id=topic.id WHERE topic.pinned=0 AND topic.forum_id=$show ORDER BY post.created DESC"
(Wobei ich sämtliche $row->id's zu $row->topic_id umändern musste)
Diese Methode funktionierte auch, das Problem darin ist aber,dass er nunmal alle Posts nutzt, d.h. ich keinerlei Probleme habe solang ein Topic nur 1 Post hat, aber sobalt jemand darauf antwortet erscheint dies mehrmals.

Beispiel:

Thread1       posts:1       autor:keks         lastpost:2005-02-10 16:23:04
Thread2       posts:1       autor:knubbel     lastpost:2005-02-09 16:23:34
Thread3       posts:2       autor:baer         lastpost:2005-02-09 01:27:00
Thread4       posts:2       autor:mookoo     lastpost:2005-02-08 00:00:00
Thread4       posts:2       autor:mookoo     lastpost:2005-02-08 00:00:00
Thread3       posts:2       autor:baer         lastpost:2005-02-09 01:27:00

Wie man sieht erscheinen die 2 Letzten Posts doppelt, in umgekehrter Reihenfolge und irgendwie sinnlos.
Ich bin langsam mit meinem Rat am ende, habe schon ein GROUP BY probiert ("SELECT post.* FROM post INNER JOIN topic ON post.topic_id=topic.id WHERE topic.pinned=0 AND topic.forum_id=$show GROUP BY post.topic_id ORDER BY post.created DESC"), doch auch das macht fehler, es werden dann nämlich nur die ersten 2 und die letzten 2 Einträge angezeigt, d.h. die Sortierung ist falsch (bei den letzten 2 kommt zuerst der mit 2005-02-08 und dann der mit 2005-02-09)

Ich weiß es ist evtl etwas viel verlangt und schlecht beschrieben, ich würde mich dennoch unheimlich über Hilfe freuen, ich bin momentan nämlich mit meinem Latein am ende.

Mit freundlichen Grüßen
Thomas

[feuervogel]

wie wäre es, wenn du mit DISTINCT nur unterschiedliche ids raussuchst...

[Marilu]

Du mußt die Abfrage mit GROUP BY erweitern, um die doppelten Einträge zu eliminieren. Poste doch mal einen Dump zum rumspielen.

[Thom-]

Guten Morgen,

ich danke erstmal für die Beiträge :]

Inzwischen steige ich leider irgendwie da nicht mehr durch.
Habe meine Tabelle geleert und Systematisch Topics und Posts eingefügt.
Das Problem mit den Doppelten Einträgen existiert zwar nicht mehr, aber ich habe herausgefunden das er nicht nach dem ältesten Post sortiert, sondern nach dem ersten Post.

Ich weiß ehrlichgesagt nicht wo das Problem ist, habs auch mit nem zusätzlichen distinct dazu Probiert, aber evtl. sehe ich ja den Wald vor lauter Bäumen nicht.

Hier erstmal ein link zum dump:
Dump

Und ein link zu einemnem Screenshot, welches das Problem evtl besser erläutert:
Screenshot

Ich danke schonmal für die investierte Zeit

mit freundlichen Grüßen
Thomas

[Marilu]

Ich kann die Tabellen nicht erzeugen, es hängt am COLLATE.

[Thom-]

Zitat von Marilu

Ich kann die Tabellen nicht erzeugen, es hängt am COLLATE.

Hrm, keine Ahnung, hab den Dump direkt vom phpMyAdmin so bekommen.
Man kann aber glaub den hinteren Teil (also den Teil hinter der abschließenden Klammer eines Creates) weglassen.

Aus

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

mach

);

korrigiert mich falls ich mich irre.
Frage mich ohnehin für was dies gut ist :]

mit freundlichen Grüßen
Thomas

[Marilu]

Welche Spalten (aus welcher Tabelle) willst Du letztendlich mit Deiner Abfrage anzeigen?

[Thom-]

Im Endeffekt möchte ich alle Topics auflisten lassen, welche aber nach dem created-Datum ihrer letzten Posts sortiert sein sollen.

[xabbuh]

PHP-Code:

<?php
    $sql = "SELECT
                p.id,
                p.topic_id,
                p.user_id,
                p.created AS datum,
                p.title,
                p.text,
                t.id,
                t.forum_id,
                t.closed,
                t.pinned
            FROM
                post p
            LEFT JOIN
                topic t
            ON
                t.id = p.topic_id
            GROUP BY
                p.topic_id
            ORDER BY
                YEAR(p.created),
                MONTH(p.created),
                DAYOFMONTH(p.created)";
?>

[Thom-]

Guten Tag,

erstmal möchte ich mich bei dir bedanken xabbuh für die Mühe.
Es klappt auch soweit wie man die Threads in normaler Reihenfolge anzeigen möchte.
Möchte man diese hingegen von hinten anzeigen (also mit nem DESC dran) gehts schief.

Ich weiß langsam nicht so recht ob sich die mühe noch lohnt, oder ob man nicht einfach an jedes Topic ein LastPostDate anfügen sollte, dies würde wohl auch bei großer Datenmenge wesentlich schneller sein als ein rekursives suchen nach dem lastpost.

Ich danke hiermit nochmals für die Hilfe.
Schade das es nicht geklappt hatte wie ich dachte :]

einen schönen Abend noch,
Thomas