[mysql] mysql naar xml

[RSD]

Stel ik heb het volgende model:

movies
id
name

categories
id
name

moviescategories
id
movieid
categoryid

Een movie kan in meerdere categorieen voorkomen, dit wordt bijgehouden middels de koppeltabel moviescategories.

Nu wil ik de volgende xml genereren met behulp van 1 query:
[code=xml]
<movies>
<movie>
<name>name</name>
<categories>
<category>name1</category
<category>name2</category
...
</categories>
</movie>
<movie>
....
</movie>
....
</movies>
[/code=xml]
Het gaat me nu om het categories gedeelte. Ik kan geen query bedenken hoe ik dit kan doen. Ik kan het wel via php doen door eerst 1 query voor de film te maken en dan voor de categorie etc.. maar dat is niet de bedoeling. Het zou het mooiste zijn als ik de categories in een comma separated list uit de mysql tabel kan halen en deze dan met behulp van php kan exploden. Dit is me alleen nog niet gelukt. Is dit uberhaupt wel mogelijk?

Dus dat ik een query heb die als output heeft per film:

is,name,catnames(commaseparated).

[Spockz]

Zoiets?:

SQL:

SELECT movies.id, movies.name, categories.name
FROM movies, categories, moviescategories
WHERE movies.id = moviescategories.movieid 
      AND categories.id = moviescategories.categoryid

[Webgnome]

Buiten het feit dat ik me afvraag waarom je in movies EN in moviescategories een categoryid specificeerd zou het zoiets moeten zijn als:

select c.name, m.name from movies as m, categories as c where (select id from movies ) = c.movieid

offtopic:
Het is pseudo-code/mysql aangezien het al ontzettend lang geleden is voor mij dat ik voor het laatst iets in sql gemaakt heb

[FragFrog]

Zo te zien is de catagoryId kolom in je movies tabel overbodig, tenzij je hem als hoofdcatagorie wilt gebruiken dan is'ie verkeerd benoemd.

Aangaande je vraag: meerdere velden in een CSL returnen is voor zover ik weet niet mogelijk met MySQL. Wat je wel kan doen is 3 queries uitvoeren die elk een tabel ophalen en in een array zetten en vervolgens in PHP de array's mergen. Niet heel erg moeilijk, wel lekker snel

[Niemand_Anders]

Kijk anders eens naar de Microsoft SQL oplossing 'FOR XML EXPLICIT'.

SQL:

select
1 as Tag,
null as Parent,
m.id as [movie!1!id],
m.name as [movie!1!name],
null as [category!2!name]
from movies

union

select 
2 as Tag,
1 as Parent,
mc.id as [movie!1!id],
null as [movie!1!name],
c.name as [category!2!name]
from moviescategory mc inner join categories c on mc.categoryid=c.id

order by [movie!1!id], Tag, [category!2!name]

Deze query levert een platte presentatie van je xml op.
de eerste regel heeft geen [category!2!name] (null value), terwijl juiste de child records (categories) een null value voor [movie!1!name] opleveren.

Als je het resultaat ziet, zal het principe erachter wel duidelijk worden denk ik. De union(s) worden uiteindelijk via de order by op de juiste manier gesorteert.

[RSD]

Bestaat er in mysql niet iets als implode, zodat ik zoiets kan doen:

SELECT m.id,m.name,(SELECT IMPLODE(c.name,''))

[Verwijderd]

RSD schreef op woensdag 23 januari 2008 @ 14:46:
Bestaat er in mysql niet iets als implode, zodat ik zoiets kan doen:

SELECT m.id,m.name,(SELECT IMPLODE(c.name,''))

Bedoel je mss CONCAT() of begrijp ik je vraag nou niet goed?

[BarthezZ]

Waarom wil je dit eigenlijk zo doen? dingen door je database server laten afhandelen is ook niet heilig.
En door middel van een php script valt dit veel simpeler af te handelen.

[steffex]

maak een array op en loop daar lekker doorheen! 10x makkelijker dan het gepriegel wat je nu doet...

[Jaap-Jan]

Misschien een idee om alle entries uit moviescategories te pakken, daar de movies.name en category.name bij te joinen en te sorteren op movieid. Je hebt alleen, movieid, movies.name en category.name nodig.

Dan heb je wel wat dubbele data, maar je hebt maar één query nodig en het is niet verschrikkelijk ingewikkeld te begrijpen. Vervolgens is het een kwestie van alle records doorlopen. Als je naar het volgende record springt, en movieid is hetzelfde gebleven, dan heb je dus een nieuwe categorie voor die movie. Als movieid verschilt, dan heb je dus een nieuwe film te pakken.

edit:
Volgens mij hetzelfde als wat Spockz doet

[ Voor 9% gewijzigd door Jaap-Jan op 23-01-2008 15:03 ]

[Verwijderd]

kijk eens naar group_concat.. dan selecteer je gewoon als je movies met de daarbij behorende catagorieën, group je op movie en regel je met group_concat je comma seperated catagorie lijstje

[pedorus]

Het mooiste is dan natuurlijk met

SQL:

CONCAT('    <category>',
   GROUP_CONCAT(categories.naam SEPARATOR '</category\n    <category>'),
   '<category\n')

Je gebruikt dan het SqlHtml pattern. Hoef je ook gelijk nooit meer de database-engine te vervangen..

[Verwijderd]

pedorus schreef op woensdag 23 januari 2008 @ 15:55:
Het mooiste is dan natuurlijk met

SQL:

CONCAT('    <category>',
   GROUP_CONCAT(categories.naam SEPARATOR '</category\n    <category>'),
   '<category\n')

Je gebruikt dan het SqlHtml pattern. Hoef je ook gelijk nooit meer de database-engine te vervangen..

dat vind ik helemaal niet mooi.. je gooit 2 datalagen (database en xml) én opmaak (de \n en tabs) allemaal door elkaar heen..

[BHR]

Verwijderd schreef op woensdag 23 januari 2008 @ 16:08:

Tip: kijk even wat thedailywtf.com voor een site is (en bookmarken )

[ Voor 42% gewijzigd door BHR op 23-01-2008 16:19 ]

[Verwijderd]

BHR schreef op woensdag 23 januari 2008 @ 16:18:
[...]
Tip: kijk even wat thedailywtf.com voor een site is (en bookmarken )

thedailywtf.com ken ik al ja, maar ik had de link al helemaal niet bekeken omdat ik het zo'n onzin vond .. Denk ik wijs de TS er ook even op.

[pedorus]

Voor de duidelijkheid: de oplossing zoals in Jaap-Jan's post lijkt me tot nu toe de beste. Dan kun je namelijk gelijk zorgen dat de categorie "Grappen, grollen & onzin" geen probleem is. En je bent niet gebonden aan MySQL.

[RSD]

Wat is que preformance in jullie ogen het beste, de GROUP_CONCAT of een JOIN en dan in php de boel netjes afdrukken. Met GROUP_CONCAT kan ik het lijstje in 1 regel tergu krijgen gescheiden door een komma. In de categorie naam komen alleen cijfers en letters voor en geen andere vreemde tekens dus dat is goed op te lossen. In PHP hoeh ik die GROUP_CONCAT record dan alleen maar te exploden en dan met fopreach de tags eromheen te zetten. De andere vergt wat meer php. Wat is het beste?

[Jaap-Jan]

De eerste vraag die je je moet stellen is hoe belangrijk performance is, of er bottlenecks kunnen zijn. Qua snelheid zal het elkaar waarschijnlijk niet veel ontlopen. Mijn methode levert wat meer dataverkeer op, GROUP_CONCAT vraagt meer processing op de database.

Als je wilt weten hoe belangrijk performance is, moet je je dus gaan afvragen:
1) Hoeveel gebruikers gebruiken het systeem tegelijk?
2) Hoe groot wordt de database qua films en categoriën?

Als je dat soort dingen weet, kun je gaan benchmarken en kijken hoe snel het systeem performt in beide situaties.

Als het antwoord op beide vragen 'niet zoveel' is, kies dan gewoon de methode waar jij het meeste voor voelt.