Aus 2 Abfragen 1 machen (mehrere tabellen)

K

Kipperlenny

Erfahrenes Mitglied
Moin

Momentan gibt es diesen Code:

PHP: 
$result=$db->query("SELECT u.*,c.* FROM user AS u, staedte AS c WHERE u.id='1' AND u.activeid1=c.id");
  $userdata1=$db->fetch_array($result);
  
$resultS=$db->query("SELECT u.*,c.* FROM user AS u, staedteS AS c WHERE u.id='1' AND u.activeidS=c.id");
  $userdataS=$db->fetch_array($resultS);

Der erstellt zwei Arrays, wobei sich in diesen Arrays nur die zweite Hälfte unterscheidet.
Deswegen hätte ich gerne nur eine Abfrage und dann nur einen Array wo alles drin ist. ich habe jetzt folgendes gemacht:

PHP: 
$result=$db->query("SELECT u.*,h.*,s.* FROM user AS u, staedte AS h, staedteS AS s WHERE u.id='1' AND u.activeid1=h.id AND u.activeidS=s.id");
  $userdata1=$db->fetch_array($result);

Das funktioniert auch schon ganz gut, hier mal das was bei den drei Möglichkeiten rauskommt:

HTML: 
1. Abfrage:
Array
(
    [0] => 1
    [id] => 1
    [1] => Kipperlenny
    [name] => Neues Land
    [2] => 05481eb2c551506f859a2d2290c55101
    [passwort] => 05481eb2c551506f859a2d2290c55101
    [3] => kipperlenny@gmx.de
    [email] => kipperlenny@gmx.de
    [4] => 10
    [punkte1] => 10
    [5] => 10
    [punkteS] => 10
    [6] => 3
    [rang1] => 3
    [7] => 2
    [rangS] => 2
    [8] => 4
    [rasse1] => 4
    [9] => 2
    [rasseS] => 2
    [10] => 1
    [epoche1] => 1
    [11] => 1
    [epocheS] => 1
    [12] => 0
    [allianz1] => 0
    [13] => 0
    [allianzS] => 0
    [14] => 0
    [allianz1admin] => 0
    [15] => 0
    [allianzSadmin] => 0
    [16] => 
    [avatar] => 
    [17] => 
    [icq] => 
    [18] => b56b345d7d3b18da0a14451d3e263728
    [sessionid] => b56b345d7d3b18da0a14451d3e263728
    [19] => 130b67311d703dd38bc0a3406e2f74fc
    [cookiekey] => 130b67311d703dd38bc0a3406e2f74fc
    [20] => 1
    [geworben] => 1
    [21] => 1
    [serveradmin] => 1
    [22] => 1
    [activeid1] => 1
    [23] => 4
    [activeidS] => 4
    [24] => 1
    [25] => 1
    [userid] => 1
    [26] => Neues Land
    [27] => 70
    [x] => 70
    [28] => 1
    [y] => 1
    [29] => 1
    [startland] => 1
    [30] => 1000
    [nahrung] => 1000
    [31] => 1000
    [eisen] => 1000
    [32] => 1000
    [stein] => 1000
    [33] => 1000
    [geld] => 1000
    [34] => 0
    [stahl] => 0
    [35] => 0
    [titan] => 0
    [36] => 0
    [aluminium] => 0
    [37] => 0
    [silizium] => 0
    [38] => 1
    [Steinsucherhuette] => 1
    [39] => 0
    [Jagdhuette] => 0
    [40] => 0
    [Marktplatz] => 0
    [41] => 0
    [Hoehle] => 0
    [42] => 0
    [Dorfzentrum] => 0
    [43] => 0
    [Kriegerhoehle] => 0
    [44] => 0
    [Lagerplatz] => 0
    [45] => 0
    [Spaehturm] => 0
    [46] => 0
    [Steinbruch] => 0
    [47] => 0
    [Farm] => 0
    [48] => 0
    [Haus] => 0
    [49] => 0
    [Palast] => 0
    [50] => 0
    [Burg] => 0
    [51] => 0
    [Lagerhaus] => 0
    [52] => 0
    [Eisenmine] => 0
    [53] => 0
    [Truppenuebungsplatz] => 0
    [54] => 0
    [Wall] => 0
    [55] => 10
    [punkte] => 10
)

2. Abfrage: 
Array
(
    [0] => 1
    [id] => 4
    [1] => Kipperlenny
    [name] => zweitS Land
    [2] => 05481eb2c551506f859a2d2290c55101
    [passwort] => 05481eb2c551506f859a2d2290c55101
    [3] => kipperlenny@gmx.de
    [email] => kipperlenny@gmx.de
    [4] => 10
    [punkte1] => 10
    [5] => 10
    [punkteS] => 10
    [6] => 3
    [rang1] => 3
    [7] => 2
    [rangS] => 2
    [8] => 4
    [rasse1] => 4
    [9] => 2
    [rasseS] => 2
    [10] => 1
    [epoche1] => 1
    [11] => 1
    [epocheS] => 1
    [12] => 0
    [allianz1] => 0
    [13] => 0
    [allianzS] => 0
    [14] => 0
    [allianz1admin] => 0
    [15] => 0
    [allianzSadmin] => 0
    [16] => 
    [avatar] => 
    [17] => 
    [icq] => 
    [18] => b56b345d7d3b18da0a14451d3e263728
    [sessionid] => b56b345d7d3b18da0a14451d3e263728
    [19] => 130b67311d703dd38bc0a3406e2f74fc
    [cookiekey] => 130b67311d703dd38bc0a3406e2f74fc
    [20] => 1
    [geworben] => 1
    [21] => 1
    [serveradmin] => 1
    [22] => 1
    [activeid1] => 1
    [23] => 4
    [activeidS] => 4
    [24] => 4
    [25] => 1
    [userid] => 1
    [26] => zweitS Land
    [27] => 70
    [x] => 70
    [28] => 3
    [y] => 3
    [29] => 1
    [startland] => 1
    [30] => 1000
    [nahrung] => 1000
    [31] => 1000
    [eisen] => 1000
    [32] => 1000
    [stein] => 1000
    [33] => 1000
    [geld] => 1000
    [34] => 0
    [stahl] => 0
    [35] => 0
    [titan] => 0
    [36] => 0
    [aluminium] => 0
    [37] => 0
    [silizium] => 0
    [38] => 1
    [Steinsucherhuette] => 1
    [39] => 0
    [Jagdhuette] => 0
    [40] => 0
    [Marktplatz] => 0
    [41] => 0
    [Hoehle] => 0
    [42] => 0
    [Dorfzentrum] => 0
    [43] => 0
    [Kriegerhoehle] => 0
    [44] => 0
    [Lagerplatz] => 0
    [45] => 0
    [Spaehturm] => 0
    [46] => 0
    [Steinbruch] => 0
    [47] => 0
    [Farm] => 0
    [48] => 0
    [Haus] => 0
    [49] => 0
    [Palast] => 0
    [50] => 0
    [Burg] => 0
    [51] => 0
    [Lagerhaus] => 0
    [52] => 0
    [Eisenmine] => 0
    [53] => 0
    [Truppenuebungsplatz] => 0
    [54] => 0
    [Wall] => 0
    [55] => 10
    [punkte] => 10
)

3. Abfrage:
Array
(
    [0] => 1
    [id] => 4
    [1] => Kipperlenny
    [name] => zweitS Land
    [2] => 05481eb2c551506f859a2d2290c55101
    [passwort] => 05481eb2c551506f859a2d2290c55101
    [3] => kipperlenny@gmx.de
    [email] => kipperlenny@gmx.de
    [4] => 10
    [punkte1] => 10
    [5] => 10
    [punkteS] => 10
    [6] => 3
    [rang1] => 3
    [7] => 2
    [rangS] => 2
    [8] => 4
    [rasse1] => 4
    [9] => 2
    [rasseS] => 2
    [10] => 1
    [epoche1] => 1
    [11] => 1
    [epocheS] => 1
    [12] => 0
    [allianz1] => 0
    [13] => 0
    [allianzS] => 0
    [14] => 0
    [allianz1admin] => 0
    [15] => 0
    [allianzSadmin] => 0
    [16] => 
    [avatar] => 
    [17] => 
    [icq] => 
    [18] => b56b345d7d3b18da0a14451d3e263728
    [sessionid] => b56b345d7d3b18da0a14451d3e263728
    [19] => 130b67311d703dd38bc0a3406e2f74fc
    [cookiekey] => 130b67311d703dd38bc0a3406e2f74fc
    [20] => 1
    [geworben] => 1
    [21] => 1
    [serveradmin] => 1
    [22] => 1
    [activeid1] => 1
    [23] => 4
    [activeidS] => 4
    [24] => 1
    [25] => 1
    [userid] => 1
    [26] => Neues Land
    [27] => 70
    [x] => 70
    [28] => 1
    [y] => 3
    [29] => 1
    [startland] => 1
    [30] => 1000
    [nahrung] => 1000
    [31] => 1000
    [eisen] => 1000
    [32] => 1000
    [stein] => 1000
    [33] => 1000
    [geld] => 1000
    [34] => 0
    [stahl] => 0
    [35] => 0
    [titan] => 0
    [36] => 0
    [aluminium] => 0
    [37] => 0
    [silizium] => 0
    [38] => 1
    [Steinsucherhuette] => 1
    [39] => 0
    [Jagdhuette] => 0
    [40] => 0
    [Marktplatz] => 0
    [41] => 0
    [Hoehle] => 0
    [42] => 0
    [Dorfzentrum] => 0
    [43] => 0
    [Kriegerhoehle] => 0
    [44] => 0
    [Lagerplatz] => 0
    [45] => 0
    [Spaehturm] => 0
    [46] => 0
    [Steinbruch] => 0
    [47] => 0
    [Farm] => 0
    [48] => 0
    [Haus] => 0
    [49] => 0
    [Palast] => 0
    [50] => 0
    [Burg] => 0
    [51] => 0
    [Lagerhaus] => 0
    [52] => 0
    [Eisenmine] => 0
    [53] => 0
    [Truppenuebungsplatz] => 0
    [54] => 0
    [Wall] => 0
    [55] => 10
    [punkte] => 10
    [56] => 4
    [57] => 1
    [58] => zweitS Land
    [59] => 70
    [60] => 3
    [61] => 1
    [62] => 1000
    [63] => 1000
    [64] => 1000
    [65] => 1000
    [66] => 0
    [67] => 0
    [68] => 0
    [69] => 0
    [70] => 1
    [71] => 0
    [72] => 0
    [73] => 0
    [74] => 0
    [75] => 0
    [76] => 0
    [77] => 0
    [78] => 0
    [79] => 0
    [80] => 0
    [81] => 0
    [82] => 0
    [83] => 0
    [84] => 0
    [85] => 0
    [86] => 0
    [87] => 10
)

Allerdings, wie man sieht fehlen bei der dritten Abfrage im unteren Drittel die Damen, Werte sind zwar da (sind ja die gleichen wie bei der 2. Abfrage) aber der Name dazu fehlt (der wie bei der 2. Abfrage da sein sollte).

Was mache ich falsch?

lenny
 
Zuletzt bearbeitet:
Probier mal folgendes, das müsste eigentlich deine Dupletten rauswerfen.
Vorausgesetzt, deine Tabellen unterscheiden sich nicht strukturell - aber dazu hast du nichts geschrieben und ich bin zu faul dein Array-Dump durchzuarbeiten

SQL: 
SELECT u.*,c.* FROM user AS u, staedte AS c WHERE u.id='1' AND u.activeid1=c.id
UNION
SELECT u.*,c.* FROM user AS u, staedteS AS c WHERE u.id='1' AND u.activeidS=c.id
 
Hm, da fehlt jetzt aber was - Dubletten rausschmeißen alleine genügt ja nicht...
Weil wie in meinem ersten Post geschrieben, unterscheiden sich die beiden Arrays in der zweiten Hälfte. In dem Array den ich jetzt versuche zu bauen, soll ja die erste Hälfte der beiden Arrays einmal drin sein und dann die beiden unterschiedlichen Hälften auch. Verständlich?

HTML: 
Array
(
    [0] => 1
    [id] => 1
    [1] => Kipperlenny
    [name] => haupt land 1
    [2] => 05481eb2c551506f859a2d2290c55101
    [passwort] => 05481eb2c551506f859a2d2290c55101
    [3] => kipperlenny@gmx.de
    [email] => kipperlenny@gmx.de
    [4] => 10
    [punkte1] => 10
    [5] => 10
    [punkteS] => 10
    [6] => 3
    [rang1] => 3
    [7] => 2
    [rangS] => 2
    [8] => 4
    [rasse1] => 4
    [9] => 2
    [rasseS] => 2
    [10] => 1
    [epoche1] => 1
    [11] => 1
    [epocheS] => 1
    [12] => 0
    [allianz1] => 0
    [13] => 0
    [allianzS] => 0
    [14] => 0
    [allianz1admin] => 0
    [15] => 0
    [allianzSadmin] => 0
    [16] => 
    [avatar] => 
    [17] => 
    [icq] => 
    [18] => b56b345d7d3b18da0a14451d3e263728
    [sessionid] => b56b345d7d3b18da0a14451d3e263728
    [19] => 130b67311d703dd38bc0a3406e2f74fc
    [cookiekey] => 130b67311d703dd38bc0a3406e2f74fc
    [20] => 1
    [geworben] => 1
    [21] => 1
    [serveradmin] => 1
    [22] => 1
    [activeid1] => 1
    [23] => 4
    [activeidS] => 4
    [24] => 1
    [25] => 1
    [userid] => 1
    [26] => haupt land 1
    [27] => 70
    [x] => 70
    [28] => 1
    [y] => 1
    [29] => 1
    [startland] => 1
    [30] => 1000
    [nahrung] => 1000
    [31] => 1000
    [eisen] => 1000
    [32] => 1000
    [stein] => 1000
    [33] => 1000
    [geld] => 1000
    [34] => 0
    [stahl] => 0
    [35] => 0
    [titan] => 0
    [36] => 0
    [aluminium] => 0
    [37] => 0
    [silizium] => 0
    [38] => 1
    [Steinsucherhuette] => 1
    [39] => 0
    [Jagdhuette] => 0
    [40] => 0
    [Marktplatz] => 0
    [41] => 0
    [Hoehle] => 0
    [42] => 0
    [Dorfzentrum] => 0
    [43] => 0
    [Kriegerhoehle] => 0
    [44] => 0
    [Lagerplatz] => 0
    [45] => 0
    [Spaehturm] => 0
    [46] => 0
    [Steinbruch] => 0
    [47] => 0
    [Farm] => 0
    [48] => 0
    [Haus] => 0
    [49] => 0
    [Palast] => 0
    [50] => 0
    [Burg] => 0
    [51] => 0
    [Lagerhaus] => 0
    [52] => 0
    [Eisenmine] => 0
    [53] => 0
    [Truppenuebungsplatz] => 0
    [54] => 0
    [Wall] => 0
    [55] => 10
    [punkte] => 10
)
<br><br>Array
(
    [0] => 1
    [id] => 4
    [1] => Kipperlenny
    [name] => speed land 2
    [2] => 05481eb2c551506f859a2d2290c55101
    [passwort] => 05481eb2c551506f859a2d2290c55101
    [3] => kipperlenny@gmx.de
    [email] => kipperlenny@gmx.de
    [4] => 10
    [punkte1] => 10
    [5] => 10
    [punkteS] => 10
    [6] => 3
    [rang1] => 3
    [7] => 2
    [rangS] => 2
    [8] => 4
    [rasse1] => 4
    [9] => 2
    [rasseS] => 2
    [10] => 1
    [epoche1] => 1
    [11] => 1
    [epocheS] => 1
    [12] => 0
    [allianz1] => 0
    [13] => 0
    [allianzS] => 0
    [14] => 0
    [allianz1admin] => 0
    [15] => 0
    [allianzSadmin] => 0
    [16] => 
    [avatar] => 
    [17] => 
    [icq] => 
    [18] => b56b345d7d3b18da0a14451d3e263728
    [sessionid] => b56b345d7d3b18da0a14451d3e263728
    [19] => 130b67311d703dd38bc0a3406e2f74fc
    [cookiekey] => 130b67311d703dd38bc0a3406e2f74fc
    [20] => 1
    [geworben] => 1
    [21] => 1
    [serveradmin] => 1
    [22] => 1
    [activeid1] => 1
    [23] => 4
    [activeidS] => 4
    [24] => 4
    [25] => 1
    [userid] => 1
    [26] => speed land 2
    [27] => 70
    [x] => 70
    [28] => 3
    [y] => 3
    [29] => 1
    [startland] => 1
    [30] => 1000
    [nahrung] => 1000
    [31] => 1000
    [eisen] => 1000
    [32] => 1000
    [stein] => 1000
    [33] => 1000
    [geld] => 1000
    [34] => 0
    [stahl] => 0
    [35] => 0
    [titan] => 0
    [36] => 0
    [aluminium] => 0
    [37] => 0
    [silizium] => 0
    [38] => 1
    [Steinsucherhuette] => 1
    [39] => 0
    [Jagdhuette] => 0
    [40] => 0
    [Marktplatz] => 0
    [41] => 0
    [Hoehle] => 0
    [42] => 0
    [Dorfzentrum] => 0
    [43] => 0
    [Kriegerhoehle] => 0
    [44] => 0
    [Lagerplatz] => 0
    [45] => 0
    [Spaehturm] => 0
    [46] => 0
    [Steinbruch] => 0
    [47] => 0
    [Farm] => 0
    [48] => 0
    [Haus] => 0
    [49] => 0
    [Palast] => 0
    [50] => 0
    [Burg] => 0
    [51] => 0
    [Lagerhaus] => 0
    [52] => 0
    [Eisenmine] => 0
    [53] => 0
    [Truppenuebungsplatz] => 0
    [54] => 0
    [Wall] => 0
    [55] => 10
    [punkte] => 10
)
<br><br>Array
(
    [0] => 1
    [id] => 1
    [1] => Kipperlenny
    [name] => haupt land 1
    [2] => 05481eb2c551506f859a2d2290c55101
    [passwort] => 05481eb2c551506f859a2d2290c55101
    [3] => kipperlenny@gmx.de
    [email] => kipperlenny@gmx.de
    [4] => 10
    [punkte1] => 10
    [5] => 10
    [punkteS] => 10
    [6] => 3
    [rang1] => 3
    [7] => 2
    [rangS] => 2
    [8] => 4
    [rasse1] => 4
    [9] => 2
    [rasseS] => 2
    [10] => 1
    [epoche1] => 1
    [11] => 1
    [epocheS] => 1
    [12] => 0
    [allianz1] => 0
    [13] => 0
    [allianzS] => 0
    [14] => 0
    [allianz1admin] => 0
    [15] => 0
    [allianzSadmin] => 0
    [16] => 
    [avatar] => 
    [17] => 
    [icq] => 
    [18] => b56b345d7d3b18da0a14451d3e263728
    [sessionid] => b56b345d7d3b18da0a14451d3e263728
    [19] => 130b67311d703dd38bc0a3406e2f74fc
    [cookiekey] => 130b67311d703dd38bc0a3406e2f74fc
    [20] => 1
    [geworben] => 1
    [21] => 1
    [serveradmin] => 1
    [22] => 1
    [activeid1] => 1
    [23] => 4
    [activeidS] => 4
    [24] => 1
    [25] => 1
    [userid] => 1
    [26] => haupt land 1
    [27] => 70
    [x] => 70
    [28] => 1
    [y] => 1
    [29] => 1
    [startland] => 1
    [30] => 1000
    [nahrung] => 1000
    [31] => 1000
    [eisen] => 1000
    [32] => 1000
    [stein] => 1000
    [33] => 1000
    [geld] => 1000
    [34] => 0
    [stahl] => 0
    [35] => 0
    [titan] => 0
    [36] => 0
    [aluminium] => 0
    [37] => 0
    [silizium] => 0
    [38] => 1
    [Steinsucherhuette] => 1
    [39] => 0
    [Jagdhuette] => 0
    [40] => 0
    [Marktplatz] => 0
    [41] => 0
    [Hoehle] => 0
    [42] => 0
    [Dorfzentrum] => 0
    [43] => 0
    [Kriegerhoehle] => 0
    [44] => 0
    [Lagerplatz] => 0
    [45] => 0
    [Spaehturm] => 0
    [46] => 0
    [Steinbruch] => 0
    [47] => 0
    [Farm] => 0
    [48] => 0
    [Haus] => 0
    [49] => 0
    [Palast] => 0
    [50] => 0
    [Burg] => 0
    [51] => 0
    [Lagerhaus] => 0
    [52] => 0
    [Eisenmine] => 0
    [53] => 0
    [Truppenuebungsplatz] => 0
    [54] => 0
    [Wall] => 0
    [55] => 10
    [punkte] => 10
)
 
Also wenn ich die SQL Abfrage von die bei phpmyadmin direkt eingebe, dann kommt genau das raus was ich will - nur mein print_r zeigt nicht das an was in dem array eigentlich drin... kann ich print_r nicht nutzen bei einem array der durch deine abfrage entstanden ist?
 
An meiner Abfrage liegt das sicherlich nicht - du bekommst von MySql ein Resultset zurück, das du weiterverarbeitest. In dem Moment, wo du was mit print_r machst, weiß dein Skript schon lange nicht mehr, was da die Datenbank gemacht hat.

Machst du vorher noch Änderungen am Array?
Sind da wirklich Unterschiede, oder sieht das mit print_r nur so aus?
(Ich weigere weiterhin mir das genau anzuschauen ...)
 
Du musst es dir auch nicht mehr anschauen, da deine Antwort super ist :D

Nur mit print_r konnte ich nicht sehen dass es richtig ist!
 
HTML: 
   1.
      SELECT u.*,c.* FROM user AS u, staedte AS c WHERE u.id='1' AND u.activeid1=c.id
   2.
      UNION
   3.
      SELECT u.*,c.* FROM user AS u, staedteS AS c WHERE u.id='1' AND u.activeidS=c.id

Wenn ich damit jetzt nen Array gebildet habe, wie komme ich denn dann an die einzelnen Sachen ran?!

Der Array heißt dann z.b. $test dann kann ich ja über $test['userid'] die userid rausbekommen die ja in der Tablle "user" gespeichert ist.

Aber wie komme ich zum Beispiel an die Höhe der Holzrohstoffe ran in einer Stadt aus der Tabelle "staedteS"? Weil in "staedte" heißt die Spalte ja genauso - ist nur ne ganz andere stadt....

Verstanden? Also ARRAY -> USERDATEN , STADT1 DATEN, STADT2 DATEN <- Wenn die Daten von Stadt1 und Stadt2 den gleichen Namen haben, wie kann ich sie dann unterschiedlich aufrufen?
 
Einfache Antwort: Gar nicht.

Wenn du nur eine Abfrage haben willst, dann heißen deine Spalten immer alle gleich.
Alternativ könntest du noch in jedem Select einen konstanten String mitgeben (z.B.: "Gruppe 1", "Gruppe 2") um dann später in PHP danach zu unterscheiden.
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück