28. juni 2010 - 20:52Der er
14 kommentarer og 1 løsning
SELECT fra tre tabeller - og sortering af data
Jeg sysler med en udvidelse til et CMS, der byder på en række af de samme funktionaliteter som Microsoft Access - herunder oprettelse af formularer (til indtastning af data) samt generering af rapporter (output). Det er muligt at oprette felter af følgende seks typer i formularer:
Disse data ønsker jeg at lagre i databasen - men værdien af et afkrydsningsfelt, en rulleliste eller en alternativknap er jo i princippet blot et 0 eller et 1-tal i databasen. Det ville næsten være spild af ressourcer at lagre dem i eksempelvis et felt af typen TEXT - selvom det i princippet kunne rumme alle datatyperne. Jeg har derfor valgt følgende tabelstruktur - med henblik på at undgå at drive unødig rovdrift på ressourcerne:
custom_values_text ( value_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT, value_field MEDIUMINT(8) NOT NULL DEFAULT '0', value_value VARCHAR(255) NOT NULL DEFAULT '', value_record MEDIUMINT(8) NOT NULL DEFAULT '0', PRIMARY KEY (value_id) ) TYPE=MyISAM;
custom_values_textarea ( value_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT, value_field MEDIUMINT(8) NOT NULL DEFAULT '0', value_value TEXT NOT NULL, value_record MEDIUMINT(8) NOT NULL DEFAULT '0', PRIMARY KEY (value_id) ) TYPE=MyISAM;
custom_values_select ( value_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT, value_field MEDIUMINT(8) NOT NULL DEFAULT '0', value_value MEDIUMINT(8) NOT NULL DEFAULT '0', value_record MEDIUMINT(8) NOT NULL DEFAULT '0', PRIMARY KEY (value_id) ) TYPE=MyISAM;
Som det fremgår er den eneste forskel på de tre tabeller typen af feltet value_value (hvori værdien lagres). De har - med andre ord - den samme struktur. Delen med at oprette formularer og indsætte data i de tre tabeller fungerer fint - men det driller lidt, når jeg skal til at generere rapporterne (dvs. udskrive dataene fra de tre tabeller). Jeg har kigget i retning af UNION - med henblik på at lave en SELECT fra alle tre tabeller på én gang. Ikke nok med det, så skal jeg have jo rent faktisk også have alle records "grupperet" (dvs. at når jeg udskriver mine data i en HTML-tabel med mine data, så udgør hver "record" én række). Er der nogle bud på, hvordan sådan et udtryk kunne se ud?
Jeg håber det giver lidt mening - sig endelig til, hvis I har behov for at jeg uddyber noget.
Foruden de tre ovennævnte tabeller har jeg i øvrigt de her fire - men det er vist underordnet:
custom_forms ( form_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT, form_title VARCHAR(200) NOT NULL DEFAULT '', form_start INT(10) UNSIGNED NOT NULL DEFAULT '0', form_end INT(10) UNSIGNED NOT NULL DEFAULT '0', form_fields TEXT NOT NULL, PRIMARY KEY (form_id) ) TYPE=MyISAM;
custom_fields ( field_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT, field_type VARCHAR(100) NOT NULL DEFAULT '', field_label VARCHAR(200) NOT NULL DEFAULT '', field_parent MEDIUMINT(8) NOT NULL DEFAULT '0', PRIMARY KEY (field_id) ) TYPE=MyISAM;
custom_reports ( report_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT, report_title VARCHAR(200) NOT NULL DEFAULT '', report_start INT(10) UNSIGNED NOT NULL DEFAULT '0', report_end INT(10) UNSIGNED NOT NULL DEFAULT '0', report_fields TEXT NOT NULL, PRIMARY KEY (report_id) ) TYPE=MyISAM;
custom_values_records ( record_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT, record_user MEDIUMINT(8) NOT NULL DEFAULT '0', record_ip VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY (record_id) ) TYPE=MyISAM;
1) Hvis du skal have hentet data fra 3 tabeller "under hinanden" så er UNION korrekt.
2) Jeg tror ikke at det vil være et problem at gemme data som TEXT og dermed have en enkelt tabel. Men hvis det er så måske en enkelt tabel med et VARCHAR og et TEXT felt, hvor TEXT feltet så indeholder det udover længden af VARCHAR feltet.
Har du mulighed for at konkretisere dit forslag til et udtryk?
Jeg vurderer, at TEXT er uhensigtsmæssigt til visse datatyper - så jeg tror umiddelbart jeg vil fastholde min tabelstruktur, medmindre nogle har alternative forslag til en tabelstruktur, hvor jeg fortsat har forskellige felttyper til de forskellige datatyper.
3 byte er ikke en størrelse som naturligt brugs af computere d.v.s. at alt laves på 4 byte (INT) og det så pakkes ned i og op fra 3 bytes når det skal bruges.
Og størrelses forskellen mellem 3 og 4 byte per tal må være aldeles ubetydeligt.
Jeg foreslog faktisk to strukturer - begge enkelt tabel:
custom_values ( value_id INT NOT NULL AUTO_INCREMENT, value_field INT NOT NULL DEFAULT '0', value_value TEXT NOT NULL, value_record INT NOT NULL DEFAULT '0', PRIMARY KEY (value_id) ) TYPE=MyISAM;
eller
custom_values ( value_id INT NOT NULL AUTO_INCREMENT, value_field INT NOT NULL DEFAULT '0', value_value VARCHAR(255) NOT NULL DEFAULT '', value_xvalue TEXT NOT NULL, value_record INT NOT NULL DEFAULT '0', PRIMARY KEY (value_id) ) TYPE=MyISAM;
Desværre er jeg ikke så garvet bruger af eksperten.dk - så det var ikke lige gået op for mig, hvordan man tildelte point. Hvis du poster et svar, så skal jeg tildele dig de 100 point.
@arne_v: Hov, jeg havde vist overset et af dine spørgsmål i farten - men ja, det var vist snarere et spørgsmål om religion end performance, men jeg kan godt se, at dit forslag til en tabelstruktur er mere passende. Én tabel til værdierne er at foretrække - så jeg har valgt den første:
custom_values ( value_id INT NOT NULL AUTO_INCREMENT, value_field INT NOT NULL DEFAULT '0', value_value TEXT NOT NULL, value_record INT NOT NULL DEFAULT '0', PRIMARY KEY (value_id) ) TYPE=MyISAM;
Imidlertid afføder det et andet "problem" - dog af lidt mindre art. Jeg ved ikke, om jeg burde oprette en ny tråd, da der er tale om et nyt problem - men eftersom det er afledt af dette tror jeg, jeg holder det i samme tråd:
Jeg har - foruden tabellen custom_values - fortsat en tabel, der hedder custom_values_records:
custom_values_records ( record_id INT NOT NULL AUTO_INCREMENT, record_user INT NOT NULL DEFAULT '0', record_ip VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY (record_id) ) TYPE=MyISAM;
Den indeholder et ID (et autonummereringsfelt), et felt indeholdende en oplysning om hvilken bruger der har indtastet de pågældende oplysninger, samt et felt der hedder record_ip (indeholdende brugerens IP-adresse).
Mine SQL-udtryk og de dertilhørende while-løkker er ikke for kønne:
$result = dbquery("SELECT DISTINCT record_id FROM ".DB_CUSTOM_VALUES_RECORDS." tr INNER JOIN ".DB_CUSTOM_VALUES." tv ON tr.record_id = tv.value_record WHERE tv.value_field IN(".implode(",", $report_fields).") "); while ($data = dbarray($result)) { $result2 = dbquery("SELECT * FROM ".DB_CUSTOM_VALUES." WHERE value_field IN(".implode(",", $report_fields).") AND value_record='".$data['record_id']."' "); while ($data2 = dbarray($result2)) { echo "<td>".$data2['value_value']."</td>\n"; } echo "</tr>\n<tr>\n"; }
Meningen med det første SQL-udtryk er, at jeg ønsker at finde frem til alle unikke record_id'er - som vel at mærke hører til den pågældende rapport. Bagefter har jeg - indeni while-loopet - en ny SQL-forespørgsel, der henter værdien i tabellen custom_values_records. Det giver det rigtige resultat, men det må næsten kunne gøres lidt mere elegant?
@mike1963: Jeg er bange for, at jeg ikke helt har forstået i hvilken forstand du mener NOT NULLs "koster"? UNION-problematikken er vist ikke så aktuel længere, idet jeg har valgt at benytte arne_v's forslag til en tabelstruktur, hvorved jeg lagrer alle mine værdier i én tabel.
$result = dbquery("SELECT * FROM ".DB_CUSTOM_VALUES_RECORDS." tr INNER JOIN ".DB_CUSTOM_VALUES." tv ON tr.record_id = tv.value_record WHERE tv.value_field IN(".implode(",", $report_fields).") "); while ($data = dbarray($result)) { echo "<td>".$data['value_value']."</td>\n"; // test om behov for at skifte tr }
@arne_v: Den løsning har jeg overvejet - og forsøgt. Jeg var umiddelbart meget tilfreds - men argumentet for ikke at bruge den er, at den er meget sårbar i den forstand, at hvis der mangler en værdi i databasen, så forskubbes det hele. Lad mig lige forsøge at konkretisere min lidt abstrakte forklaring af problemstillingen:
Lad os sige, at jeg har medtaget fem felter i min rapport - og har oprettet to poster, som ser ud som følgende:
Når jeg udskriver data ser den ud som følgende - og det er jo umiddelbart meget godt:
Navn Adresse Postnummer By Telefonnummer Test Testersen Testvej 1 9999 Testby 99999999 Tester Test Testparken 2 8888 Testborg 88888888
Efterfølgende beslutter jeg så, at jeg gerne vil redigere min rapport og medtage endnu et felt, der hedder "betalt" - og så har vi balladen:
Navn Adresse Postnummer By Telefonnummer Betalt Test Testersen Testvej 1 9999 Testby 99999999 Tester Test Testparken 2 8888 Testborg 88888888
Nu bliver alle mine data "forskubbet" - hvilket jo ikke er så elegant. Forklaringen er, at de to poster jo ikke indeholder nogen værdi tilhørende feltet "betalt" - jeg indsætter nemlig altid en post i databasen for hvert felt (også selvom et felt står tomt - så er value_value bare tom). Men "betalt" eksisterede ikke, da de to poster blev oprettet - så når der mangler en værdi, så går det hele i smadder.
Den løsning jeg er kommet frem til har den fordel, at den blot efterlader "betalt" blankt. Her forskubbes data altså ikke - selvom jeg tilføjer nye felter efterfølgende.
Tak for tippet. Det burde jeg have sagt mig selv - men så kom jeg da en løsning det nærmere. Nu ser min kode ud som følgende:
$record_id = ""; $result = dbquery("SELECT * FROM ".DB_CUSTOM_VALUES_RECORDS." tr INNER JOIN ".DB_CUSTOM_VALUES." tv ON tr.record_id = tv.value_record WHERE tv.value_field IN(".implode(",", $report_fields).") "); while ($data = dbarray($result)) { if ($record_id != $data['record_id']) { if ($record_id != "") echo "</tr>\n<tr>\n"; $record_id = $data['record_id']; } echo "<td>".$data['value_value']."</td>\n"; }
Nu har jeg nemlig fået styr på "rækkeskiftene" - nu er der (tilsyneladende) blot ét problem tilbage:
Hvis jeg tilføjer kolonnen "betalt" mangler der jo en td (dvs. en kolonne) i visse rækker. Det problem har jeg forsøgt at afhjælpe ved at lave en tæller, der holder styr på det og tilføjer eventuelle manglende td'er ("tomme") i forlængelse af rækken:
<td> </td>
Men den tilføjer dem blot i slutningen af rækken - jeg kan ikke lige gennemskue, hvordan jeg kan tilføje manglende td'er inde midt i et sted. Det lykkedes mig kun at tilføje dem i forlængelse af rækken, hvorved dataene jo ikke kommer til at stå korrekt. Hvis nu jeg tilføjer en kolonne ("betalt") inde midt i et sted - eksempelvis som kolonne 3 ud af 6...
Mon det kan løses på en eller anden måde?
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
