Select bepaalde columns dynamisch
De volgende query werkt goed;
show columns from table1 where (substr(field,1,2)='c$')
Nu wil ik de resultaten verwerken in een query en de daadwerkelijke data alleen uit die columns halen. Ik wil dus eigenlijk naar de volgende query toe;
select show columns from table1 where (substr(field,1,2)='c$') from table1
Is zoiets mogelijk in mysql?
Is je datamodel wel in orde en genormaliseerd?
De query moet dus altijd een lijst geven met alle data in de custom kolommen, als de gebruiker een nieuwe toevoegd moet deze ook dynamisch in de query meegenomen worden
Het is wel riskant / gevoelig.. maar als je het goed afschermt dan is het wel te doen denk ik :)
Haha, dit wou ik zelf ook weleens in een tool! Alleen ik denk dat het vrij lastig is..
Wat je eventueel we zou kunnen proberen is het in een stored procedure te doen? Die kan alle kolommen fetchen en dat schrijf je dan in een nieuwe query die je uiteindelijk uitvoert :)
Even een grof schetsje, zitten zeker weten nog fouten in :p
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE PROCEDURE naamVanJeFunctie()
BEGIN
DECLARE column VARCHAR(50);
DECLARE thequery TEXT
DECLARE columns TEXT
DECLARE cur1 CURSOR FOR SELECT columnname FROM INFORMATIONSCHEMA.TABLES WHERE tablename = 'table1' AND SCHEMA = 'db';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noresultsleft=1;
SET noresultsleft = 0;
SET thequery = 'SELECT ';
OPEN cur1;
WHILE noresultsleft=0 DO
FETCH cur1 INTO column;
SET columns = CONCAT(column,', ');
END WHILE
SET thequery = CONCAT(thequery,columns,' FROM sometable');
SET noresultsleft = 0;
CLOSE cur1;
PREPARE stmt FROM @thequery;
EXECUTE stmt;
DEALLOCATE stmt;
END;
BEGIN
DECLARE column VARCHAR(50);
DECLARE thequery TEXT
DECLARE columns TEXT
DECLARE cur1 CURSOR FOR SELECT columnname FROM INFORMATIONSCHEMA.TABLES WHERE tablename = 'table1' AND SCHEMA = 'db';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noresultsleft=1;
SET noresultsleft = 0;
SET thequery = 'SELECT ';
OPEN cur1;
WHILE noresultsleft=0 DO
FETCH cur1 INTO column;
SET columns = CONCAT(column,', ');
END WHILE
SET thequery = CONCAT(thequery,columns,' FROM sometable');
SET noresultsleft = 0;
CLOSE cur1;
PREPARE stmt FROM @thequery;
EXECUTE stmt;
DEALLOCATE stmt;
END;
Gewijzigd op 01/01/1970 01:00:00 door kees Schepers
kees schreef op 16.03.2009 13:34:
Najah, ik ken applicaties zat waarbij gebruikers 'vrije velden' kunnen toevoegen, dat is hetzelfde idee als hij heeft denk ik..
Dat geloof ik nooit... Alé, ze kunnen wel eigen veld toevoegen, maar dan niet op deze manier... Kijk eens naar de manier hoe een phpBB2 (ik weet niet op ze het in 3 ook zo doen) zijn configuration tabel opslaat in de database....
Jens
edit:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE `phpbb_config` (
`config_name` varchar(255) NOT NULL,
`config_value` varchar(255) NOT NULL,
PRIMARY KEY (`config_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Gegevens worden uitgevoerd voor tabel `phpbb_config`
--
INSERT INTO `phpbb_config` (`config_name`, `config_value`) VALUES ('config_id', '1'),
('board_disable', '1'),
('sitename', 'sitenaam'),
('site_desc', 'site description'),
('cookie_name', 'phpbb2mysql'),
('cookie_path', '/'),
('cookie_domain', ''),
('cookie_secure', '0'),
('session_length', '3600'),
('allow_html', '1'),
('allow_html_tags', 'b,i,u,pre,object,param,embed'),
('allow_bbcode', '1'),
('allow_smilies', '1'),
('allow_sig', '1'),
enzoverder
`config_name` varchar(255) NOT NULL,
`config_value` varchar(255) NOT NULL,
PRIMARY KEY (`config_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Gegevens worden uitgevoerd voor tabel `phpbb_config`
--
INSERT INTO `phpbb_config` (`config_name`, `config_value`) VALUES ('config_id', '1'),
('board_disable', '1'),
('sitename', 'sitenaam'),
('site_desc', 'site description'),
('cookie_name', 'phpbb2mysql'),
('cookie_path', '/'),
('cookie_domain', ''),
('cookie_secure', '0'),
('session_length', '3600'),
('allow_html', '1'),
('allow_html_tags', 'b,i,u,pre,object,param,embed'),
('allow_bbcode', '1'),
('allow_smilies', '1'),
('allow_sig', '1'),
enzoverder
Gewijzigd op 01/01/1970 01:00:00 door Jens V
Maargoed ik wou de gebruiker alleen maar helpen :) Dat het onveilig/geen is al verteld door andere users.
@KEes; dat ziet er erg chique uit. Is dit allemaal MySQL, dit is volledig nieuw voor mij.. :( Is er ergens een goede tutorial te vinden over het uitvoeren van Stored Procedures?
Bastiaan schreef op 16.03.2009 15:00:
velden aanmaken is een volledig veilige methode waarbij de gebruiker datatypes kan kiezen en het systeem op het meest rustige moment de velden toevoegd.
Dream on boy, dream on....
Al eens gedacht aan wat er gebeurt als de gebruiker velden gaat veranderen en of verwijderen en dan plots systeemvelden tegen komt? Veel plezier om dat op te lossen!
Mij niet gezien....
Gewijzigd op 01/01/1970 01:00:00 door Jens V
Maargoed als er nog mensen zijn die wat info hebben over stored procedures, graag!
Maar reactie(s) van Jens zijn niet al te vriendelijk ben ik het met je eens.
Het is niet de mooiste oplossing dat ben ik met de rest eens, maar ik zelf zat ook weleens in dezelfde situatie waar een oplossing als deze mij een uitkomst had geboden.
Daarnaast is een site die slecht beveiligd is tegen MySQL injection etc net zo onveilig als jij schetst Jens. Dus je moet met dit 'systeem' net zo voorzichtig zijn als anders en dan kan er weinig gebeuren naar mijn idee.
Betreft de stored procedure;
Ja dit is MySQL. In MySQL 5.1 kun je ook stored routines maken en uitvoeren. Je hebt functies en procedures, en een procedure is hetgene wat jij zoekt in dit geval. Ik zelf heb het geleerd tijdens het voorbereiden van mijn MySQL examen uit het MySQL Study Guide boek, maar er is denk ook wel genoeg op internet te vinden. Google eens op 'Stored Routines MySQL'.
Ben benieuwd of je eruit komt!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
users
-----------------
id
...
...
...
custom_fields
-----------------
user_id
fieldname
value
-----------------
id
...
...
...
custom_fields
-----------------
user_id
fieldname
value
Bastiaan schreef op 16.03.2009 16:49:
heb ik nu al niet eerder gezegd dat dit iets is wat ik niet in de hand heb? En iets minder denigrerend mag wel met je "dream on..."
Ik zeg het alleen maar voor je eigen goed... Je database model is waarschijnlijk niet in orde als je het zo gaat doen... Maar ja, jouw database, niet de mijne...
kees schreef op 16.03.2009 17:00:
Maar reactie(s) van Jens zijn niet al te vriendelijk ben ik het met je eens.
Geef eens een voorbeeldje, buiten die hierboven dan?
kees schreef op 16.03.2009 17:00:
Daarnaast is een site die slecht beveiligd is tegen MySQL injection etc net zo onveilig als jij schetst Jens. Dus je moet met dit 'systeem' net zo voorzichtig zijn als anders en dan kan er weinig gebeuren naar mijn idee.
Als je je website slecht beveiligt tegen SQLInjection is dat wederom niet het probleem van het datamodel, maar van je scripting kunsten... Een datamodel waar je gebruikers kolommen van tabellen laat aanmaken en wijzigen is bijna nooit een goed datamodel...
Mvg,
Jens
Gewijzigd op 01/01/1970 01:00:00 door Jens V
kees schreef op 16.03.2009 17:00:
Ja dit is MySQL. In MySQL 5.1 kun je ook stored routines maken en uitvoeren. Je hebt functies en procedures, en een procedure is hetgene wat jij zoekt in dit geval. Ik zelf heb het geleerd tijdens het voorbereiden van mijn MySQL examen uit het MySQL Study Guide boek, maar er is denk ook wel genoeg op internet te vinden. Google eens op 'Stored Routines MySQL'.
Toch iets beter opletten tijdens de les, stored procedures staan sinds versie 5.0 in MySQL. En wanneer je toch beter aan het opletten bent, dynamische datamodellen zijn hét recept voor dramatische performance, er kunnen geen indexen worden gebruikt.
Ik heb MySQL toch al niet zo hoog zitten, maar wat je hier voorschotelt, lijkt ook nog eens aan te tonen dat veel te dure examens ook weggegooid geld zijn.
Ga normaliseren en gooi jouw "dynamische" kolommen in een tabelletje met value-content, dan is er nog iets van te maken. Kun je ook een index gebruiken, dat scheelt weer een slok op een borrel.
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
CREATE TABLE
tabelnaam(
id INT auto_increment primary key,
kolomnaam VARCHAR(255) NOT NULL,
waarde TEXT
);
CREATE INDEX i_kolom ON tabelnaam (kolomnaam);
tabelnaam(
id INT auto_increment primary key,
kolomnaam VARCHAR(255) NOT NULL,
waarde TEXT
);
CREATE INDEX i_kolom ON tabelnaam (kolomnaam);
Dit is al een beroerde "oplossing", maar fysiek dynamische kolommen... Performance wordt om te janken.
Maar goed, MySQL is toch al om te janken. (sorry, moest even)
Ten eerste, ik raad nergens Bastiaan aan om dit te gaan gebruiken, maar aangezien hij toch al heeft besloten om het te gaan doen. Daarom help ik hem graag met een oplossing.
En Frank als jou MySQL niet lekker ligt, prima maar moet dit in al je posts blijken? Lijkt me een beetje overbodig. Als je zo graag haat uitzaait, zou ik zeggen word lid van Ajax en ga Feynoord aanhangers uitschelden ofzo, we hebben het over verschillende database platform die ieder zijn eigenschappen hebben.
En ik ben ook niet perfect net zoals iedereen dus mijn excuus dat ik niet (meer) wist in welke versie dit gekomen was.
Ik zou allemaal eens teurg naar de les normen en waarden gaan, waar je leert anderen mensen te respecteren. En realiseer jezelf wat je toegevoegde waarde is aan dit forum en wat de bedoeling is. (O.a. het helpen van mensen?). Tuurlijk doe je dat wel door iemand te adviseren iets niet te gaan gebruiken, maar aan bijdehand doen hebben we niets aan.
Misschien reageer ik wat overdreven, maar bekijk het eens vanuit de user die dit topic opent.
Ook op het gebied van veiligheid heb je probleem met het door jou voorgestelde model, iedereen heeft rechten nodig op ALTER TABLE. En als er iets onveilig is, dan is dat het wel. Voor je het weet ben je data kwijt, heeft MySQL toch al een handje van, of laat iemand de boel met opzet onderuit gaan door teveel kolommen toe te voegen. Dit wordt echt een hackersfeestje, maak je daar geen zorgen over.
Ik ben geen liefhebber van MySQL omdat deze database onbetrouwbaar is. Zelfs een goed geconfigureerde database kan door iedere database user weer naar de bliksem worden geholpen, ze kunnen allemaal runtime de instelingen aanpassen. Heb jij net je best gedaan om er voor te zorgen dat datums ook echte datums zijn, maakt een gek jouw instellingen weer ongedaan. Zit jij weer opgescheept met de bekende 0000-00-00 datums.... En dat is nog maar een voorbeeldje, laat ik het daar ook maar even bij houden.
Wanneer jij foute oplossingen ook hulp vindt, prima, maar ik denk daar heel anders over. Ik probeer zoveel mogelijk fouten te voorkomen, dat werkt een stuk sneller en veiliger.
Ontopic: Zorg voor een goed datamodel, een zo goed mogelijk datamodel. Gebruikers hebben nooit rechten nodig om kolommen toe te voegen, te veranderen of te verwijderen, die richting kun je dus niet opgaan.