komplizierte SQL-Abfrage

[Marc21]

Liebe Forumsteilnehmer,

Ich habe ein Problem, das ich nicht alleine bewältigen kann und das meiner Meinung auch nicht gerade trivial ist. Ich hoffe Ihr könnt mir weiterhelfen...

Problem:
Ich habe eine SQL-Tabelle (ich nenne sie jetzt mal index-Tabelle) in der die Namen von mehreren anderen SQL-Tabellen stehen. Alle SQL-Tabellen die in der index-Tabelle stehen sind gleich aufgebaut. Jetzt möchte ich eine Abfrage über alle in der der index-Tabelle stehenden Tabellen starten, die mir die 5 Datensätze selektiert. Diese 5 Datensätze sollen nach einem Zahlenwert absteigend übergreifend über alle Tabellen selektiert werden.

also im Prinzip:
$abfrage = "SELECT * FROM alle Tabellen in der index-Tabelle ORDER BY Zahlenwert DESC LIMIT 5";

Beispiel:

Inhalt der index-Tabelle:
id | name
-----------
1  | name1
2  | name2
3  | name3

Inhalt der name2-Tabelle
id | zahlenwert
-----------
21 | 108
22 | 5
23 | 180

Inhalt der name2-Tabelle
id | zahlenwert
-----------
21 | 14
22 | 500
23 | 259

Inhalt der name3-Tabelle
id | zahlenwert
-----------
31 | 59
32 | 405
33 | 9

Es sollen also 21,23,22,23,32 augegeben werden!

Bitte helft mir...

Danke im Vorraus
Marc21

[CirTap]

Lieber Forumsanfrage,
also entweder ist deine Fragestellung flasch, das eigentliche Ergebnis ein adneres, oder du hast dich schlicht an einige Stellen vertippt (2x tabelle name2):

Es sollen also 21,23,22,23,32 augegeben werden!

mal angenommen es ginge "on-the-fly" die Namen von Tabellen als Variablen in einer SQL Abfrage "aus sich selbst heraus" zu bestimmen und das Ergebnis wäre dann so eine Top-5 Liste mit den zwei Spalten id, zahlenwert aus drei (Beispiel-)Tabellen <schwitz>, dann ergibt das für mich diese Liste (table mal als Info dazu):

table | id | zahlenwert
----------------------
name1 | 22 |   5
name3 | 33 |   9
name2 | 21 |  14
name3 | 31 |  59
name1 | 21 | 108

Die Frage ist: interessiert dich eigentlich "id" oder "zahlenwert"?
Die andere Frage: stehen die id-Werte irgendwie in Verbindung?

Di wirst nicht umhin kommen, dir erst die Tabellennamen zu besorgen, udn diese dann z.B. in PHP als Teil der Abfrage für die WHERE und FROMs einzubauen.
Wenn die IDs der nameX-Tabellen irgendwie zusammengehören hilft evtl. ein JOIN zwischen name1.id und name2.id

Vielleicht formulierst du auch einfach mal das Ergebnis mit weniger abstrakten Daten: was steht denn in den Tabellen, wie gehören sie zusammen (wenn überhaupt) und was willst du letzlich ausgeben.

CirTap

[Marcel]

Lieber CirTap,

also entweder ist deine Fragestellung flasch, das eigentliche Ergebnis ein adneres, oder du hast dich schlicht an einige Stellen vertippt (2x tabelle name2):

Natürlich hast du recht, ich habe mich vertippt, oder besser nach Copy&Paste vergessen etwas zu ändern. Das Beispiel sollte eigentlich so aussehen:
Beispiel:

Inhalt der index-Tabelle:
id | name
-----------
1  | name1
2  | name2
3  | name3

Inhalt der name1-Tabelle
id | zahlenwert
-----------
11 | 108
12 | 5
13 | 180

Inhalt der name2-Tabelle
id | zahlenwert
-----------
21 | 14
22 | 500
23 | 259

Inhalt der name3-Tabelle
id | zahlenwert
-----------
31 | 59
32 | 405
33 | 9

Es sollen also 11,13,22,23,32 augegeben werden!

Aber um mal Konkret zu werden, damit du dir besser vorstellen kannst für was ich das ganze überhaupt machen will: Es geht um eine Linkliste die ich gerade für die Seite unseres Jugendtreffs schreibe (http://www.treffpage.de/start/links). Dort sind Links in verschiedene Kategorien aufgeteilt. Als nettes Feature hab ich mir überlegt, dass die Klicks auf die Links mitgezählt werden und die Links innerhalb der Kategorien immer nach Klicks absteigend sortiert werden. Dann werden die wichtigen (oder beliebten) Links weiter oben angezeigt als die unwichtigen. Das funktioniert auch alles schon, jetzt würde ich eben nur noch gerne eine Top5-Linkliste erstellen die die am meisten angeklickten links aus allen Kategorien zusammenfasst. Das Problem ist jetzt, dass die ganzen Links eben nicht in einer Tabelle abgelegt sind sondern jede Kategorie eine eigene Tabelle hat. Der Name aller Tabellen (die auch gleichzeitig den Namen der Kategorien entsprechen) liegen in der index-Tabelle.

Die Frage ist: interessiert dich eigentlich "id" oder "zahlenwert"?

Mich interessiert ID. Die ID ist der Link, der Zahlenwert ist die Anzahl der Klicks zu dem Link.

Die andere Frage: stehen die id-Werte irgendwie in Verbindung?

Die ID-Werte stehen in keiner Verbindung, wie gesagt, es sind die Links. Im Beispiel habe ich nur Zahlen verwendet, weil ich das einfach fand.

Vielleicht formulierst du auch einfach mal das Ergebnis mit weniger abstrakten Daten: was steht denn in den Tabellen, wie gehören sie zusammen (wenn überhaupt) und was willst du letzlich ausgeben.

Ok ich versuche das mal:

Inhalt der index-Tabelle:
id | kategorie
----------------
1  | jugendtreffs
2  | partner
3  | sonstiges

Inhalt der jugendtreffs- Tabelle
adresse             | klicks
--------------------------------
www.jugendtreff1.de | 108
www.jugendtreff2.de | 5
www.jugendtreff3.de | 180

Inhalt der partner- Tabelle
adresse         | klicks
--------------------------------
www.partner1.de | 14
www.partner2.de | 500
www.partner3.de | 259

Inhalt der sonstiges- Tabelle
adresse           | klicks
--------------------------------
www.sonstiges1.de | 59
www.sonstiges2.de | 405
www.sonstiges3.de | 9

Ausgeben will ich die 5 Links die bereits am häufigsten angeklickt wurden. Also:
www.partner2.de
www.sonstiges2.de
www.partner3.de
www.jugendtreff3.de
www.jugendtreff1.de

Ich hoffe es ist jetzt etwas klarer was ich machen will.

Liebe Grüße,
Marcel

[CirTap]

ah jetzt ja...

also, du wirst wohl nicht umhin kommen, die Namen der Tabellen (wenn sie denn so dynamisch sind) erstmal mit einem SELECT rauszusuchen, damit du sie in einer zweiten Abfrage verwenden kannst.

also in pseudo-code:
$tabellen = $db->query("SELECT * from index-tabelle");
foreach($tabellen => $tabelle) {
  $sql .= "siehe unten :)"
}
$top5 = $db->query($sql);
echo $top5;

Genaugenommen ist dein Freund für die eigentliche Top 5: UNION. Ich hoffe du hast das in eienr MySQL4 DB oder einer DB die UNION kennt -- sonst wirds nix.

(SELECT adresse, klicks FROM jugendtreffs ORDER BY klicks DESC LIMIT 0, 5)
  UNION
(SELECT adresse, klicks FROM partner ORDER BY klicks DESC LIMIT 0, 5)
  UNION
(SELECT adresse, klicks FROM sonstiges ORDER BY klicks DESC LIMIT 0, 5)
  ORDER BY klicks DESC LIMIT 0, 5

Die "(SELECT..) UNION" Zeilen kannst du wie oben angedeutet in einer Schleife zusammenbasteln wobei der Tabellenname eben aus der zuvor gelesenen "index" Tabelle stammt.

Wichtig bei UNION: die einzelnen SELECTs müssen die gleiche Anzahl Spalten und diese die gleichen Datentypen haben. Hier sind's immer zwei Spalten: 1. = Text, 2. = Zahl. Falls es hier kracht, prüfe ob fie "adresse" Spalte auch wirklich gleich lange VARCHAR() sind, und die klicks nicht mal doubles, longs, und ints -- nur zur Sicherheit. Ebenso bestimmt das erste SELECT wie deine Felder nachher in der DB-Ausgabe heissen.

Jeder der SELECTs liefert dir von den Besten (ORDER BY klicks DESC) die ersten 5 (LIMIT 0, 5), das sind erstmal maximal 15 Datensätze.

Nachdem alle drei Tabellen zusammen sind, wird nochmal absteigend sortiert und daraus wieder die TOP 5 ermittelt.
Denk an die Klammern um jede SELECT Anweisung.

Also bei mir gings :-)

Viel Spaß,
CirTap

[Marc21]

Hallo CirTap,

danke für deine Hilfe! Folgendes hab ich programmiert:

$tabellennamen = mysql_query("SELECT * FROM n_link_kat");

while($zeile = mysql_fetch_row($tabellennamen)) {
 //schreibt alle Tabellennamen in ein Array
        $tabellen[] = $zeile[0];
}
//Zählt die Tabellennamen
$anzahl = count($tabellen);

//Bastelt den Union SELECT zusammen
for($count = 0; $count < $anzahl; $count++){
 $return .= "(SELECT url, klicks FROM $tabellen[$count] ORDER BY klicks DESC LIMIT 0, 5)";

if($count < $anzahl-1){
  $return .= "UNION";
 }
}
$return .= "ORDER BY klicks DESC LIMIT 0, 5";

while($top5 = mysql_fetch_row(mysql_query($return))){
 echo "$top5[0] <br>";
}

Dann hab ichs laufen lassen, folgender Fehler:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /web/start/links/index.php on line 108

Erst dann hab ich gelesen

Ich hoffe du hast das in eienr MySQL4 DB oder einer DB die UNION kennt -- sonst wirds nix.

Na toll, meine MYSQL-Version ist 3.x :(

Hast du noch eine Idee?

Trotzdem vielen Dank für deine Mühe, Liebe Grüße
Marc21

[CirTap]

Hi,

Erst dann hab ich gelesen

Ich hoffe du hast das in eienr MySQL4 DB oder einer DB die UNION kennt -- sonst wirds nix.
Na toll, meine MYSQL-Version ist 3.x :(

Hast du noch eine Idee?

Naja, ich hätte da zwar einen tricky Workaround der UNIONs für MySQL 3 nachbaut, aber vielleicht machst du dann doch der Einfachheit halber soviele Abfragen wie nötig und klebst die Ergebnisse einfach in ein PHP-Array zusammen.
'ne weitere Alternative steht weiter unten, doch zuerst:
Die SELECT Anweisungen bleiben wie gehabt, d.h. du holst dir die jeweiligen Top5 und schaufelst sie in ein Array das du dann nochmal auf PHP-Seite sortierst.

<?php
// Sammelbecken
$alleklicks = array();
// der Schleifenteil
for($count = 0; $count < $anzahl; $count++){
  $sql = "SELECT url, klicks FROM $tabellen[$count] ORDER BY klicks DESC LIMIT 0, 5";
  // ein assoziatives Array ist immer praktisch :-)
  if ( $top5 = mysql_fetch_assoc( mysql_query($sql) ) ) {
    $alleklicks[ $top5['url'] ] = $top5['klicks'];
  }
}
// absteigend sortieren
rsort($alleklicks, SORT_NUMERIC);
reset($alleklicks);
$cnt = 1;
foreach ($alleklicks as $url => $klicks) {
  echo "<br />Platz $cnt: $url ($klicks)";
  $cnt++;
}
?>

So sollt es (auch) funktionieren.
Achtung! Könnten ein paar Schraibpfehla drin sein!

So und nu die Alternative:
mach's einfach "einfach" und leg EINE Tabelle an, in der die Klicks und URLs drinstehen, dazu ein Feld mit der Kategorie, ein paar Indizes und gut isses. Das erspart dir viel Arbeit bei zukünfigen Auswertungen oder anderen Statistiken, z.B. welche URLs wurden heute, in den letzen X Tagen etc. angeglickt. Ein paar Datumsfelder mit rein und du kannst praktisch mit einem SELECT alle Auswertungen zusammenschummeln die du glaubst so zu brauchen :-)
Auf lange Sicht ist das sicher praktikabler und MySQL muss sich nur mit einer Tabelle rumärgern anstatt einem Dutzend.

Hier findest du eine kleine "Anleitung" wie du die ganzen Tabellen schnell in eine zusammenfassen kannst: http://forum.de.selfhtml.org/?t=91161&m=547983

Viel Spaß,
CirTap

[Marcel]

Hi CirTap,

nachdem ich zwei kleine Sachen verändert habe, hat es wunderbar funktioniert:

while ($top5 = mysql_fetch_assoc($result)) {
------
$alleklicks[$top5['url']] = $top5['klicks'];
}

arsort($alleklicks);
--

Vielen Dank!!!

Liebe Grüße
Marcel

[MudGuard]

Hi,

Aber um mal Konkret zu werden, damit du dir besser vorstellen kannst für was ich das ganze überhaupt machen will: Es geht um eine Linkliste die ich gerade für die Seite unseres Jugendtreffs schreibe (http://www.treffpage.de/start/links). Dort sind Links in verschiedene Kategorien aufgeteilt.

Und dann hast Du für jede Kategorie eine eigene Tabelle gemacht?

Das klingt für mich so, als wäre das Datenmodell kaputt.

Anstelle von diversen identisch aufgebauten Tabellen würde ich eine einzige Tabelle benutzen, die so wie Deine einzelnen Tabellen aufgebaut ist mit einer zusätzlichen Spalte für die Kategorie.

cu,
Andreas