[MySQL&PHP] Table/field-informatie koppelen aan resultaat

[r0bert]

Voor het project waar ik momenteel aan werk, wil ik gebruik maken van een generiek gedeelte om queries uit te voeren. Deze query-resultaten moeten voorzien zijn van aanvullende informatie van de resultaatvelden. Ik gebruik PDO om de queries uit te voeren, maar ik denk dat dat er niet veel toe zal doen. Ik zie vooral problemen wanneer in twee databases, tabbelen bestaan met dezelfde naam.

Voorbeeldquery:

code:

SELECT *
   FROM `dbKlant1`.`klantGegevens`,
        `dbKlant2`.`klantGegevens`

En om maar even helemaal een praktisch realistische casus op te stellen, heeft dbKlant2.klantGegevens 2 extra velden tussen de anderen.

Met PDO kan ik de tabelnaam eventueel nog achterhalen, maar volgens mij niet de database. Het zal dus moeilijk zijn om een SHOW COLUMNS query te koppelen? Ik kan de informatie wel opslaan in Arrays, maar om de dubbele tabelnaam te kunnen hanteren zal de databasenaam hierin ook een index moeten zijn, waardoor vanuit het MySQL-resultaat de terugkoppeling niet meer te maken is.

Hopelijk is mijn probleem duidelijk en kan iemand me op weg helpen?

Edit: Aliassen gebruiken voor de tabellen vind ik meer een workaround dan een oplossing - en ook niet erg mooi/generiek.

[ Voor 5% gewijzigd door r0bert op 29-07-2008 15:51 ]

[Face_-_LeSS]

Wanneer je data uit een database trekt dan weet is de database naam toch al bekend...?

[Krooswijk.com]

Weet niet zeker of ik begrijp wat je bedoelt, maar je kunt je output fields bepalen met:

SQL:

SELECT k1.id AS klant1_id, k2.id AS klant2_id
   FROM `dbKlant1`.`klantGegevens` AS k1,
        `dbKlant2`.`klantGegevens` AS k2

[mr_derk]

r0bert schreef op dinsdag 29 juli 2008 @ 15:50:

En om maar even helemaal een praktisch realistische casus op te stellen, heeft dbKlant2.klantGegevens 2 extra velden tussen de anderen.

uitgaande van bovenstaande tekst denk ik dat je de tabellen moet joinen (outer join). De records met lege waardes zijn dan afkomstig van dbKlant1. Evt. kan je ook nog de functie database() gebruiken in mysql.

- SQLFAQ: Programming FAQ - SQL

En wat is er mis met aliassen?

[ Voor 27% gewijzigd door mr_derk op 29-07-2008 16:39 ]

[r0bert]

Ik zal proberen iets duidelijk voorbeeld te geven.

Mijn input bestaat uit XML-formaat. Daarbij worden tables opgesomt met daaraan een namespace. Deze XML wordt omgezet naar SQL en dan uitgevoerd. Van de resultaten wil ik dus achterhalen aan welke table - en daarmee namespace - ze gekoppelt waren.

Wanneer table-namen uniek zijn kan ik het me nog bedenken hoe dat werkt, echter met dubbele tabelnamen uit verschillende databases ben ik de kluts kwijt.

Beetje psuedo:

code:

dbKlant1.klantGegevens => xmlns = urn:klant2:gegevens
dbKlant2.klantGegevens => xmlns = urn:klantJan:info

select * from dbKlant1.klantGegevens, dbKlant2.klantGegevens

resultaat:

klantId
klantNaam

klantId
klantNaam

klantId
klantNaam

>> allemaal afkomstig uit tabel klantGegevens (zoals met PDO te achterhalen is). Om de namespace te vinden heb ik de database nodig of had ik van tevoren gegevens vast moet leggen (hoe?)

Misschien komt dit iets verder?

code:

En wat is er mis met aliassen?

Gewoon niet mooi.

Dat komt me te ver in de buurt van

str1 =
str2 =
str3 =

ipv

str[] =
str[] =
str[] =

En deels kwestie van persoonelijke voorkeur.

[Verwijderd]

r0bert schreef op woensdag 30 juli 2008 @ 15:37:

code:

En wat is er mis met aliassen?

Gewoon niet mooi.

Dat vind ik nou van SQL, dat vind ik gewoon niet mooi. Daarom gebruik ik simpelweg geen SQL.

[r0bert]

Zoals het voorbeeld wat er erbij geeft, het is een soort van verkapte workaround. Je geeft je tabellen een naam - i.c.m. de database (en evt. server) zelfs een unieke naam - en vervolgens ga je aliassen om er een andere unieke naam van te maken. Als ik in de hele applicatie de data in de tabel met de tabelnaam benader, wil ik niet in een ander stukje ineens gaan aliassen.

Ik vind SQL trouwens sowieso lelijk Misschien ben ik een beetje verwend qua programmeren Maar ik zoek dus een andere oplossing waardoor ik ook de aansluiting met de rest van mijn applicatie behoudt.

[Verwijderd]

r0bert schreef op woensdag 30 juli 2008 @ 16:01:
Zoals het voorbeeld wat er erbij geeft, het is een soort van verkapte workaround. Je geeft je tabellen een naam - i.c.m. de database (en evt. server) zelfs een unieke naam - en vervolgens ga je aliassen om er een andere unieke naam van te maken.

Nee, je gebruikt een alias om meer duidelijkheid te verschaffen:

code:

select
  werkgever.naam werkgever_naam,
  werknemer.naam werknemer_naam
from
  persoon werkgever,
  persoon werknemer
where
  ....

[r0bert]

Of, zoals boven beschreven juist meer onduidelijkheid en inconsistentie. Persoonlijke vind ik - nogmaals - werkgever.naam ook mooier. Omdat je de correcte syntax aanhoudt en je geen gegevens als brondatabase verloren laat gaan.

Maar afgezien van die bijzaak, draagt het niet erg bij aan mijn oplossing aangezien dit generiek moet zijn voor willekeurige input en vanuit de rest van de (xml)applicatie naar verwezen moet worden. Zijn er technieken met SQL bekent dat er extra metagegevens toegevoegd of uitgelezen kunnen worden? Ik heb ze nml niet kunnen vinden via Google

[r0bert]

Maar goed, zelfs met aliassen kom ik er niet netjes uit.

Enige methode die ik zie als ik ga aliassen, is om er een numeric id aan toe te voegen als 'table1','table2'...'table14'.

Want als ik een soort van concat toe gaan passen, kan ik weer dit soort situaties krijgen:

code:

db                table
Artikel         . VoorrraadBestelling             AS   ArtikelVoorraadBestelling
ArtikelVoorraad . Bestelling                      AS   ArtikelVoorraadBestelling

Oftewel, dat heb ik nog geen unieke namen.

Maar de numerieke aliassing (met iterator) is weer lastig als er door de user vanuit subqueries, condities, functies of transacties naar de normale tabelnaam verwezen wordt

[P.O. Box]

ik geloof niet dat ik je probleem echt begrijp, maar toch een poging tot iets:

code:

select 'artikelvoorraad', *
from artikelvoorraad
union
select 'artikel', *
from artikel

zoiets? je eerste veld uit een record is dan je tabelnaam...

[Hydra]

r0bert schreef op vrijdag 01 augustus 2008 @ 14:01:
Enige methode die ik zie als ik ga aliassen, is om er een numeric id aan toe te voegen als 'table1','table2'...'table14'.

Ik zie je bijdrage op TheDailyWTF met plezier tegemoed.

[glashio]

Is de PHP methode mysql_fetch_field niet wat je zoekt ? Dit zou moeten werken als je de tabelnamen as aliases meegeeft.
Alsvolgt :

SQL:

SELECT *
   FROM `dbKlant1`.`klantGegevens` `dbKlant1.klantGegevens` ,
        `dbKlant2`.`klantGegevens` `dbKlant2.klantGegevens`

[r0bert]

Edwardvb schreef op vrijdag 01 augustus 2008 @ 14:32:
ik geloof niet dat ik je probleem echt begrijp, maar toch een poging tot iets:

code:

select 'artikelvoorraad', *
from artikelvoorraad
union
select 'artikel', *
from artikel

zoiets? je eerste veld uit een record is dan je tabelnaam...

Dat is het geloof ik niet helemaal nee. Ik wil van een resultfield kunnen bepalen uit welke table en database het komt - ook bij gelijke naamgeving (maar bijv andere database). De fieldname en tablename kan ik al achterhalen via PDO (gebruikte extensie voor uitvoeren van de queries), maar de database is hier niet te herleiden, dus zoek ik een soort van workaround. Misschien is er wel iets mogelijk met een deel van jouw oplossing, maar ik zie nog niet direct hoe?

Hydra schreef op vrijdag 01 augustus 2008 @ 14:54:
[...]


Ik zie je bijdrage op TheDailyWTF met plezier tegemoed.

Ik zie liever een alternatief, en op zijn minst een argument dat dit technisch minder is dan 'ArtikelenDatabaseKlant', 'ArtikelenDatabaseVoorraad'. Dat is alleen voor humane interactie nuttig. Echter wordt mijn probleem voorafgaand in het topic voldoende toegelicht en is het misschien minder gepast om de populist uit te gaan hangen door de 'DailyWTF' rond te bazuinen. Sorry voor mijn geraaktheid, maar ik vind het een ongepaste reactie.

glashio schreef op vrijdag 01 augustus 2008 @ 15:21:
Is de PHP methode mysql_fetch_field niet wat je zoekt ? Dit zou moeten werken als je de tabelnamen as aliases meegeeft.
Alsvolgt :

SQL:

SELECT *
   FROM `dbKlant1`.`klantGegevens` `dbKlant1.klantGegevens` ,
        `dbKlant2`.`klantGegevens` `dbKlant2.klantGegevens`

Ziet er netjes uit inderdaad, maar ik betwijfel of deze syntax/naamgeving valid SQL is. Zal het eens nazoeken.

Face_-_LeSS schreef op dinsdag 29 juli 2008 @ 15:55:
Wanneer je data uit een database trekt dan weet is de database naam toch al bekend...?

Jazeker, het probleem is hoe ik die gegevens die vooraf bekend zijn, achteraf kan koppelen aan een resultfield?

[ Voor 71% gewijzigd door r0bert op 01-08-2008 16:36 ]

[Voutloos]

r0bert schreef op vrijdag 01 augustus 2008 @ 16:29:
[...]

Ik zie liever een alternatief, en op zijn minst een argument dat ...

Jij komt juist niet met een goed argument om aliassen te mijden.

Sorry voor mijn geraaktheid, maar ik vind het een ongepaste reactie.

Je kent TDWTF blijkbaar, dus dan kan je ook weten dat een groot deel van de db WTF's hierop lijkt. Wees blij dat je vroegtijdig (desnoods wat bot) gewaarschuwd wordt.

Ziet er netjes uit inderdaad, maar ik betwijfel of deze syntax/naamgeving valid SQL is. Zal het eens nazoeken.

Dit had mark platvoet al netjes voorgedaan? Hop hop hop.

[r0bert]

Ik heb tot nu toe helaas inderdaad geen andere oplossing dan aliassen. Ik heb zeker een aantal argumenten genoemd over intercompatibiliteit met de rest van de applicatie en interne verwijzingen naar de originele tabelnaam (in queries, transacties, views) waarmee verwarring zou kunnen ontstaan binnen de applicatie, maar zolang ik geen alternatief heb, is het inderdaad de beste oplossing.

Dit had mark platvoet al netjes voorgedaan? Hop hop hop.

Ik ben het juist niet eens met het argument dat het meer duidelijk verschaft, omgekeerd - maar nogmaals heb ik inderdaad geen betere oplossing. Dus nu maar proberen via die oplossing het resultaat te bereiken

Daarbij zie ik niet in waarom 'table13' een betere naamgeving zou zijn dan 'werknemer.klant' aangezien alles toch in een array-achtige collection verzamelt zal moeten worden waarbij het alias als index gebruikt zal worden

Edit:
http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

There are some restrictions on the characters that may appear in identifiers:
- Database and table names cannot contain “/”, “\”, “.”, or characters that are not allowed in filenames.

Het is me niet helemaal duidelijk of dit nu ook voor aliases geldt?

The set of alphanumeric characters from the current character set, “_”, and “$” are not special.

Ik denk dat het dan maar iets moet worden als:

code:

SELECT * 
   FROM 
      `Artikel`.`VoorraadBestelling` AS `VoorraadBestelling$1`, 
      `ArtikelVoorraad`.`Bestelling` AS `Bestelling$2`

/en\

SELECT * 
   FROM 
      `dbKlant1`.`KlantGegevens` AS `KlantGegevens$7`, 
      `dbKlant2`.`KlantGegevens` AS `KlantGegevens$12`

Waarbij dus een uniek numeriek ID (die mijn XML elementen zelf al hebben).

Ik sta open voor andere oplossing of suggesties/verbeteringen.

[ Voor 50% gewijzigd door r0bert op 01-08-2008 17:00 ]

[Hydra]

r0bert schreef op vrijdag 01 augustus 2008 @ 16:29:
Ik zie liever een alternatief, en op zijn minst een argument dat dit technisch minder is dan 'ArtikelenDatabaseKlant', 'ArtikelenDatabaseVoorraad'. Dat is alleen voor humane interactie nuttig. Echter wordt mijn probleem voorafgaand in het topic voldoende toegelicht en is het misschien minder gepast om de populist uit te gaan hangen door de 'DailyWTF' rond te bazuinen. Sorry voor mijn geraaktheid, maar ik vind het een ongepaste reactie.

Sorry hoor, maar je

• Bent eigenwijs
• Gebruikt verschillende tabellen voor dezelfde data (slecht genormaliseerd)
• Wil kolomnamen gaan nummeren
• Mixed parameters door 'tabel'namen (urn:klantJan:info)
• Wil geen standaard functionaliteit gebruiken omdat dat (SQL) 'lelijk' is
• Creeert afhankelijkheden waar die niet horen ('brondatabase' in je tabel/kolomnamen)
• Creeert problemen omdat je om een slechtgenormaliseerd database model heen hacked

Dat tabelnamen alleen leesbaar moeten zijn voor 'human' interaction is complete kolder. Tabelnamen beschrijven wat ze bevatten, zelfde geldt voor kolommen. Als iemand een keer die software moet gaan onderhouden levert dat een hoop extra kosten op omdat het design gewoon brak is.

[r0bert]

Hydra schreef op vrijdag 01 augustus 2008 @ 16:55:
[...]
Sorry hoor, maar je
• Bent eigenwijs

Valt mee, ik weet wat ik wil en ben daar misschien te gefocussed op. Zal andere oplossingen uitproberen, zoals nu met aliassen.

• Gebruikt verschillende tabellen voor dezelfde data (slecht genormaliseerd)

Ligt iets lastiger. Zoals ik zei wordt de query aangevoerd door een willekeurige client/klant. Deze kunnen ook hun eigen tabellenstructuur maken en dan ga ik er dus inderdaad maar vanuit dat het slecht genormaliseerd is. Toch wil ik graag dat mijn script er wel mee om kan gaan

• Wil kolomnamen gaan nummeren

Tabelaliassen, om zo een unieke reference te hebben om de databasenaam te kunnen achterhalen ( waar dit topic voor was). De nummering zorgt ervoor dat het altijd uniek is, wat bij het concatten van namen niet altijd zo zal zijn zoals in een eerder voorbeeld te zien is.

• Mixed parameters door 'tabel'namen (urn:klantJan:info)

Dat moet je me even uitleggen, snap niet precies wat je bedoelt?

• Wil geen standaard functionaliteit gebruiken omdat dat (SQL) 'lelijk' is

Jawel, maar er is meer dan aliassen en ik hoef me niet tot SQL te beperken aangezien vanuit XML/PHP de query al op wordt gezet. Mooiste zou zijn om van tevoren metadata te registreren en koppelen aan het SQL resultaat. Met alleen de SQL standaard is mijn probleem volgens mij niet op te lossen? Zou het graag doen hoor.

• Creeert afhankelijkheden waar die niet horen ('brondatabase' in je tabel/kolomnamen)

Ik moet weten uit welke database de data komt, en dat kan niet met de normale resultset van PDO. Hoezo afhankelijkheden die er niet horen?

• Creeert problemen omdat je om een slechtgenormaliseerd database model heen hacked

Zoals eerder gezegd mag een slechte normalisatie geen blokkade voor het script zijn.

Dat tabelnamen alleen leesbaar moeten zijn voor 'human' interaction is complete kolder. Tabelnamen beschrijven wat ze bevatten, zelfde geldt voor kolommen. Als iemand een keer die software moet gaan onderhouden levert dat een hoop extra kosten op omdat het design gewoon brak is.

De normale tabel en kolomnamen worden ingevult door klanten of eventueel mijzelf. Daar zal niets mis mee zijn (of juist wel ), maar het gaat hier om het aliassen om een unieke reference te hebben.
_{Voor een systeem/computer heeft `table54` natuurlijk geen nuttigere betekenis dan `VoorraadArtikelen`, dat was mijn punt wat ik probeer duidelijk te maken.}

Ik geloof dat mijn probleem niet helemaal helder is in de topicstart, maar ik zoek dus een manier om van de velden van mijn resultset de brondatabase te achterhalen, zonder dat daarbij inconsistentie zal ontstaan (bijvoorbeeld dat door concatten gelijke aliassen ontstaan). Het is dus een script wat het toestaat om MySQL queries als custom XML in te voeren, en de uitvoer netjes in XML is

Verder stel ik alle reacties zeer op prijs en hecht waarde aan de tijd die ervoor genomen is. Sommige oplossingen zijn echter in mijn geval minder goed toepasbaar dan andere. Dat iemand mijn brainstorm dan een dailywtf gaat noemen, moet ik dan maar als feedback nemen.

[ Voor 3% gewijzigd door r0bert op 01-08-2008 17:13 ]

[Voutloos]

r0bert schreef op vrijdag 01 augustus 2008 @ 17:10:
Tabelaliassen, om zo een unieke reference te hebben om de databasenaam te kunnen achterhalen ( waar dit topic voor was). De nummering zorgt ervoor dat het altijd uniek is, wat bij het concatten van namen niet altijd zo zal zijn zoals in een eerder voorbeeld te zien is.

Een nummering legt hier niets uit, terwijl er wel wellicht iets te verklaren valt: Waarom wordt die 1e tabel gebruikt, waarom wordt die 2e gebruikt? Je gebruikt toch ook niet enkel $var1, $var2 etc in je code?

[r0bert]

Input:

XML:

database dbKlant 1 -> namespace /klant/
  table KlantGegevens
     field klantId
     field klantNaam -> namespace /klant/extended/

database dbKlant 2
   table KlantGegevens -> namespace /klant/
      field klantId
      field klantNaam

Query:

SQL:

SELECT 
    `dbKlant1`.`KlantGegevens`.`klantId`,
    `dbKlant1`.`KlantGegevens`.`klantNaam`,
    `dbKlant2`.`KlantGegevens`.`klantId`,
    `dbKlant2`.`KlantGegevens`.`klantNaam`
        FROM
    `dbKlant1`.`KlantGegevens`
    `dbKlant2`.`KlantGegevens`

Resultset:

PHP:

Array(
    [0] => Array(
        ['klantId'] => '1',
        ['klantNaam'] => 'Jan Janssen'
    ),
    [1] => Array(
        ['klantId'] => '1',
        ['klantNaam'] => 'Gerrit Jan'
    )
)

Nu moet `klantNaam` 'Jan Janssen' een andere namespace krijgen (/klant/extended) dan 'Gerrit Jan' (zie xml input). Dat is zo wel zelf te zien, maar nu moet mijn computer dat nog begrijpen.

Dát is het probleem, en daarom lijkt mij nummering (id) i.c.m. aliassing zoals in het begin van het topic door mensen gesuggereerd wordt tot nu toe de enige oplossing?

Met PDO kan ik tot 'KlantGegevens' achterhalen, maar dat is in deze testcase niet voldoende om de juiste namespace te vinden.

[ Voor 27% gewijzigd door r0bert op 02-08-2008 11:57 ]

[r0bert]

Het is gelukt met aliassing, beetje grove workaround, maar tot nu toe nog geen bugs tegen gekomen. Sta open voor alternatieven maar tot zover iig bedankt.