Spalten und Zeilen aus zwei Tabellen als View/Tabelle vereinen

[Maggi]

Hallo Bernd,
hier das Ergebnis:
Kann man Tabellen hier einfügen?

DESCRIBE jomkas_user_profiles;

Field Type Null Key Default Extra
user_id int(11) NO PRI NULL
profile_key varchar(100) NO PRI NULL
profile_value varchar(255) NO NULL
ordering int(11) NO 0

DESCRIBE jomkas.users;

Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
name varchar(255) NO MUL
username varchar(150) NO MUL
email varchar(100) NO MUL
password varchar(100) NO
usertype varchar(25) NO MUL
block tinyint(4) NO MUL 0
sendEmail tinyint(4) YES 0
registerDate datetime NO 0000-00-00 00:00:00
lastvisitDate datetime NO 0000-00-00 00:00:00
activation varchar(100) NO
params text NO NULL
lastResetTime datetime NO 0000-00-00 00:00:00
resetCount int(11) NO 0
VG
Maggi

 

[Maggi]

Hallo,
habe im Joomla Forum nochmal nachgefragt zu den Quotes:
Die Daten werden im JSON-Format gespeichert. Daher die Double-Quotes.
Der Tipp war:
"Dann musst du doch die Werte zuvor nur per PHP-Code: $feldeintrag = json_decode($feldeintrag); entqoten"

Ich denke jetzt wird es doch langsam zu komplex.....
VG
Maggi

 

[akretschmer]

Hallo akretschmer,
wenn es nicht aus meinem ersten Posting hervor gegangen sein sollte, dann Entschuldigung. Auch wenn ich dachte dies geschrieben zu haben.
Es handelt sich um zwei Tabellen:
1. jomkas.users und daraus die Spalten id, name und email.
In der zweiten Tabelle
2. jomkas_user_profiles mit den Spalten user_id, profile_key, profile_value.
Die Id aus der ersten ist mit der User_id aus der zweiten Tabelle verknüpft.
Für jede id aus der ersten Tabelle stehen in der zweiten Tabelle in jeder Zeile dann verschiedene Einträge in der Spalte profile_key.
Beispiel:
User_id, profile_key, profile_value
41, profile.postal_code, 60437
41, profile.city, Frankfurt
41, profile.dob, 1968-02-15
43, profile.postal_code, 60435
43, profile.city, Frankfurt
43, profiledob, 1970-08-25
usw.
Die Spalten sind durch Kommata getrennt hier im Forum.
Am Ende soll eine Tabelle mit den Spalten:
name, email, PLZ, Stadt, Geburtstag
herauskommen für jede id pro Zeile.

Zusätzlich sollte noch:
1. Das Datum hat das Format YYYYMMDD bei der Eingabe (vorgegeben), das sollte aber in der Tabelle in dem Deutsche Format angezeigt werden : DDMMYYYY
2. Die Tabelle jomkas_users ist verknüpft mit einer weiteren Tabelle (jomkas_user_usergroup_map), welche die Spalten user_id und group_id enthält. Alle User werden hier unter User_id aufgelistet (entsprechend der IDs aus der "jomkas_User" Tabelle). Es sollen nur User mit dem Wert "9" in der Spalte group_id in die zu bildende Tabelle übernommen werden also berücksichtigt werden.
VG
Maggi

test=*# select * from u;
 id |  name
----+---------
 41 | user_41
 43 | user_43
(2 rows)

Time: 0,158 ms
test=*# select * from p;
 uid | key |  val
-----+-----+------------
  41 | plz | 60437
  41 | ort | ffm
  41 | dob | 1968-02-15
  43 | plz | 60435
  43 | ort | ffm
  43 | dob | 1970-08-25
(6 rows)

Time: 0,159 ms
test=*# select u.id, u.name, array_to_string(array_agg(case when p.key='plz' then p.val else null end),',') as plz, array_to_string(array_agg(case when p.key='ort' then p.val else null end),',') as ort, to_char(array_to_string(array_agg(case when p.key='dob' then p.val else null end),',')::date,'DD.MM.YYYY') as dop from u left join p on u.id=p.uid group by u.id, u.name;
 id |  name  |  plz  | ort |  dop
----+---------+-------+-----+------------
 41 | user_41 | 60437 | ffm | 15.02.1968
 43 | user_43 | 60435 | ffm | 25.08.1970
(2 rows)

Eingentlich einfach und von mir schon zigmal hier gezeigt. Allerdings geht das so nicht in MySQL: das kann das nicht.

 

[akretschmer]

Hallo,
habe im Joomla Forum nochmal nachgefragt zu den Quotes:
Die Daten werden im JSON-Format gespeichert.

Wo ist hier JSON im Spiel, und warum verwendet man dann eine DB, die das nicht kann?

Fragen über Fragen, ...

 

[Maggi]

Hallo,
vielen dank für die Antworten die allerdings dann doch die Frage bei mir aufwerfen.

Wo ist hier JSON im Spiel, und warum verwendet man dann eine DB, die das nicht kann?

Fragen über Fragen, ...

Kann ich nicht sagen, aber der Kommentar war:
"Ich schätze mal, um weitere Profilfelder möglich zu machen, die bspw. auch Arrays sein können (Häkchenfelder mit Mehrfachauswahl z.B.)."
Ob das nun stimmt oder nur eine Vermutung ist kann ich nicht beurteilen.

test=*# select * from u;
id |  name
----+---------
41 | user_41
43 | user_43
(2 rows)

Time: 0,158 ms
test=*# select * from p;
uid | key |  val
-----+-----+------------
  41 | plz | 60437
  41 | ort | ffm
  41 | dob | 1968-02-15
  43 | plz | 60435
  43 | ort | ffm
  43 | dob | 1970-08-25
(6 rows)

Time: 0,159 ms
test=*# select u.id, u.name, array_to_string(array_agg(case when p.key='plz' then p.val else null end),',') as plz, array_to_string(array_agg(case when p.key='ort' then p.val else null end),',') as ort, to_char(array_to_string(array_agg(case when p.key='dob' then p.val else null end),',')::date,'DD.MM.YYYY') as dop from u left join p on u.id=p.uid group by u.id, u.name;
id |  name  |  plz  | ort |  dop
----+---------+-------+-----+------------
41 | user_41 | 60437 | ffm | 15.02.1968
43 | user_43 | 60435 | ffm | 25.08.1970
(2 rows)

Eingentlich einfach und von mir schon zigmal hier gezeigt. Allerdings geht das so nicht in MySQL: das kann das nicht.

Mein Anliegen war es, mich in einem "MYSQL" Forum mit einem Problem an Forumsteilnehmer zu wenden, mit der Hoffnung auf Hilfestellung.
VG
Maggi

 

[akretschmer]

Mein Anliegen war es, mich in einem "MYSQL" Forum mit einem Problem an Forumsteilnehmer zu wenden, mit der Hoffnung auf Hilfestellung.
VG
Maggi

Vermutlich kann man es mehr oder weniger anpassen. Das war mehr als Idee, wie man es machen kann.

 

[Maggi]

Ok danke für den Vorschlag.
Dann warte ich mal ab, ob jemand eine Idee dazu hat, ich kann das leider nicht.
Darf man erfahren, was MySQL eigentlich nicht kann an dem Beispiel?
VG
Maggi

 

[akretschmer]

Ok danke für den Vorschlag.
Dann warte ich mal ab, ob jemand eine Idee dazu hat, ich kann das leider nicht.
Darf man erfahren, was MySQL eigentlich nicht kann an dem Beispiel?
VG
Maggi

diverse Funktionen wie array_agg() zur Aggregation. Es gibt wohl group_concat() oder sowas.

 

[Maggi]

Hallo Bernd,
ich hatte die Vorschläge von Dir umgesetzt und eigentlich funktioniert es damit auch schon gut.
SELECT
jomkas_users. name, email,
GROUP_CONCAT(if(profile_key ='profile.phone', TRIM( '"' FROM profile_value) , '')) AS 'Tel',
GROUP_CONCAT(if(profile_key ='profile.address1', TRIM( '"' FROM profile_value) , '')) AS 'Straße',
GROUP_CONCAT(if(profile_key ='profile.postal_code', TRIM( '"' FROM profile_value) , '')) AS 'PLZ',
GROUP_CONCAT(if(profile_key ='profile.city', TRIM( '"' FROM profile_value) , '')) AS 'Stadt',
GROUP_CONCAT(if(profile_key ='profile.dob', TRIM( '"' FROM profile_value) , '')) AS 'Geburtstag'
FROM jomkas_users LEFT JOIN jomkas_user_profiles ON jomkas_users.id = jomkas_user_profiles.user_id
GROUP BY jomkas_users.id

Das einzige was nun passiert ist, das die Anführungszeichen nun weg sind dafür aber mehrere Kommata vor und hinter dem Eintrag stehen. Und zwar nur bei den Feldern aus der jomkas_user_profiles Tabelle, wo vorher die Anführungszeichen standen.
Und hast Du noch eine Idee das Internationale Datumsformat in das deutsche mit DD.MM.YYY zu ändern in dem obigen Beispiel?
Vielen Dank
VG
Maggi