Tutorials
Meer met MySQL
In oudere versies van MySQL was er door het gebrek aan functionaliteit weinig te doen met MySQL zelf, echter is sinds 4.x het arsenaal aan functies sterk gegroeid. Daarom wordt het tijd deze mogelijkheden aan het licht te brengen.
Pagina 1
Rekenkundig I
Net zoals in PHP zijn er de operators.
+ Optellen
- Aftrekken
* Vermenigvuldigen
/ Delen
Hoe werkt dit precies? Best simpel eigenlijk met bv "SELECT 8*5" kan je al 40 terugkrijgen.
In een tabel werkt het als volgt:
Er zijn bv de velden zakken en knikkers in de tabel speeltuin. Om aan alle knikkers te komen gebruik je:
"SELECT (knikkers*zakken) AS totaal_knikkers from speeltuin"
Je vermenigvuldigt de 2 velden met elkaar en geeft het resultaat mee onder de naam totaal_knikkers
Een ander voorbeeld:
Je hebt een teller in een pagina zitten waarvoor je per pagina een row gebruikt die je elke keer wil ophogen per bezoek.
"UPDATE teller SET aantal = (aantal+1) WHERE id_pagina='1'"
je SET het nieuwe aantal als aantal+1, simpel gezegd $aantal++, alleen zonder alle moeite om de waarde eerst uit de database te halen, op te hogen en weer terug te zetten
+ Optellen
- Aftrekken
* Vermenigvuldigen
/ Delen
Hoe werkt dit precies? Best simpel eigenlijk met bv "SELECT 8*5" kan je al 40 terugkrijgen.
In een tabel werkt het als volgt:
Er zijn bv de velden zakken en knikkers in de tabel speeltuin. Om aan alle knikkers te komen gebruik je:
"SELECT (knikkers*zakken) AS totaal_knikkers from speeltuin"
Je vermenigvuldigt de 2 velden met elkaar en geeft het resultaat mee onder de naam totaal_knikkers
Een ander voorbeeld:
Je hebt een teller in een pagina zitten waarvoor je per pagina een row gebruikt die je elke keer wil ophogen per bezoek.
"UPDATE teller SET aantal = (aantal+1) WHERE id_pagina='1'"
je SET het nieuwe aantal als aantal+1, simpel gezegd $aantal++, alleen zonder alle moeite om de waarde eerst uit de database te halen, op te hogen en weer terug te zetten
Pagina 2
Rekenkundig II
Iets moeilijker nu:
Het volgende tabel telt het aantal mensen dat heeft gestemd en berekend het nieuwe gemiddelde uit. Het veld stemmen (integer) telt het aantal stemmen,het
veld average (decimal 4,2) voor de gemiddelde score en het veld enquete_id (integer, primary key) geeft aan welke enquete het is. De op te geven stem loopt van 1 tot 10 en wordt vertegenwoordigd door de variabele $score.
"UPDATE enquete SET
average = (((average*stemmen)+".$score.")/(stemmen+1)),
stemmen = (stemmen+1)
WHERE enquete_id='1'
"
Uitwerking
(average*stemmen)
Vermenigvuldig het gemiddelde met het aantal uitgebrachte stemmen
((average*stemmen)+".$score.")
verhoog dit met de nieuw uitgebrachte stem in $score
(((average*stemmen)+".$score.")/(stemmen+1))
en deel het vervolgens door het nieuw totaal uitgebrachte stemmen
average = (((average*stemmen)+".$score.")/(stemmen+1))
plaats het als nieuwe waarde in het veld average
stemmen = (stemmen+1)
Hoog het aantal uitgebrachte stemmen met 1 op en plaats dat als nieuwe waarde
Op zich is dit al een enquete script, je hoeft alleen nog het formulier en de database connectie erbij te plaatsen.
Het volgende tabel telt het aantal mensen dat heeft gestemd en berekend het nieuwe gemiddelde uit. Het veld stemmen (integer) telt het aantal stemmen,het
veld average (decimal 4,2) voor de gemiddelde score en het veld enquete_id (integer, primary key) geeft aan welke enquete het is. De op te geven stem loopt van 1 tot 10 en wordt vertegenwoordigd door de variabele $score.
"UPDATE enquete SET
average = (((average*stemmen)+".$score.")/(stemmen+1)),
stemmen = (stemmen+1)
WHERE enquete_id='1'
"
Uitwerking
(average*stemmen)
Vermenigvuldig het gemiddelde met het aantal uitgebrachte stemmen
((average*stemmen)+".$score.")
verhoog dit met de nieuw uitgebrachte stem in $score
(((average*stemmen)+".$score.")/(stemmen+1))
en deel het vervolgens door het nieuw totaal uitgebrachte stemmen
average = (((average*stemmen)+".$score.")/(stemmen+1))
plaats het als nieuwe waarde in het veld average
stemmen = (stemmen+1)
Hoog het aantal uitgebrachte stemmen met 1 op en plaats dat als nieuwe waarde
Op zich is dit al een enquete script, je hoeft alleen nog het formulier en de database connectie erbij te plaatsen.
Pagina 3
Functies I
Net zoals in PHP heeft MySQL ook zijn eigen functies, deze kan je gebruiken om een datum te formateren, een string te bewerken of om de tangens van een
waarde uit te rekenen. Hieronder geef ik enkele voorbeelden
Het formateren van een datum:
"SELECT date_format(date, \'%e-%m-%Y\') as outputdate FROM table"
Hiermee formateer je het veld date en laat je het onder de naam outputdate uit
en krijg als outputdate voor een datefield DD-MM-YYYY in plaats van YYYY-MM-DD
%e = dag van de maand (0-31)
%m = maand in nummers (00-12)
%Y = jaar in 4 cijfers
Alle andere opties zijn te vinden op
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html onder de kop DATE_FORMAT
Het bewerken van een string:
Een tabel met de velden voornaam, achternaam en woonplaats wil ik met sql bevragen zodat ik mijn naam een woonplaats in een zin (string) als result krijg
"SELECT CONCAT( voornaam , '' , achternaam , ' wonende te ' , woonplaats ) AS zin from tabel persoonsgegevens.
de waarde zin zou in mijn geval bevatten "Robin Hozee wonende te Hoek van Holland" De functie CONCAT voegt dus effectief verschillende strings uit zowel de
database als scriptmatig ingegeven strings bij elkaar.
Meer string functies zijn er te vinden op:
http://dev.mysql.com/doc/mysql/en/String_functions.html
waarde uit te rekenen. Hieronder geef ik enkele voorbeelden
Het formateren van een datum:
"SELECT date_format(date, \'%e-%m-%Y\') as outputdate FROM table"
Hiermee formateer je het veld date en laat je het onder de naam outputdate uit
en krijg als outputdate voor een datefield DD-MM-YYYY in plaats van YYYY-MM-DD
%e = dag van de maand (0-31)
%m = maand in nummers (00-12)
%Y = jaar in 4 cijfers
Alle andere opties zijn te vinden op
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html onder de kop DATE_FORMAT
Het bewerken van een string:
Een tabel met de velden voornaam, achternaam en woonplaats wil ik met sql bevragen zodat ik mijn naam een woonplaats in een zin (string) als result krijg
"SELECT CONCAT( voornaam , '' , achternaam , ' wonende te ' , woonplaats ) AS zin from tabel persoonsgegevens.
de waarde zin zou in mijn geval bevatten "Robin Hozee wonende te Hoek van Holland" De functie CONCAT voegt dus effectief verschillende strings uit zowel de
database als scriptmatig ingegeven strings bij elkaar.
Meer string functies zijn er te vinden op:
http://dev.mysql.com/doc/mysql/en/String_functions.html
Pagina 4
Functies II
Rekenkundige functies
Je hebt een bestand opgeslagen ter groote van 16448344 bytes in de tabel bestand, nu wil je het bestand in zowel kilobytes als megabytes laten zien aan de
bezoeker van je website, Het veld formaat is een integer van in dit geval tenminste 7 tekens lang waarin de grootte van het bestand staat.
Hoeveel kilobytes is het bestand
"SELECT (formaat/POW(2,10)) AS size FROM bestand"
De functie POW(n,p) berekent het getal van n tot de macht van p, in dit geval is een kilobyte 2 tot de macht van 10 aan bytes, en wordt die onder de naam 'size' in het resultaat teruggegeven.
Voor megabytes welke een byte tot de macht 20 zijn doe je:
"SELECT (formaat/POW(2,20)) AS size FROM bestand"
In combinatie met die functie CONCAT en het toegevoegde veld `naam` kan je het volgende doen:
"SELECT CONCAT('Het bestand ', naam , ' is ', ROUND((formaat/POW(2,10)),0) , ' Kbyte of ' , ROUND((formaat/POW(2,20)),0), ' Mbyte') AS zin FROM bestand"
(formaat/POW(2,10) en (formaat/POW(2,20)
Rekenen het bestandsformaat in kilobytes en megabytes uit.
ROUND((formaat/POW(2,10)),0)
ROUND (n,d) rond het getal n af in het aantal decimalen d, in dit voorbeeld 0 dus.
CONCAT('Het bestand ', naam , ' is ', ROUND((formaat/POW(2,10)),0) , ' Kbyte of ' , ROUND((formaat/POW(2,20)),0), ' Mbyte') AS zin
CONCAT voegt vervolgens de uitgerekende groottes met de door mij toevoegde strings samen onder de noemer 'zin'
zin geeft nu "Het bestand picture.jpg is 16063 Kbyte of 16 Mbyte"
Meer numerische functies zijn te vinden op:
http://dev.mysql.com/doc/mysql/en/Numeric_Functions.html
Je hebt een bestand opgeslagen ter groote van 16448344 bytes in de tabel bestand, nu wil je het bestand in zowel kilobytes als megabytes laten zien aan de
bezoeker van je website, Het veld formaat is een integer van in dit geval tenminste 7 tekens lang waarin de grootte van het bestand staat.
Hoeveel kilobytes is het bestand
"SELECT (formaat/POW(2,10)) AS size FROM bestand"
De functie POW(n,p) berekent het getal van n tot de macht van p, in dit geval is een kilobyte 2 tot de macht van 10 aan bytes, en wordt die onder de naam 'size' in het resultaat teruggegeven.
Voor megabytes welke een byte tot de macht 20 zijn doe je:
"SELECT (formaat/POW(2,20)) AS size FROM bestand"
In combinatie met die functie CONCAT en het toegevoegde veld `naam` kan je het volgende doen:
"SELECT CONCAT('Het bestand ', naam , ' is ', ROUND((formaat/POW(2,10)),0) , ' Kbyte of ' , ROUND((formaat/POW(2,20)),0), ' Mbyte') AS zin FROM bestand"
(formaat/POW(2,10) en (formaat/POW(2,20)
Rekenen het bestandsformaat in kilobytes en megabytes uit.
ROUND((formaat/POW(2,10)),0)
ROUND (n,d) rond het getal n af in het aantal decimalen d, in dit voorbeeld 0 dus.
CONCAT('Het bestand ', naam , ' is ', ROUND((formaat/POW(2,10)),0) , ' Kbyte of ' , ROUND((formaat/POW(2,20)),0), ' Mbyte') AS zin
CONCAT voegt vervolgens de uitgerekende groottes met de door mij toevoegde strings samen onder de noemer 'zin'
zin geeft nu "Het bestand picture.jpg is 16063 Kbyte of 16 Mbyte"
Meer numerische functies zijn te vinden op:
http://dev.mysql.com/doc/mysql/en/Numeric_Functions.html
Pagina 5
Overwegingen
Waarom kiezen voor MySQL als je het al in PHP kan?
MySQL zal vaak een betere performance geven, maar toch is niet altijd even handig om alles binnen MySQL te doen.
Hoe zwaarder je database wordt gebruikt des te beter is het gebruik van MySQL als scripttaal. Resultsets zullen veel kleiner worden nadat je de informatie al hebt bewerkt in de MySQL server. Hierdoor worden de database en webserver minder belast.
Bij simpele gastboeken, nieuwsberichten en andere kleine toepassingen is het vaak niet nodig om je code te optimaliseren, zowel PHP en MySQL zullen het werk efficient kunnen afhandelen.
Is deze hele tutorial dan helemaal voor niks geweest?
Nee, ook binnen kleine applicaties valt er winst met mySQL te behalen. Met simpele funcies kan je redelijk wat PHP scripting overslaan waardoor je het er toch iets prettiger/overzichtelijker gaat uitzien. Je kunt het natuurlijk ook zien als een opstap naar het serieuze SQL werk!
Als iemand aan SQL denkt komen als eerst de woorden UPDATE, INSERT en SELECT naar boven. Maar je kan veel meer met (My)SQL, En wie wil nou niet een query result gelijk in een pagina plaatsen zonder in php allerlei functies er op los te laten?
Voor meer voorbeelden moet ik jullie verwijzen naar:
http://dev.mysql.com/doc/mysql/en/String_functions.html
http://dev.mysql.com/doc/mysql/en/Numeric_Functions.html
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
Denk er aan welke functie bij welke versie van MySQL hoort. Hoe hoger de versie van MySQL des te groter wordt het aanbod van functies.
Veel plezier!
MySQL zal vaak een betere performance geven, maar toch is niet altijd even handig om alles binnen MySQL te doen.
Hoe zwaarder je database wordt gebruikt des te beter is het gebruik van MySQL als scripttaal. Resultsets zullen veel kleiner worden nadat je de informatie al hebt bewerkt in de MySQL server. Hierdoor worden de database en webserver minder belast.
Bij simpele gastboeken, nieuwsberichten en andere kleine toepassingen is het vaak niet nodig om je code te optimaliseren, zowel PHP en MySQL zullen het werk efficient kunnen afhandelen.
Is deze hele tutorial dan helemaal voor niks geweest?
Nee, ook binnen kleine applicaties valt er winst met mySQL te behalen. Met simpele funcies kan je redelijk wat PHP scripting overslaan waardoor je het er toch iets prettiger/overzichtelijker gaat uitzien. Je kunt het natuurlijk ook zien als een opstap naar het serieuze SQL werk!
Als iemand aan SQL denkt komen als eerst de woorden UPDATE, INSERT en SELECT naar boven. Maar je kan veel meer met (My)SQL, En wie wil nou niet een query result gelijk in een pagina plaatsen zonder in php allerlei functies er op los te laten?
Voor meer voorbeelden moet ik jullie verwijzen naar:
http://dev.mysql.com/doc/mysql/en/String_functions.html
http://dev.mysql.com/doc/mysql/en/Numeric_Functions.html
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
Denk er aan welke functie bij welke versie van MySQL hoort. Hoe hoger de versie van MySQL des te groter wordt het aanbod van functies.
Veel plezier!
Reacties
0