Tutorials
SQL Beginners handleiding
Een handleiding voor de eerste kennismaking met SQL. Up-to-date versie: SQL Beginnershandleiding
Pagina 1
Inleiding
Welkom bij deze tutorial over het gebruik van SQL. In deze tutorial zal ik aan de hand van vele voorbeelden proberen je de basis van het gebruik van SQL en het werken met de database bij te brengen.
Wat is SQL?
De afkorting SQL staat voor Structured Query Language en is een taal die gebruikt wordt om een communiceren met een database. En dan hebben we het niet over één enkele database, maar over een hele reeks. Binnen de webontwikkeling zijn MySQL en PostgreSQL de twee database systemen die het meest gebruikt worden, maar ook andere databases als Firebird, DB2, MSSQL en Oracle werken bijvoorbeeld met SQL.
Omdat de taal op zoveel verschillende systemen moet werken, zijn er in de loop der jaren standaards voor ontwikkeld. De ANSI (American National Standards Institute) standaard is daarvan waarschijnlijk de meest bekende. Maar ondanks deze standaard hebben de meeste database systemen hun eigen SQL dialect, waarvan de een meer afwijkt van de ANSI standaard dan de ander. Voor de basisfunctionaliteiten zoals invoegen, wijzigen en verwijderen van data is het gelukkig wel mogelijk om dezelfde code te gebruiken.
Voorkennis
Voordat je deze tutorial leest, is het makkelijk als je al een redelijke basiskennis van het programeren in PHP hebt. Hoewel PHP en SQL twee totaal verschillende talen zijn, leg ik in deze tutorial wel de directe link ertussen. Mocht je deze kennis nog niet bezitten, overweeg om dan eerst mijn PHP Beginners handleiding te lezen.
Deze tutorial
In deze tutorial zal ik een introductie in het gebruik van SQL geven. Naast de voorbeelden van code die gebruikt worden om data in de database te selecteren of manipuleren, zal ik ook aangeven hoe je de verbinding vanuit PHP maakt en hoe je in PHP met de verkregen data om kunt gaan.
Omdat MySQL tot op heden de meest gebruikte database binnen webontwikkeling is, zal ik daar voor de voorbeelden in deze tutorial ook gebruik van maken. Helaas is MySQL wel een database die het niet zo nauw neemt met de ANSI standaarden. In deze tutorial voldoet echter elke regel SQL, tenzij duidelijk anders vermeld, aan de ANSI standaarden. Dat betekent dat dezelfde code op elke andere database te gebruiken is.
Wat heb ik nodig?
Om deze tutorial makkelijk te kunnen volgen en de voorbeelden makkelijk uit te kunnen voeren, heb je de beschikking over een MySQL database server nodig. Daarnaast wordt het gebruik van phpMyAdmin om de database te beheren aangeraden.
Het makkelijkste is het om een lokale test server te installeren met een totaal pakket als WAMP. Dit pakket bevat alles dat je nodig hebt voor deze tutorial.
Changelog
23-01-2008 - Van elk voorbeeld waarin PHP gebruikt wordt en de basishandelingen uitgelegd worden, is nu ook de PDO variant in de tutorial opgenomen. Vooralsnog enkel via deze link te vinden.
Opmerking: op PHPHulp heb ik helaas niet heel veel opties met betrekking tot de layout. Een duidelijkere versie van deze tutorial kun je hier vinden: SQL Beginners handleiding
Wat is SQL?
De afkorting SQL staat voor Structured Query Language en is een taal die gebruikt wordt om een communiceren met een database. En dan hebben we het niet over één enkele database, maar over een hele reeks. Binnen de webontwikkeling zijn MySQL en PostgreSQL de twee database systemen die het meest gebruikt worden, maar ook andere databases als Firebird, DB2, MSSQL en Oracle werken bijvoorbeeld met SQL.
Omdat de taal op zoveel verschillende systemen moet werken, zijn er in de loop der jaren standaards voor ontwikkeld. De ANSI (American National Standards Institute) standaard is daarvan waarschijnlijk de meest bekende. Maar ondanks deze standaard hebben de meeste database systemen hun eigen SQL dialect, waarvan de een meer afwijkt van de ANSI standaard dan de ander. Voor de basisfunctionaliteiten zoals invoegen, wijzigen en verwijderen van data is het gelukkig wel mogelijk om dezelfde code te gebruiken.
Voorkennis
Voordat je deze tutorial leest, is het makkelijk als je al een redelijke basiskennis van het programeren in PHP hebt. Hoewel PHP en SQL twee totaal verschillende talen zijn, leg ik in deze tutorial wel de directe link ertussen. Mocht je deze kennis nog niet bezitten, overweeg om dan eerst mijn PHP Beginners handleiding te lezen.
Deze tutorial
In deze tutorial zal ik een introductie in het gebruik van SQL geven. Naast de voorbeelden van code die gebruikt worden om data in de database te selecteren of manipuleren, zal ik ook aangeven hoe je de verbinding vanuit PHP maakt en hoe je in PHP met de verkregen data om kunt gaan.
Omdat MySQL tot op heden de meest gebruikte database binnen webontwikkeling is, zal ik daar voor de voorbeelden in deze tutorial ook gebruik van maken. Helaas is MySQL wel een database die het niet zo nauw neemt met de ANSI standaarden. In deze tutorial voldoet echter elke regel SQL, tenzij duidelijk anders vermeld, aan de ANSI standaarden. Dat betekent dat dezelfde code op elke andere database te gebruiken is.
Wat heb ik nodig?
Om deze tutorial makkelijk te kunnen volgen en de voorbeelden makkelijk uit te kunnen voeren, heb je de beschikking over een MySQL database server nodig. Daarnaast wordt het gebruik van phpMyAdmin om de database te beheren aangeraden.
Het makkelijkste is het om een lokale test server te installeren met een totaal pakket als WAMP. Dit pakket bevat alles dat je nodig hebt voor deze tutorial.
Changelog
23-01-2008 - Van elk voorbeeld waarin PHP gebruikt wordt en de basishandelingen uitgelegd worden, is nu ook de PDO variant in de tutorial opgenomen. Vooralsnog enkel via deze link te vinden.
Opmerking: op PHPHulp heb ik helaas niet heel veel opties met betrekking tot de layout. Een duidelijkere versie van deze tutorial kun je hier vinden: SQL Beginners handleiding
Pagina 2
De eerste tabel
Een database is opgebouwd uit een of meedere tabellen. Deze tabellen bevatten op hun beurt een of meedere kolommen waarin verschillende soorten data opgeslagen kan worden. De werkelijke data bevindt zich in rijen (records) waarvan elk apart deel van de data zich in een aparte kolom bevindt.
Een plaatje zegt meer dan woorden, dus laten we de tabel waar we straks mee gaan werken eens visualiseren:
Dit is de tabel werknemers waar we in deze tutorial veel mee zullen werken. Ik zal straks uitleggen hoe we deze tabel aan kunnen maken, maar laten we eerst eens naar de opbouw kijken.
Zoals ik al zei is de naam van deze tabel werknemers. We zien dat de tabel bestaat uit 6 kolommen met respectievelijke de namen id, voornaam, tussenvoegsel, achternaam, geboortedatum, salaris_schaal. De namen van de kolommen geven al aan wat voor soort data erin opgeslagen wordt.
Tenslotte zien we dat deze tabel al drie records met data bevat. Straks zullen we terugkomen op de inhoud van deze records, maar eerst zal ik uitleggen hoe we deze tabel aan kunnen maken.
Aanmaken van de tabel: de query
Om de database iets te laten doen, geven we hem opdrachten geschreven in SQL, zogenaamde queries. Elk type query heeft een bepaalde syntax die voor elk type anders is. De syntax van elk soort query kun je terugvinden in de MySQL handleiding. Zo ook de syntax voor het aanmaken van een nieuwe tabel.
Om de tabel uit bovenstaand voorbeeld te creëren gebruiken we de volgende SQL query:
We zien dat de query begint met CREATE TABLE dat aangeeft dat we een nieuwe tabel willen maken. Dit statement wordt gevolgd door de naam van de tabel en daarna volgen tussen haakjes de kolommen van de tabel.
De verschillende kolommen worden gescheiden door een komma en in bovenstaand voorbeeld stelt elke regel dus een aparte kolom voor. Elke regel begint met de naam van de kolom gevolgd door het datatype en de lengte (over de verschillende datatypen komen we zodadelijk te spreken). Vervolgens volgt eventueel de optie NOT NULL dat betekent dat de kolom altijd een waarde moet hebben. Als dit niet het geval is zal er altijd een default waarde opgegeven moeten worden welke overigens ook NULL kan zijn.
Andere opties die nog op deze regel voor kunnen komen zijn bijvoorbeeld het voor MySQL specifieke auto_increment. Deze optie wordt op een kolom van het type INT aangebracht en zorgt ervoor dat bij elk nieuw record dat ingevoegd wordt, deze kolom een unieke opeenvolgende waarde krijgt.
Na de kolommen volgen nog enkele regels waarin we verschillende sleutels voor de tabel aan kunnen maken. De verschillende soorten sleutels en hun functie komen later in deze tutorial aan bod. De allerlaatste regel van deze query geeft de engine aan die we gebruiken. Ook dit is weer specifiek voor MySQL en wij zullen altijd de InnoDB engine gebruiken. Waarom we dat doen zal later blijken in het gedeelte over het relationele aspect van de database.
Omdat het aanmaken van een tabel vrij specifiek is voor elke database, is deze query ook alleen te gebruiken in een MySQL omgeving. Dit komt onder andere door de auto_increment en de engine die we definiëren.
Aanmaken van de tabel: het uitvoeren
Het aanmaken van tabellen is een proces dat je voor een applicatie normaal gesproken maar één keer uitvoert. De CREATE TABLE query is dan ook een query die je niet regelmatig zult gebruiken, voornamelijk ook omdat je er waarschijnlijk voor kiest om tabellen aan te maken met een interface zoals phpMyAdmin.
Om nu deze tabel aan te maken, zou je bovenstaande query in de SQL tab van phpMyAdmin kunnen uitvoeren. Zorg wel dat je eerst zelf al een database hebt aangemaakt waar je de tabel in kwijt kunt. Als je niet weet hoe dat moet, raadpleeg dan je host of kijk eens goed rond in phpMyAdmin indien je een lokale server (WAMP bijvoorbeeld) gebruikt.
Naamgeving
Net zoals bij variabelen in PHP is het ook hier belangrijk dat je duidelijk namen voor je tabellen en kolommen kiest. Probeer in de namen enkele letters en eventueel underscores te gebruiken, dan weet je al bijna zeker dat een naam ook geldig is. De laatste vereiste is dat de naam niet voorkomt in de lijst met gereserveerde woorden.
Helaas is het grote nadeel van MySQL dat deze ook namen toestaat die eigenlijk helemaal niet mogelijk zijn. Zo is een naam als z-index helemaal geen probleem in deze database. Dit komt doordat MySQL in zijn queries standaard backticks (`) gebruikt. Dit is echter niet gewenst in ANSI SQL en wij zullen het dan ook nooit gebruiken. Wij zorgen gewoon dat onze tabel en kolomnamen wel in orde zijn!
Datamodel
De structuur van tabellen binnen een database noem je een datamodel. Als je grotere systemen gaat bouwen is het belangrijk dat je gaat normaliseren om zo een goed datamodel te verkrijgen. Het doel is om data in de database nooit dubbel op te slaan.
Het normaliseren van een datamodel valt echter buiten het onderwerp van deze tutorial, maar meer informatie hierover kun je lezen in deze tutorial.
Datatypen
Bij het aanmaken van een nieuwe tabel, geef je voor elke kolom aan wat voor soort data erin komt te staan. Dit doe je door een datatype aan die kolom toe te kennen.
Veelgebruikte datatypen zijn:
[li]INT - Integers[/li]
[li]DECIMAL - Decimale getalen[/li]
[li]VARCHAR - Strings[/li]
[li]TEXT - Langere stukken tekst[/li]
[li]DATE - Datum[/li]
[li]DATETIME - Combinatie van datum en tijd[/li]
Sommige van deze datatypen zijn specifiek voor MySQL, andere database systemen kennen hun eigen datatypes. Raadpleeg daarom altijd de handleiding van je database om te zien welke datatypen je tot je beschikking heb. Voor MySQL is een overzicht te vinden in hoofdstuk 9 van de MySQL handleiding.
Een plaatje zegt meer dan woorden, dus laten we de tabel waar we straks mee gaan werken eens visualiseren:
+----+----------+---------------+------------+---------------+----------------+
| id | voornaam | tussenvoegsel | achternaam | geboortedatum | salaris_schaal |
+----+----------+---------------+------------+---------------+----------------+
| 1 | Nico | de | Boer | 1958-03-24 | 18 |
| 2 | Tim | NULL | Janssen | 1982-01-30 | 10 |
| 3 | Pim | NULL | Vosse | 1980-12-20 | 10 |
+----+----------+---------------+------------+---------------+----------------+
Dit is de tabel werknemers waar we in deze tutorial veel mee zullen werken. Ik zal straks uitleggen hoe we deze tabel aan kunnen maken, maar laten we eerst eens naar de opbouw kijken.
Zoals ik al zei is de naam van deze tabel werknemers. We zien dat de tabel bestaat uit 6 kolommen met respectievelijke de namen id, voornaam, tussenvoegsel, achternaam, geboortedatum, salaris_schaal. De namen van de kolommen geven al aan wat voor soort data erin opgeslagen wordt.
Tenslotte zien we dat deze tabel al drie records met data bevat. Straks zullen we terugkomen op de inhoud van deze records, maar eerst zal ik uitleggen hoe we deze tabel aan kunnen maken.
Aanmaken van de tabel: de query
Om de database iets te laten doen, geven we hem opdrachten geschreven in SQL, zogenaamde queries. Elk type query heeft een bepaalde syntax die voor elk type anders is. De syntax van elk soort query kun je terugvinden in de MySQL handleiding. Zo ook de syntax voor het aanmaken van een nieuwe tabel.
Om de tabel uit bovenstaand voorbeeld te creëren gebruiken we de volgende SQL query:
CREATE TABLE werknemers (
id int(8) NOT NULL auto_increment,
voornaam varchar(50) NOT NULL,
tussenvoegsel varchar(4) default NULL,
achternaam varchar(75) NOT NULL,
geboortedatum date NOT NULL,
salaris_schaal int(2) default '10',
PRIMARY KEY (id)
) ENGINE=InnoDB
We zien dat de query begint met CREATE TABLE dat aangeeft dat we een nieuwe tabel willen maken. Dit statement wordt gevolgd door de naam van de tabel en daarna volgen tussen haakjes de kolommen van de tabel.
De verschillende kolommen worden gescheiden door een komma en in bovenstaand voorbeeld stelt elke regel dus een aparte kolom voor. Elke regel begint met de naam van de kolom gevolgd door het datatype en de lengte (over de verschillende datatypen komen we zodadelijk te spreken). Vervolgens volgt eventueel de optie NOT NULL dat betekent dat de kolom altijd een waarde moet hebben. Als dit niet het geval is zal er altijd een default waarde opgegeven moeten worden welke overigens ook NULL kan zijn.
Andere opties die nog op deze regel voor kunnen komen zijn bijvoorbeeld het voor MySQL specifieke auto_increment. Deze optie wordt op een kolom van het type INT aangebracht en zorgt ervoor dat bij elk nieuw record dat ingevoegd wordt, deze kolom een unieke opeenvolgende waarde krijgt.
Na de kolommen volgen nog enkele regels waarin we verschillende sleutels voor de tabel aan kunnen maken. De verschillende soorten sleutels en hun functie komen later in deze tutorial aan bod. De allerlaatste regel van deze query geeft de engine aan die we gebruiken. Ook dit is weer specifiek voor MySQL en wij zullen altijd de InnoDB engine gebruiken. Waarom we dat doen zal later blijken in het gedeelte over het relationele aspect van de database.
Omdat het aanmaken van een tabel vrij specifiek is voor elke database, is deze query ook alleen te gebruiken in een MySQL omgeving. Dit komt onder andere door de auto_increment en de engine die we definiëren.
Aanmaken van de tabel: het uitvoeren
Het aanmaken van tabellen is een proces dat je voor een applicatie normaal gesproken maar één keer uitvoert. De CREATE TABLE query is dan ook een query die je niet regelmatig zult gebruiken, voornamelijk ook omdat je er waarschijnlijk voor kiest om tabellen aan te maken met een interface zoals phpMyAdmin.
Om nu deze tabel aan te maken, zou je bovenstaande query in de SQL tab van phpMyAdmin kunnen uitvoeren. Zorg wel dat je eerst zelf al een database hebt aangemaakt waar je de tabel in kwijt kunt. Als je niet weet hoe dat moet, raadpleeg dan je host of kijk eens goed rond in phpMyAdmin indien je een lokale server (WAMP bijvoorbeeld) gebruikt.
Naamgeving
Net zoals bij variabelen in PHP is het ook hier belangrijk dat je duidelijk namen voor je tabellen en kolommen kiest. Probeer in de namen enkele letters en eventueel underscores te gebruiken, dan weet je al bijna zeker dat een naam ook geldig is. De laatste vereiste is dat de naam niet voorkomt in de lijst met gereserveerde woorden.
Helaas is het grote nadeel van MySQL dat deze ook namen toestaat die eigenlijk helemaal niet mogelijk zijn. Zo is een naam als z-index helemaal geen probleem in deze database. Dit komt doordat MySQL in zijn queries standaard backticks (`) gebruikt. Dit is echter niet gewenst in ANSI SQL en wij zullen het dan ook nooit gebruiken. Wij zorgen gewoon dat onze tabel en kolomnamen wel in orde zijn!
Datamodel
De structuur van tabellen binnen een database noem je een datamodel. Als je grotere systemen gaat bouwen is het belangrijk dat je gaat normaliseren om zo een goed datamodel te verkrijgen. Het doel is om data in de database nooit dubbel op te slaan.
Het normaliseren van een datamodel valt echter buiten het onderwerp van deze tutorial, maar meer informatie hierover kun je lezen in deze tutorial.
Datatypen
Bij het aanmaken van een nieuwe tabel, geef je voor elke kolom aan wat voor soort data erin komt te staan. Dit doe je door een datatype aan die kolom toe te kennen.
Veelgebruikte datatypen zijn:
[li]INT - Integers[/li]
[li]DECIMAL - Decimale getalen[/li]
[li]VARCHAR - Strings[/li]
[li]TEXT - Langere stukken tekst[/li]
[li]DATE - Datum[/li]
[li]DATETIME - Combinatie van datum en tijd[/li]
Sommige van deze datatypen zijn specifiek voor MySQL, andere database systemen kennen hun eigen datatypes. Raadpleeg daarom altijd de handleiding van je database om te zien welke datatypen je tot je beschikking heb. Voor MySQL is een overzicht te vinden in hoofdstuk 9 van de MySQL handleiding.
Pagina 3
De database benaderen vanuit PHP
Om de database vanuit PHP te benaderen hebben we een groot aantal mogelijkheden tot onze beschikking. De betere manieren om met een database te communiceren zijn bijvoorbeeld via een 'database abstraction layer' zoals PDO of door in het specifieke geval van MySQL gebruik te maken van de MySQLi extensie binnen PHP.
Helaas zijn er op moment van schrijven van deze tutorial nog heel veel hosts die (een van) eerder genoemde methoden niet ondersteund. Omdat ik een tutorial wil schrijven waar iedereen mee uit de voeten kan, zal ik in deze voorbeelden gebruik maken van de mysql_* functies die PHP ons biedt.
Opmerking: de mysql_* functies worden vanaf PHP6 uitgefaseerd en zullen daarna voorgoed verdwijnen. Als je nieuwe scripts schrijft doe je er verstandig aan om voor bijvoorbeeld PDO of MySQLi te kiezen.
Voor meer informatie over de betere manieren kun je terecht in een van onderstaande tutorials:
[li]PDO - Verbinden met verschillende databases[/li]
[li]MySQLi - Een nieuwe manier van interactie tussen PHP en MySQL[/li]
Toevoeging PDO voorbeelden (23-01-2008)
Aangezien zoals gezegd de mysql_* functies vanaf PHP6 uitgefaseerd worden, heb ik besloten om van elk gegeven PHP voorbeeld, die de basishandelingen van het werken met de database laten zien, ook de PDO variant in deze tutorial op te nemen. Helaas heb ik hier niet de mogelijkheid om paginas aan deze tutorial toe te voegen, de aanvulligen zijn dus vooralsnog enkel via onderstaande link te vinden:
SQL Beginnershandleiding
Verbinden met de database en MySQL in ANSI mode zetten
Om in PHP verbinding te maken met de database, gebruiken we het volgende stukje code:
[code=db_config.php]
<?php
$db = array (
'host' => 'localhost',
'user' => 'root',
'pass' => '',
'dbname' => 'test'
);
if(!mysql_connect($db['host'], $db['user'], $db['pass']))
{
trigger_error('Fout bij verbinden: '.mysql_error());
}
elseif(!mysql_select_db($db['dbname']))
{
trigger_error('Fout bij selecteren database: '.mysql_error());
}
else
{
$sql = "SET SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY'";
if(!mysql_query($sql))
{
trigger_error('MySQL in ANSI niet mogelijk');
}
}
?>
[/code]
Dit scriptje slaan we op als db_config.php en gebruiken we overal waar we een database verbinding nodig hebben. Wat we feitelijk doen is allereerst een array $cfg aanmaken die een viertal gegevens bevat:
[li]host - De hostname waar de database server te vinden is. Standaard is dat localhost.[/li]
[li]user - De gebruikersnaam om in te loggen op de database server.[/li]
[li]pass - Het bijbehorende wachtwoord.[/li]
[li]dbname - De naam van de database waarmee we willen werken[/li]
Verander deze gegevens dus even zodat ze kloppen voor jouw eigen situatie.
Met mysql_connect() proberen we een verbinding te maken met de database, als dat niet lukt geven we een foutmelding met behulp van trigger_error(). Vervolgens selecteren we met behulp van mysql_select_db() de database die we willen gaan gebruiken.
Tenslotte voeren we een sql query uit die MySQL in de ANSI mode zet. Want zoals ik in de inleiding al zei, neemt MySQL het niet zo nauw met de ANSI standaard. Gelukkig kunnen we MySQL op deze manier dwingen om zich meer aan die standaard te houden.
Op elke pagina waar we vervolgens een database verbinding nodig hebben, plaatsen we:
Uitvoeren van queries
Voor het uitvoeren van queries zal ik in alle voorbeelden een vaste syntax in PHP gebruiken. Deze ziet er als volgt uit:
In de variabele $sql zetten we de query die we willen uitvoeren. Vervolgens voeren we met mysql_query() deze query uit en controleren we of deze query gelukt is. Als dat niet het geval is, gebruiken we trigger_error() om een foutmelding te geven. Het is belangrijk om in die foutmelding zowel de foutmelding van MySQL, op te halen met mysql_error(), en de gebruikte query weer te geven. Dat maakt het debuggen een stuk eenvoudiger.
Dit is de basis waarmee we in PHP gaan werken. We zullen zien dat dit stukje script telkens weer in onze code terugkomt.
Helaas zijn er op moment van schrijven van deze tutorial nog heel veel hosts die (een van) eerder genoemde methoden niet ondersteund. Omdat ik een tutorial wil schrijven waar iedereen mee uit de voeten kan, zal ik in deze voorbeelden gebruik maken van de mysql_* functies die PHP ons biedt.
Opmerking: de mysql_* functies worden vanaf PHP6 uitgefaseerd en zullen daarna voorgoed verdwijnen. Als je nieuwe scripts schrijft doe je er verstandig aan om voor bijvoorbeeld PDO of MySQLi te kiezen.
Voor meer informatie over de betere manieren kun je terecht in een van onderstaande tutorials:
[li]PDO - Verbinden met verschillende databases[/li]
[li]MySQLi - Een nieuwe manier van interactie tussen PHP en MySQL[/li]
Toevoeging PDO voorbeelden (23-01-2008)
Aangezien zoals gezegd de mysql_* functies vanaf PHP6 uitgefaseerd worden, heb ik besloten om van elk gegeven PHP voorbeeld, die de basishandelingen van het werken met de database laten zien, ook de PDO variant in deze tutorial op te nemen. Helaas heb ik hier niet de mogelijkheid om paginas aan deze tutorial toe te voegen, de aanvulligen zijn dus vooralsnog enkel via onderstaande link te vinden:
SQL Beginnershandleiding
Verbinden met de database en MySQL in ANSI mode zetten
Om in PHP verbinding te maken met de database, gebruiken we het volgende stukje code:
[code=db_config.php]
<?php
$db = array (
'host' => 'localhost',
'user' => 'root',
'pass' => '',
'dbname' => 'test'
);
if(!mysql_connect($db['host'], $db['user'], $db['pass']))
{
trigger_error('Fout bij verbinden: '.mysql_error());
}
elseif(!mysql_select_db($db['dbname']))
{
trigger_error('Fout bij selecteren database: '.mysql_error());
}
else
{
$sql = "SET SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY'";
if(!mysql_query($sql))
{
trigger_error('MySQL in ANSI niet mogelijk');
}
}
?>
[/code]
Dit scriptje slaan we op als db_config.php en gebruiken we overal waar we een database verbinding nodig hebben. Wat we feitelijk doen is allereerst een array $cfg aanmaken die een viertal gegevens bevat:
[li]host - De hostname waar de database server te vinden is. Standaard is dat localhost.[/li]
[li]user - De gebruikersnaam om in te loggen op de database server.[/li]
[li]pass - Het bijbehorende wachtwoord.[/li]
[li]dbname - De naam van de database waarmee we willen werken[/li]
Verander deze gegevens dus even zodat ze kloppen voor jouw eigen situatie.
Met mysql_connect() proberen we een verbinding te maken met de database, als dat niet lukt geven we een foutmelding met behulp van trigger_error(). Vervolgens selecteren we met behulp van mysql_select_db() de database die we willen gaan gebruiken.
Tenslotte voeren we een sql query uit die MySQL in de ANSI mode zet. Want zoals ik in de inleiding al zei, neemt MySQL het niet zo nauw met de ANSI standaard. Gelukkig kunnen we MySQL op deze manier dwingen om zich meer aan die standaard te houden.
Op elke pagina waar we vervolgens een database verbinding nodig hebben, plaatsen we:
<?php
require_once 'db_config.php';
?>
Uitvoeren van queries
Voor het uitvoeren van queries zal ik in alle voorbeelden een vaste syntax in PHP gebruiken. Deze ziet er als volgt uit:
<?php
$sql = "";
if(!$res = mysql_query($sql))
{
trigger_error(mysql_error().'<br />In query: '.$sql);
}
?>
In de variabele $sql zetten we de query die we willen uitvoeren. Vervolgens voeren we met mysql_query() deze query uit en controleren we of deze query gelukt is. Als dat niet het geval is, gebruiken we trigger_error() om een foutmelding te geven. Het is belangrijk om in die foutmelding zowel de foutmelding van MySQL, op te halen met mysql_error(), en de gebruikte query weer te geven. Dat maakt het debuggen een stuk eenvoudiger.
Dit is de basis waarmee we in PHP gaan werken. We zullen zien dat dit stukje script telkens weer in onze code terugkomt.
Pagina 4
Invoegen van records
Nu we een eigen tabel gemaakt hebben en gezien hebben hoe we de database vanuit PHP kunnen benaderen, is het tijd om eens wat gegevens aan onze tabel toe te gaan voegen.
Syntax
Voor het invoegen van records gebruiken we een INSERT query. De syntax van zo'n query ziet er als volgt uit:
De query beginnen we met INSERT INTO gevolgd door de naam van de tabel waarin we records willen invoegen. Achter de tabelnaam staan tussen haakjes de kolommen in welke we gegevens weg gaan schrijven. In het VALUES gedeelte geven we vervolgens de waarden op die we in elke kolom willen invoegen.
Invoegen van een record (SQL)
De INSERT query voor het invoegen van de eerste rij uit onze tabel ziet er als volgt uit:
Allereerst valt ons op dat we de kolom id niet in onze INSERT query terug zien. Dat komt omdat we een auto_increment op die kolom hebben staan waardoor MySQL automatisch een waarde toekent.
Verder valt te zien dat we strings in SQL net zo goed tussen quotes zetten als in PHP. Aangezien we ook hier enkele quotes gebruiken, zullen we zien dat we in PHP dubbele quotes gaan gebruiken voor onze SQL queries.
Invoegen van een record (PHP)
Als we deze query in PHP willen uitvoeren, zou dat er als volgt uitzien:
De query hebben we nu in de variabele $sql opgenomen, uitgevoerd en gecontroleerd op fouten. Als je dit scriptje uitvoert zul je hoogstwaarschijnlijk een witte lege pagina te zien krijgen. Dat is in dit geval de bevestiging dat het gelukt is. Merk ook nog even de dubbele quotes op die we rond onze query gebruikt hebben, op die manier hoeven we de enkele quotes in de query niet te escapen.
Omdat we een auto_increment in de tabel hebben staan, kunnen we met mysql_insert_id() het id bepalen van het laatste ingevoegde record. De variabele $id zal nu deze dus dit id bevatten. Dit heb je natuurlijk lang niet altijd nodig, maar soms komt het erg van pas.
Meerdere records tegelijk invoegen (SQL)
Het is ook mogelijk om met één query meerdere records in te voegen. Dit doen we door de VALUES van de verschillende records te scheiden met een komma:
Het enige dat aan deze query anders is ten opzichte van de eerste INSERT query, is dat er nu twee records tegelijkertijd ingevoegd worden. Verder is alles hetzelfde.
Meedere records tegelijk invoegen (PHP)
In PHP zou deze bewerking er als volgt uitzien:
Zoals we zien is hier ook niet veel verschil met de vorige code om een record in te voegen.
Het invoegen van meerdere records kan op een veel efficiëntere manier, namelijk door het gebruik van een prepared statement. Onder andere PDO en MySQLi bieden deze functionaliteit:
[li]PDO - Prepared statements[/li]
[li]MySQLi - Prepared statements[/li]
Invoegen van gebruikersinput
Een belangrijke regel binnen PHP is dat input van een gebruiker nooit te vertrouwen is. Je zult dus ook te allen tijde moeten controleren of de data wel is dat jij denkt dat het is. Zo ook moet input beveiligd worden voordat je het in een query kunt gebruiken.
Stel je een fictieve tabel voor waarin een voor- en achternaam ingevoerd kunnen worden en laat de voor- en achternaam uit een formulier komen:
We gebruiken nu de functie mysql_real_escape_string() om de input vanuit het formulier te beveiligen. Vervolgens gebruiken we de veilige variabelen $voornaam en $achternaam in de query. Zouden we dit niet doen, dan is ons script namelijk gevoelig voor SQL injectie, een veiligheidslek.
Syntax
Voor het invoegen van records gebruiken we een INSERT query. De syntax van zo'n query ziet er als volgt uit:
INSERT INTO tabelnaam
(
kolomnaam,
kolomnaam
)
VALUES
(
waarde,
waarde
)
De query beginnen we met INSERT INTO gevolgd door de naam van de tabel waarin we records willen invoegen. Achter de tabelnaam staan tussen haakjes de kolommen in welke we gegevens weg gaan schrijven. In het VALUES gedeelte geven we vervolgens de waarden op die we in elke kolom willen invoegen.
Invoegen van een record (SQL)
De INSERT query voor het invoegen van de eerste rij uit onze tabel ziet er als volgt uit:
INSERT INTO werknemers
(
voornaam,
tussenvoegsel,
achternaam,
geboortedatum,
salaris_schaal
)
VALUES
(
'Nico',
'de',
'Boer',
'1958-03-24',
18
)
Allereerst valt ons op dat we de kolom id niet in onze INSERT query terug zien. Dat komt omdat we een auto_increment op die kolom hebben staan waardoor MySQL automatisch een waarde toekent.
Verder valt te zien dat we strings in SQL net zo goed tussen quotes zetten als in PHP. Aangezien we ook hier enkele quotes gebruiken, zullen we zien dat we in PHP dubbele quotes gaan gebruiken voor onze SQL queries.
Invoegen van een record (PHP)
Als we deze query in PHP willen uitvoeren, zou dat er als volgt uitzien:
<?php
require_once 'db_config.php';
$sql = "
INSERT INTO werknemers
(
voornaam,
tussenvoegsel,
achternaam,
geboortedatum,
salaris_schaal
)
VALUES
(
'Nico',
'de',
'Boer',
'1958-03-24',
18
)
";
if(!$res = mysql_query($sql))
{
trigger_error(mysql_error().'<br />In query: '.$sql);
}
else
{
$id = mysql_insert_id();
}
?>
De query hebben we nu in de variabele $sql opgenomen, uitgevoerd en gecontroleerd op fouten. Als je dit scriptje uitvoert zul je hoogstwaarschijnlijk een witte lege pagina te zien krijgen. Dat is in dit geval de bevestiging dat het gelukt is. Merk ook nog even de dubbele quotes op die we rond onze query gebruikt hebben, op die manier hoeven we de enkele quotes in de query niet te escapen.
Omdat we een auto_increment in de tabel hebben staan, kunnen we met mysql_insert_id() het id bepalen van het laatste ingevoegde record. De variabele $id zal nu deze dus dit id bevatten. Dit heb je natuurlijk lang niet altijd nodig, maar soms komt het erg van pas.
Meerdere records tegelijk invoegen (SQL)
Het is ook mogelijk om met één query meerdere records in te voegen. Dit doen we door de VALUES van de verschillende records te scheiden met een komma:
INSERT INTO werknemers
(
voornaam,
tussenvoegsel,
achternaam,
geboortedatum,
salaris_schaal
)
VALUES
(
'Tim',
'',
'Janssen',
'1982-01-30',
10
),
(
'Pim',
'',
'Vosse',
'1982-01-30',
10
)
Het enige dat aan deze query anders is ten opzichte van de eerste INSERT query, is dat er nu twee records tegelijkertijd ingevoegd worden. Verder is alles hetzelfde.
Meedere records tegelijk invoegen (PHP)
In PHP zou deze bewerking er als volgt uitzien:
<?php
require_once 'db_config.php';
$sql = "
INSERT INTO werknemers
(
voornaam,
tussenvoegsel,
achternaam,
geboortedatum,
salaris_schaal
)
VALUES
(
'Tim',
'',
'Janssen',
'1982-01-30',
10
),
(
'Pim',
'',
'Vosse',
'1982-01-30',
10
)
";
if(!$res = mysql_query($sql))
{
trigger_error(mysql_error().'<br />In query: '.$sql);
}
?>
Zoals we zien is hier ook niet veel verschil met de vorige code om een record in te voegen.
Het invoegen van meerdere records kan op een veel efficiëntere manier, namelijk door het gebruik van een prepared statement. Onder andere PDO en MySQLi bieden deze functionaliteit:
[li]PDO - Prepared statements[/li]
[li]MySQLi - Prepared statements[/li]
Invoegen van gebruikersinput
Een belangrijke regel binnen PHP is dat input van een gebruiker nooit te vertrouwen is. Je zult dus ook te allen tijde moeten controleren of de data wel is dat jij denkt dat het is. Zo ook moet input beveiligd worden voordat je het in een query kunt gebruiken.
Stel je een fictieve tabel voor waarin een voor- en achternaam ingevoerd kunnen worden en laat de voor- en achternaam uit een formulier komen:
<?php
require_once 'db_config.php';
$voornaam = mysql_real_escape_string($_POST['voornaam']);
$achternaam = mysql_real_escape_string($_POST['achternaam']);
$sql = "
INSERT INTO fictieve_tabel
(
voornaam,
achternaam
)
VALUES
(
'".$voornaam."',
'".$achternaam."'
)
";
if(!$res = mysql_query($sql))
{
trigger_error(mysql_error().'<br />In query: '.$sql);
}
?>
We gebruiken nu de functie mysql_real_escape_string() om de input vanuit het formulier te beveiligen. Vervolgens gebruiken we de veilige variabelen $voornaam en $achternaam in de query. Zouden we dit niet doen, dan is ons script namelijk gevoelig voor SQL injectie, een veiligheidslek.
Pagina 5
Selecteren van records
Nu we gegevens naar onze database hebben weggeschreven, willen we ze natuurlijk op weer uit de database op kunnen halen. Dit kunnen we doen met behulp van een SELECT query.
Syntax
De syntax van de simpelste SELECT query ziet er als volgt uit:
Deze syntax vormt de basis van elke SELECT query. Hoewel we later zullen zien dat hier vele uitbreidingen en varianten op mogelijk zijn, zullen we eerst deze basis maar eens gebruiken om wat gegevens te selecteren.
Namen selecteren (SQL)
Stel nu dat we de voornamen van onze werknemers uit de tabel willen selecteren. Dan zouden we daar de volgende query voor gebruiken:
Zoals we zien levert deze query netjes een lijstje met alle voornamen in onze tabel op.
Namen selecteren (PHP)
Ook in PHP kunnen we dezelfde query namen gaan selecteren. Alleen zullen we dan nog wel een manier moeten vinden om de namen op het scherm te zetten. Dat doen we als volgt:
Tot aan regel 14 komt de code bekend voor, daarna zien we echter iets nieuws. In een elseif statement controleren we met mysql_num_rows() of de resultaat-set in $res wel rijen bevat. Dit geeft ons namelijk een indicatie of er wel gegevens uit de database opgehaald zijn.
Als dat aantal niet gelijk is aan 0 gebruiken we mysql_fetch_assoc() om de rijen uit de resultaat set te halen. We zetten deze functie in een while-loop omdat we verwachten dat de resultaat-set meerdere rijen bevat. Op die manier doorlopen we alle rijen en hebben met $row telkens de beschikking over 1 rij.
De functie mysql_fetch_assoc() zorgt ervoor dat er een associatieve array gemaakt wordt van een rij uit de resultaat-set. De sleutels in die array zijn dan de kolomnamen en de waarden de bij de kolommen horende waarden. Aangezien wij de voornaam ophaalden, kunnen we deze dus uitlezen met $row['voornaam'].
Alle records selecteren
Als we alle gegevens uit een bepaalde tabel willen selecteren, kunnen we de volgende query gebruiken:
Het * staat hierbij voor alle kolommen en dient alleen gebruikt te worden als ook daadwerlijk alle kolommen nodig zijn. Als dat niet het geval is, geef je gewoon netjes de kolomnamen op.
WHERE
In de meeste gevallen zullen we niet alle records willen selecteren, maar enkel de records die aan een bepaalde voorwaarde voldoen. Zo'n voorwaarde kunnen we opgeven in de WHERE clausule van een query.
Stel dat we alleen de voornaam van de werknemer op willen halen die in salaris schaal 10 zitten. De volgende query zouden we dan gebruiken:
Net als in PHP kunnen we ook de groter dan en kleiner dan operators gebruiken. Stel dat we alle gegevens willen selecteren van werkenmers die in salaris schaal 12 of hoger zitten:
Het * is hier wederom geoorloofd omdat we werkelijk alle kolommen wilden selecteren.
WHERE ... AND ... OR
Als we records op willen halen die aan meedere voorwaarden voldoen, gebruiken we daarvoor bijvoorbeeld de volgende query:
In theorie zouden we onbeperkt veel keer AND aan de query toe kunnen voegen. Of dat nuttig is en of bovendien je datamodel dan nog juist is, is een tweede.
De tegenhanger van AND is OR. Dat kunnen we gebruiken als we records op willen halen die aan minimaal één van de gestelde voorwaarden voldoen. Het gebruik en de positie in de query is precies hetzelfde als bij AND. In een query is elke willekeurge combinatie van WHERE, AND en OR mogelijk, zolang de WHERE maar in de query voorkomt.
ORDER BY
De volgorde waarop records in een database staan heeft geen enkele waarde. Het sorteren van records zullen we dan dus ook bij het selecteren ervan moeten doen. Met behulp van ORDER BY is dat mogelijk:
In de ORDER BY geven we één of meer kolommen (gescheiden door een komma) op waarop we willen sorteren. Met behulp van ASC of DESC achter de kolomnaam geven we de sorteerrichting aan. ASC sorteert oplopend terwijl DESC aflopend sorteert.
Als je zowel een WHERE als een ORDER BY clausule in je query hebt, komt de ORDER BY altijd na de WHERE.
LIMIT
Met behulp van LIMIT is het mogelijk om het aantal records dat uit de database gehaald wordt te beperken. De syntax van de LIMIT is als volgt:
Op de plaats van van vul je het record in waar de limit moet beginnen. Het eerste record in een set heeft, net als in PHP arrays, de waarde 0. Vervolgens geef je met aantal aan hoeveel records er opgehaald moeten worden.
De eerste parameter van de LIMIT is optioneel, dus die kun je ook weglaten. In dat geval wordt het aangegeven aantal records vanaf het begin opgehaald. Dus:
is hetzelfde als:
De plaats van de LIMIT in de query is, ongeacht welke clausules er nog meer in zitten, altijd aan het einde van de query.
Syntax
De syntax van de simpelste SELECT query ziet er als volgt uit:
SELECT
kolomnaam,
kolomnaam
FROM
tabelnaam
Deze syntax vormt de basis van elke SELECT query. Hoewel we later zullen zien dat hier vele uitbreidingen en varianten op mogelijk zijn, zullen we eerst deze basis maar eens gebruiken om wat gegevens te selecteren.
Namen selecteren (SQL)
Stel nu dat we de voornamen van onze werknemers uit de tabel willen selecteren. Dan zouden we daar de volgende query voor gebruiken:
SELECT
voornaam
FROM
werknemers
+----------+
| voornaam |
+----------+
| Nico |
| Tim |
| Pim |
+----------+
Zoals we zien levert deze query netjes een lijstje met alle voornamen in onze tabel op.
Namen selecteren (PHP)
Ook in PHP kunnen we dezelfde query namen gaan selecteren. Alleen zullen we dan nog wel een manier moeten vinden om de namen op het scherm te zetten. Dat doen we als volgt:
<?php
require_once 'db_config.php';
$sql = "
SELECT
voornaam
FROM
werknemers
";
if(!$res = mysql_query($sql))
{
trigger_error(mysql_error().'<br />In query: '.$sql);
}
elseif(mysql_num_rows($res) == 0)
{
echo 'Geen resultaten gevonden';
}
else
{
while($row = mysql_fetch_assoc($res))
{
echo $row['voornaam'].'<br />';
}
}
?>
Nico
Tim
Pim
Tot aan regel 14 komt de code bekend voor, daarna zien we echter iets nieuws. In een elseif statement controleren we met mysql_num_rows() of de resultaat-set in $res wel rijen bevat. Dit geeft ons namelijk een indicatie of er wel gegevens uit de database opgehaald zijn.
Als dat aantal niet gelijk is aan 0 gebruiken we mysql_fetch_assoc() om de rijen uit de resultaat set te halen. We zetten deze functie in een while-loop omdat we verwachten dat de resultaat-set meerdere rijen bevat. Op die manier doorlopen we alle rijen en hebben met $row telkens de beschikking over 1 rij.
De functie mysql_fetch_assoc() zorgt ervoor dat er een associatieve array gemaakt wordt van een rij uit de resultaat-set. De sleutels in die array zijn dan de kolomnamen en de waarden de bij de kolommen horende waarden. Aangezien wij de voornaam ophaalden, kunnen we deze dus uitlezen met $row['voornaam'].
Alle records selecteren
Als we alle gegevens uit een bepaalde tabel willen selecteren, kunnen we de volgende query gebruiken:
SELECT *
FROM werknemers
+----+----------+---------------+------------+---------------+----------------+
| id | voornaam | tussenvoegsel | achternaam | geboortedatum | salaris_schaal |
+----+----------+---------------+------------+---------------+----------------+
| 1 | Nico | de | Boer | 1958-03-24 | 18 |
| 2 | Tim | | Janssen | 1982-01-30 | 10 |
| 3 | Pim | | Vosse | 1982-01-30 | 10 |
+----+----------+---------------+------------+---------------+----------------+
Het * staat hierbij voor alle kolommen en dient alleen gebruikt te worden als ook daadwerlijk alle kolommen nodig zijn. Als dat niet het geval is, geef je gewoon netjes de kolomnamen op.
WHERE
In de meeste gevallen zullen we niet alle records willen selecteren, maar enkel de records die aan een bepaalde voorwaarde voldoen. Zo'n voorwaarde kunnen we opgeven in de WHERE clausule van een query.
Stel dat we alleen de voornaam van de werknemer op willen halen die in salaris schaal 10 zitten. De volgende query zouden we dan gebruiken:
SELECT
voornaam,
salaris_schaal
FROM
werknemers
WHERE
salaris_schaal = 10
+----------+----------------+
| voornaam | salaris_schaal |
+----------+----------------+
| Tim | 10 |
| Pim | 10 |
+----------+----------------+
Net als in PHP kunnen we ook de groter dan en kleiner dan operators gebruiken. Stel dat we alle gegevens willen selecteren van werkenmers die in salaris schaal 12 of hoger zitten:
SELECT
*
FROM
werknemers
WHERE
salaris_schaal >= 12
+----+----------+---------------+------------+---------------+----------------+
| id | voornaam | tussenvoegsel | achternaam | geboortedatum | salaris_schaal |
+----+----------+---------------+------------+---------------+----------------+
| 1 | Nico | de | Boer | 1958-03-24 | 18 |
+----+----------+---------------+------------+---------------+----------------+
Het * is hier wederom geoorloofd omdat we werkelijk alle kolommen wilden selecteren.
WHERE ... AND ... OR
Als we records op willen halen die aan meedere voorwaarden voldoen, gebruiken we daarvoor bijvoorbeeld de volgende query:
SELECT
voornaam
FROM
werknemers
WHERE
salaris_schaal = 10
AND
achternaam = 'Vosse'
+----------+
| voornaam |
+----------+
| Pim |
+----------+
In theorie zouden we onbeperkt veel keer AND aan de query toe kunnen voegen. Of dat nuttig is en of bovendien je datamodel dan nog juist is, is een tweede.
De tegenhanger van AND is OR. Dat kunnen we gebruiken als we records op willen halen die aan minimaal één van de gestelde voorwaarden voldoen. Het gebruik en de positie in de query is precies hetzelfde als bij AND. In een query is elke willekeurge combinatie van WHERE, AND en OR mogelijk, zolang de WHERE maar in de query voorkomt.
ORDER BY
De volgorde waarop records in een database staan heeft geen enkele waarde. Het sorteren van records zullen we dan dus ook bij het selecteren ervan moeten doen. Met behulp van ORDER BY is dat mogelijk:
SELECT
voornaam,
tussenvoegsel,
achternaam,
geboortedatum
FROM
werknemers
ORDER BY
voornaam ASC
+----------+---------------+------------+---------------+
| voornaam | tussenvoegsel | achternaam | geboortedatum |
+----------+---------------+------------+---------------+
| Nico | de | Boer | 1958-03-24 |
| Pim | | Vosse | 1982-01-30 |
| Tim | | Janssen | 1982-01-30 |
+----------+---------------+------------+---------------+
In de ORDER BY geven we één of meer kolommen (gescheiden door een komma) op waarop we willen sorteren. Met behulp van ASC of DESC achter de kolomnaam geven we de sorteerrichting aan. ASC sorteert oplopend terwijl DESC aflopend sorteert.
Als je zowel een WHERE als een ORDER BY clausule in je query hebt, komt de ORDER BY altijd na de WHERE.
LIMIT
Met behulp van LIMIT is het mogelijk om het aantal records dat uit de database gehaald wordt te beperken. De syntax van de LIMIT is als volgt:
LIMIT van, aantal
Op de plaats van van vul je het record in waar de limit moet beginnen. Het eerste record in een set heeft, net als in PHP arrays, de waarde 0. Vervolgens geef je met aantal aan hoeveel records er opgehaald moeten worden.
SELECT
*
FROM
werknemers
LIMIT 1,2
+----+----------+---------------+------------+---------------+----------------+
| id | voornaam | tussenvoegsel | achternaam | geboortedatum | salaris_schaal |
+----+----------+---------------+------------+---------------+----------------+
| 2 | Tim | | Janssen | 1982-01-30 | 10 |
| 3 | Pim | | Vosse | 1982-01-30 | 10 |
+----+----------+---------------+------------+---------------+----------------+
De eerste parameter van de LIMIT is optioneel, dus die kun je ook weglaten. In dat geval wordt het aangegeven aantal records vanaf het begin opgehaald. Dus:
LIMIT 5
is hetzelfde als:
LIMIT 0,5
De plaats van de LIMIT in de query is, ongeacht welke clausules er nog meer in zitten, altijd aan het einde van de query.
Pagina 6
Wijzigen van records
Nu hebben we records in een tabel staan en weten we hoe we deze records uit de tabel moeten selecteren. De volgende stap is het wijzigen van records in de database. Hiervoor kent SQL de UPDATE query.
Syntax
De syntax van de UPDATE query ziet er als volgt uit:
Dit is de basis van de UPDATE query en die is altijd hetzelfde. Ook deze query kan op verschillende manieren uitgebreid worden, maar wij houden het voor nu bij de basis.
Een record wijzigen (SQL)
Stel dat we de salaris schaal van onze werknemer 'Tim' willen verhogen naar 12. Dan kunnen we dat met de volgende query doen:
Bij een UPDATE query geeft MySQL geen records terug, in de output kunnen we enkel zien of er records gewijzigd zijn. Zoals we zien is dat hier het geval. Als je dit wilt controleren, zou je een SELECT query uit kunnen voeren en de resultaten bekijken.
Een record wijzigen (PHP)
Als we in PHP scripts een query uitvoeren om een record te wijzigen, moeten we naast controleren of de query gelukt is, ook altijd controleren of er wel rijen gewijzigd zijn in de database. Hiervoor biedt PHP ons de functie mysql_affected_rows().
Allereerst valt op dat mysql_affected_rows() geen parameter nodig heeft. Er wordt namelijk automatisch gekeken naar de laatste uitgevoerde query. Verder is het altijd verstandig om tijdens het debuggen ook de query te echoën als er geen rijen geupdate zijn. Op die manier kun je vaak snel een fout achterhalen.
Een query die geen foutmelding geeft hoeft namelijk niet per se records gewijzigd te hebben. Als er geen records zijn die aan de WHERE clausule voldoen, zullen er ook geen records geupdate worden.
Syntax
De syntax van de UPDATE query ziet er als volgt uit:
UPDATE
tabelnaam
SET
kolomnaam = 'waarde',
kolomnaam = 'waarde'
WHERE
kolomnaam = 'waarde'
Dit is de basis van de UPDATE query en die is altijd hetzelfde. Ook deze query kan op verschillende manieren uitgebreid worden, maar wij houden het voor nu bij de basis.
Een record wijzigen (SQL)
Stel dat we de salaris schaal van onze werknemer 'Tim' willen verhogen naar 12. Dan kunnen we dat met de volgende query doen:
UPDATE
werknemers
SET
salaris_schaal = 12
WHERE
voornaam = 'Tim'
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Bij een UPDATE query geeft MySQL geen records terug, in de output kunnen we enkel zien of er records gewijzigd zijn. Zoals we zien is dat hier het geval. Als je dit wilt controleren, zou je een SELECT query uit kunnen voeren en de resultaten bekijken.
Een record wijzigen (PHP)
Als we in PHP scripts een query uitvoeren om een record te wijzigen, moeten we naast controleren of de query gelukt is, ook altijd controleren of er wel rijen gewijzigd zijn in de database. Hiervoor biedt PHP ons de functie mysql_affected_rows().
<?php
require_once 'db_config.php';
$sql = "
UPDATE
werknemers
SET
salaris_schaal = 12
WHERE
voornaam = 'Tim'
";
if(!$res = mysql_query($sql))
{
trigger_error(mysql_error().'<br />In query: '.$sql);
}
elseif(mysql_affected_rows() == 0)
{
echo 'Geen records gewijzigd. <br />Query: '.$sql;
}
else
{
echo 'Update was succesvol!';
}
?>
Allereerst valt op dat mysql_affected_rows() geen parameter nodig heeft. Er wordt namelijk automatisch gekeken naar de laatste uitgevoerde query. Verder is het altijd verstandig om tijdens het debuggen ook de query te echoën als er geen rijen geupdate zijn. Op die manier kun je vaak snel een fout achterhalen.
Een query die geen foutmelding geeft hoeft namelijk niet per se records gewijzigd te hebben. Als er geen records zijn die aan de WHERE clausule voldoen, zullen er ook geen records geupdate worden.
Pagina 7
Verwijderen van records
De laatste belangrijke bewerking die we op data in de database uit kunnen voeren, is het verwijderen van records uit de database. De query die we hiervoor gebruiken is een DELETE query.
Syntax
De syntax van de DELETE query ziet er als volgt uit:
In deze query worden er geen kolomnamen opgegeven tussen DELETE en FROM. Dit is niet nodig aangezien toch het hele record uit de tabel verwijderd wordt. Deze query zal alle records verwijderen die voldoen aan de voorwaarde(n) uit de WHERE clausule.
Een record verwijderen (SQL)
Stel dat we alle werknemers willen verwijderen die in salaris schaal 10 zitten. We zouden de volgende query kunnen uitvoeren:
Net als bij een UPDATE query zal ook de DELETE query natuurlijk geen records teruggeven. De enige informatie die we terugkrijgen is het aantal rijen dat aangepast is.
Een record verwijderen (PHP)
Ook de PHP code die we in het geval van een DELETE query gebruiken lijkt heel veel op die van een UPDATE query. Wederom moeten we ook nu controleren of er wel degelijk rijen verwijderd zijn uit de database. Dit doen we net als bij de UPDATE query met mysql_affected_rows().
Syntax
De syntax van de DELETE query ziet er als volgt uit:
DELETE FROM
tabelnaam
WHERE
kolomnaam = 'waarde'
In deze query worden er geen kolomnamen opgegeven tussen DELETE en FROM. Dit is niet nodig aangezien toch het hele record uit de tabel verwijderd wordt. Deze query zal alle records verwijderen die voldoen aan de voorwaarde(n) uit de WHERE clausule.
Een record verwijderen (SQL)
Stel dat we alle werknemers willen verwijderen die in salaris schaal 10 zitten. We zouden de volgende query kunnen uitvoeren:
DELETE FROM
werknemers
WHERE
salaris_schaal = 10
Query OK, 1 row affected (0.06 sec)
Net als bij een UPDATE query zal ook de DELETE query natuurlijk geen records teruggeven. De enige informatie die we terugkrijgen is het aantal rijen dat aangepast is.
Een record verwijderen (PHP)
<?php
require_once 'db_config.php';
$sql = "
DELETE FROM
werknemers
WHERE
salaris_schaal = 10
";
if(!$res = mysql_query($sql))
{
trigger_error(mysql_error().'<br />In query: '.$sql);
}
elseif(mysql_affected_rows() == 0)
{
echo 'Geen records verwijderd. <br />Query: '.$sql;
}
else
{
echo 'Er zijn '.mysql_affected_rows().' records verwijderd uit de database.';
}
?>
Ook de PHP code die we in het geval van een DELETE query gebruiken lijkt heel veel op die van een UPDATE query. Wederom moeten we ook nu controleren of er wel degelijk rijen verwijderd zijn uit de database. Dit doen we net als bij de UPDATE query met mysql_affected_rows().
Pagina 8
Sleutels en constraints
In de voorgaande hoofdstukken hebben we gekeken naar de belangrijkste bewerkingen die we op de data in een database kunnen uitvoeren. Voordat we nu echter verder kunnen moeten we eerst wat dieper ingaan op de opbouw van een tabel binnen een database en de onderlinge relatie tussen tabelen.
Sleutels en indexen
Bij het aanmaken van de tabel werknemers hebben we al kort kennnis gemaakt met het gebruik van sleutels in een tabel. Op de kolom id hebben we namelijk een primaire sleutel, ook wel PRIMARY KEY, aangebracht.
Een PRIMARY KEY zorgt ervoor dat alle gegevens in die kolom uniek moeten zijn. We zullen dus nooit twee records met dezelfde waarde in die kolom tegenkomen. Er kan maximaal één PRIMARY KEY per tabel voorkomen, maar iedere tabel heeft er een nodig.
Naast de primaire sleutel kunnen ook een UNIQUE KEY en INDEX op een kolom aanbrengen. De eerste doet hetzelfde als de PRIMARY KEY maar mag wel meerdere keren in een tabel voorkomen. Een INDEX wordt gebruikt om de data in die kolom te indexeren zodat bepaalde queries sneller uitgevoerd kunnen worden.
Meer informatie over sleutels en indexen in MySQL vind je in mijn MySQL Indexes tutorial.
Aanbrengen van sleutels en indexen (SQL)
Het aanbrengen van sleutels en indexen op een tabel kunnen we op verschillende manieren doen. Allereerst kunnen we daar phpMyAdmin voor gebruiken. Als we daar de structuur van een tabel bekijken zien we in het 'action' veld van een kolom verschillende knopjes.
[li]
- PRIMARY KEY[/li]
[li]
- UNIQUE KEY[/li]
[li]
- INDEX[/li]
Ook kunnen we een query gebruiken om sleutels en indexen op een tabel aan te brengen. Zo'n query ziet er bijvoorbeeld zo uit:
Met deze query zouden we een PRIMARY KEY op de kolom id aanbrengen.
Foreign Key Constraints
Zoals ik eerder al vertelde is het opzetten van een goed datamodel belangrijk. In een goed datamodel zullen er altijd onderlinge relaties tussen de verschillende tabellen zijn. Zonder die relaties hangt alles als een los hoopje zand aan elkaar en is de kans op corrupte data vrij groot.
Om die onderlinge relaties aan te brengen gebruiken we zogenaamde foreign key constraints. We koppelen als het ware een kolom uit de ene tabel aan een kolom uit een andere tabel. Helaas is de InnoDB engine de enige die het gebruik van foreign keys ondersteund, dit is ook de voornaamste reden waarom wij deze gebruiken.
Voordat we deze relaties aan kunnen gaan brengen hebben hebben we eerst een tweede tabel nodig waarin we gegevens op gaan slaan. In dit voorbeeld gebruiken we een tabel functies waarin de functie van een werknemer staat. De SQL om de benodigde tabellen aan te maken ziet er als volgt uit:
We zien een nieuwe tabel functies met daarin een kolom voor het id en een kolom voor de functienaam. We zien ook dat onze werknemers tabel uitgebreid is met een extra kolom functie_id.
Foreign key constraint aanbrengen (SQL)
Zoals je wellicht wel verwacht had, is de kolom functie_id de zogenaamde foreign key uit de werknemers tabel die verwijst naar de functies tabel. Het aanbrengen van de relatie doen we met de volgende query:
Met deze query zal er automatisch een INDEX aangemaakt worden op de kolom functie_id en zal een onderlinge relatie gelegd worden met de kolom id uit de functies tabel.
De ON DELETE en ON UPDATE regels geven aan welke handeling uitgevoerd moet worden als een waarde uit de kolom functies.id gewijzigd of verwijderd wordt. RESTRICT houdt in dat de bewerking tegengegaan wordt en CASCADE zorgt ervoor dat de foreign key ook geupdate wordt.
De andere methode is door de onderlinge relatie in de relation view van phpMyAdmin in te stellen. Deze relation view ziet er als volgt uit:
Hierin kunnen we aangeven dat de kolom functie_id verwijst naar de kolom functies->id, oftewel de kolom id uit de functies tabel.
Het aanbrengen van deze onderlinge relaties is belangrijk, zorg dus dat je dat altijd op een nette manier doet. Zonder deze relaties kun je de gegevens in je database namelijk niet vertrouwen.
Sleutels en indexen
Bij het aanmaken van de tabel werknemers hebben we al kort kennnis gemaakt met het gebruik van sleutels in een tabel. Op de kolom id hebben we namelijk een primaire sleutel, ook wel PRIMARY KEY, aangebracht.
Een PRIMARY KEY zorgt ervoor dat alle gegevens in die kolom uniek moeten zijn. We zullen dus nooit twee records met dezelfde waarde in die kolom tegenkomen. Er kan maximaal één PRIMARY KEY per tabel voorkomen, maar iedere tabel heeft er een nodig.
Naast de primaire sleutel kunnen ook een UNIQUE KEY en INDEX op een kolom aanbrengen. De eerste doet hetzelfde als de PRIMARY KEY maar mag wel meerdere keren in een tabel voorkomen. Een INDEX wordt gebruikt om de data in die kolom te indexeren zodat bepaalde queries sneller uitgevoerd kunnen worden.
Meer informatie over sleutels en indexen in MySQL vind je in mijn MySQL Indexes tutorial.
Aanbrengen van sleutels en indexen (SQL)
Het aanbrengen van sleutels en indexen op een tabel kunnen we op verschillende manieren doen. Allereerst kunnen we daar phpMyAdmin voor gebruiken. Als we daar de structuur van een tabel bekijken zien we in het 'action' veld van een kolom verschillende knopjes.
[li]
- PRIMARY KEY[/li][li]
- UNIQUE KEY[/li][li]
- INDEX[/li]Ook kunnen we een query gebruiken om sleutels en indexen op een tabel aan te brengen. Zo'n query ziet er bijvoorbeeld zo uit:
ALTER TABLE
werknemers
ADD
PRIMARY KEY(id)
Met deze query zouden we een PRIMARY KEY op de kolom id aanbrengen.
Foreign Key Constraints
Zoals ik eerder al vertelde is het opzetten van een goed datamodel belangrijk. In een goed datamodel zullen er altijd onderlinge relaties tussen de verschillende tabellen zijn. Zonder die relaties hangt alles als een los hoopje zand aan elkaar en is de kans op corrupte data vrij groot.
Om die onderlinge relaties aan te brengen gebruiken we zogenaamde foreign key constraints. We koppelen als het ware een kolom uit de ene tabel aan een kolom uit een andere tabel. Helaas is de InnoDB engine de enige die het gebruik van foreign keys ondersteund, dit is ook de voornaamste reden waarom wij deze gebruiken.
Voordat we deze relaties aan kunnen gaan brengen hebben hebben we eerst een tweede tabel nodig waarin we gegevens op gaan slaan. In dit voorbeeld gebruiken we een tabel functies waarin de functie van een werknemer staat. De SQL om de benodigde tabellen aan te maken ziet er als volgt uit:
CREATE TABLE functies (
id int(4) NOT NULL auto_increment,
functie varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB
CREATE TABLE werknemers (
id int(8) NOT NULL auto_increment,
voornaam varchar(50) NOT NULL,
tussenvoegsel varchar(4) default NULL,
achternaam varchar(75) NOT NULL,
geboortedatum date NOT NULL,
salaris_schaal int(2) default '10',
functie_id int(4) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB
We zien een nieuwe tabel functies met daarin een kolom voor het id en een kolom voor de functienaam. We zien ook dat onze werknemers tabel uitgebreid is met een extra kolom functie_id.
Foreign key constraint aanbrengen (SQL)
Zoals je wellicht wel verwacht had, is de kolom functie_id de zogenaamde foreign key uit de werknemers tabel die verwijst naar de functies tabel. Het aanbrengen van de relatie doen we met de volgende query:
ALTER TABLE
werknemers
ADD
FOREIGN KEY(functie_id)
REFERENCES functies (id)
ON DELETE RESTRICT
ON UPDATE CASCADE
Met deze query zal er automatisch een INDEX aangemaakt worden op de kolom functie_id en zal een onderlinge relatie gelegd worden met de kolom id uit de functies tabel.
De ON DELETE en ON UPDATE regels geven aan welke handeling uitgevoerd moet worden als een waarde uit de kolom functies.id gewijzigd of verwijderd wordt. RESTRICT houdt in dat de bewerking tegengegaan wordt en CASCADE zorgt ervoor dat de foreign key ook geupdate wordt.
De andere methode is door de onderlinge relatie in de relation view van phpMyAdmin in te stellen. Deze relation view ziet er als volgt uit:
Hierin kunnen we aangeven dat de kolom functie_id verwijst naar de kolom functies->id, oftewel de kolom id uit de functies tabel.
Het aanbrengen van deze onderlinge relaties is belangrijk, zorg dus dat je dat altijd op een nette manier doet. Zonder deze relaties kun je de gegevens in je database namelijk niet vertrouwen.
Pagina 9
Selecteren uit meerdere tabellen: JOINS
Naarmate de hoeveelheid gegevens die we opslaan in de database groeit, zal bij het normaliseren van ons datamodel naar voren komen dat we meerdere tabellen nodig hebben. Nu kan zal het vaak genoeg voorkomen dat we met één query gegevens uit meerdere tabellen willen selecteren.
Gelukkig hebben we op een nette manier de onderlinge relaties tussen deze tabellen aangebracht en biedt SQL ons een oplossing om uit meerdere tabellen tegelijk te selecteren. Met zogenaamde JOINS wordt aan de hand van een foreign key een verband gelegd met een andere tabel en is het mogelijk om data te selecteren.
Brongegevens
Voordat we verder kunnen kijken we eerst even naar hoe onze tabellen gevuld zijn:
We zien in totaal 3 verschillende functies in de functies tabel. Verder zien we dat er een aantal werknemers bijgekomen zijn en dat deze allemaal een functie hebben gekregen.
Naam en functie ophalen (SQL)
Stel nu dat we de namen van onze werknemers op willen halen met daarbij hun functie. We willen dus de kolommen voornaam, tussenvoegsel en achternaam uit de werknemers tabel en de kolom functie uit de functies tabel.
De output laat zien dat deze query inderdaad doet wat we willen, maar laten we nu eens kijken naar wat er precies gebeurt is.
Allereerst zien we dat de kolomnamen in de SELECT een letter ervoor hebben gekregen. Dit is een zogenaamde alias waarmee we de kolommen uit verschillende tabellen kunnen ophalen. De alias maken we aan in het FROM gedeelte van de query. Zouden we geen alias gebruiken, dan zou er in plaats van w.voornaam, werknemers.voornaam moeten staan. Maar ik ben lui, dus houd het liever zo kort mogelijk.
In de FROM clausule zien we de tabelnaam gevolgd door AS. Dit is de manier waarop we een alias voor een tabel aan kunnen maken. Nu kunnen we dus gewoon w gebruiken en hoeven we niet elke keer werknemers helemaal uit te schrijven.
Het type JOIN dat ik in dit voorbeeld gebruik is een zogenaamde INNER JOIN. In dit geval joinen we de functies tabel waarbij de kolommen functies.id overeen moet komen met de kolom werknemers.functie_id. Een INNER JOIN haalt alleen records op waarbij een match tussen deze twee kolommen is gevonden.
Naam en functie ophalen (PHP)
De PHP code waarin we deze query gebruiken ziet er als volgt uit:
We zien wederom dat er aan de basis syntax van de PHP code heel weinig verandert. Enkel de afhandeling van de resultaten uit de query zal er telkens iets anders uit zien.
Andere typen JOINS
Naast de INNER JOIN zijn er nog een aantal verschillende soorten JOINS. Deze horen echter niet tot het onderwerp van deze tutorial, dus ik zal er hier ook verder niet op in gaan.
Meer informatie over het gebruik van JOINS kun je vinden in deze tutorial geschreven door Robert Deiman. Ook deze FAQ van tweakers.net geeft meer informatie over dit onderwerp.
Gelukkig hebben we op een nette manier de onderlinge relaties tussen deze tabellen aangebracht en biedt SQL ons een oplossing om uit meerdere tabellen tegelijk te selecteren. Met zogenaamde JOINS wordt aan de hand van een foreign key een verband gelegd met een andere tabel en is het mogelijk om data te selecteren.
Brongegevens
Voordat we verder kunnen kijken we eerst even naar hoe onze tabellen gevuld zijn:
+----+----------------+
| id | functie |
+----+----------------+
| 1 | Manager |
| 2 | Werknemer |
| 3 | Senior Manager |
+----+----------------+
+----+----------+---------------+------------+---------------+----------------+------------+
| id | voornaam | tussenvoegsel | achternaam | geboortedatum | salaris_schaal | functie_id |
+----+----------+---------------+------------+---------------+----------------+------------+
| 1 | Nico | de | Boer | 1958-03-24 | 18 | 1 |
| 2 | Tim | NULL | Janssen | 1982-01-30 | 10 | 2 |
| 3 | Pim | NULL | Vosse | 1980-12-20 | 10 | 2 |
| 4 | Joost | de | Hoog | 1974-10-20 | 20 | 3 |
| 5 | Thomas | NULL | Verhoeven | 1970-06-12 | 15 | 2 |
| 6 | Niels | de | Boer | 1979-08-18 | 10 | 1 |
+----+----------+---------------+------------+---------------+----------------+------------+
We zien in totaal 3 verschillende functies in de functies tabel. Verder zien we dat er een aantal werknemers bijgekomen zijn en dat deze allemaal een functie hebben gekregen.
Naam en functie ophalen (SQL)
Stel nu dat we de namen van onze werknemers op willen halen met daarbij hun functie. We willen dus de kolommen voornaam, tussenvoegsel en achternaam uit de werknemers tabel en de kolom functie uit de functies tabel.
SELECT
w.voornaam,
w.tussenvoegsel,
w.achternaam,
f.functie
FROM
werknemers AS w
INNER JOIN
functies AS f
ON f.id = w.functie_id
+----------+---------------+------------+----------------+
| voornaam | tussenvoegsel | achternaam | functie |
+----------+---------------+------------+----------------+
| Nico | de | Boer | Manager |
| Niels | de | Boer | Manager |
| Tim | NULL | Janssen | Werknemer |
| Pim | NULL | Vosse | Werknemer |
| Thomas | NULL | Verhoeven | Werknemer |
| Joost | de | Hoog | Senior Manager |
+----------+---------------+------------+----------------+
De output laat zien dat deze query inderdaad doet wat we willen, maar laten we nu eens kijken naar wat er precies gebeurt is.
Allereerst zien we dat de kolomnamen in de SELECT een letter ervoor hebben gekregen. Dit is een zogenaamde alias waarmee we de kolommen uit verschillende tabellen kunnen ophalen. De alias maken we aan in het FROM gedeelte van de query. Zouden we geen alias gebruiken, dan zou er in plaats van w.voornaam, werknemers.voornaam moeten staan. Maar ik ben lui, dus houd het liever zo kort mogelijk.
In de FROM clausule zien we de tabelnaam gevolgd door AS. Dit is de manier waarop we een alias voor een tabel aan kunnen maken. Nu kunnen we dus gewoon w gebruiken en hoeven we niet elke keer werknemers helemaal uit te schrijven.
Het type JOIN dat ik in dit voorbeeld gebruik is een zogenaamde INNER JOIN. In dit geval joinen we de functies tabel waarbij de kolommen functies.id overeen moet komen met de kolom werknemers.functie_id. Een INNER JOIN haalt alleen records op waarbij een match tussen deze twee kolommen is gevonden.
Naam en functie ophalen (PHP)
De PHP code waarin we deze query gebruiken ziet er als volgt uit:
<?php
require_once 'db_config.php';
$sql = "
SELECT
w.voornaam,
w.tussenvoegsel,
w.achternaam,
f.functie
FROM
werknemers AS w
INNER JOIN
functies AS f
ON f.id = w.functie_id
";
if(!$res = mysql_query($sql))
{
trigger_error(mysql_error().'<br />In query: '.$sql);
}
elseif(mysql_num_rows($res) == 0)
{
echo 'Geen records gevonden.';
}
else
{
while($row = mysql_fetch_assoc($res))
{
if(empty($row['tussenvoegsel']))
{
echo $row['voornaam'].' '.$row['achternaam'].' ('.$row['functie'].') <br />';
}
else
{
echo $row['voornaam'].' '.$row['tussenvoegsel'].' '.$row['achternaam'].' ('.$row['functie'].') <br />';
}
}
}
?>
Nico de Boer (Manager)
Niels de Boer (Manager)
Tim Janssen (Werknemer)
Pim Vosse (Werknemer)
Thomas Verhoeven (Werknemer)
Joost de Hoog (Senior Manager)
We zien wederom dat er aan de basis syntax van de PHP code heel weinig verandert. Enkel de afhandeling van de resultaten uit de query zal er telkens iets anders uit zien.
Andere typen JOINS
Naast de INNER JOIN zijn er nog een aantal verschillende soorten JOINS. Deze horen echter niet tot het onderwerp van deze tutorial, dus ik zal er hier ook verder niet op in gaan.
Meer informatie over het gebruik van JOINS kun je vinden in deze tutorial geschreven door Robert Deiman. Ook deze FAQ van tweakers.net geeft meer informatie over dit onderwerp.
Pagina 10
Werken met data en tijden
Een database is uitermate geschikt om te werken met data en tijden. Het rekenen met deze gegevens is in de database heel gemakkelijk. Vandaar ook dat we dit zoveel mogelijk aan de database willen overlaten.
Om goed te kunnen werken met data en tijden is het van belang dat je het juiste datatype kiest voor deze gegevens. Een datum sla je op in een DATE veld, een tijd in een TIME veld en een combinatie van een datum en tijd komt in een DATETIME veld te staan.
Opmerking: deze datatypen zijn specifiek voor MySQL. Andere databases kennen weer andere datatypes voor data en tijden.
Formatteren van een datum (SQL)
De geboortedatum van onze werknemers staat nu in het formaat yyyy-mm-dd in de tabel, overigens het enige geldige formaat voor een datum. Maar de kans is groot dat we de datum niet in dit formaat op het scherm willen zetten.
Met behulp van de MySQL functie DATE_FORMAT() kunnen we daarom het formaat van de datum aanpassen bij het selecteren van de gegevens. Dit doen we als volgt:
De functie DATE_FORMAT() accepteert twee parameters. Allereerst de kolom waarin de datum opgeslagen is en ten tweede het formaat waarin de datum gezet moet worden. Een overzicht met alle parameters die je daarin kunt gebruiken zijn te vinden in de MySQL handleiding.
Nadat je de functie toegepast hebt, geef je dat gegeven een alias. Op die manier kun je hem selecteren in de resultaat set. Zorg dat je de naam van de alias niet hetzelfde kiest als een bestaande kolomnaam, aangezien dat verderop in de query problemen kan opleveren.
In de output zien we dat de datum nu inderdaad in het nieuwe formaat weergegeven wordt.
Formatteren van een datum (PHP)
Als we dit willen gebruiken in PHP verandert er natuurlijk niets aan de query. Het zal er dus als volgt uit komen te zien:
Rekenen met data en tijden (SQL)
Het is mogelijk om de geboortedatum kolom op te nemen in een WHERE clausule en er een vergelijking mee uit te voeren. Stel dat we bijvoorbeeld de werknemers op willen halen die na 1 januari 1980 geboren zijn:
Zoals verwacht zijn in de output alleen de gegevens van de werknemers, die in 1980 of later geboren zijn, te zien.
Huidige datum en tijd (SQL)
MySQL kent een tweetal functies die de de huidige datum en tijd teruggeven. De functie NOW() geeft een datumtijd-stempel terug waarin dus zowel een datum als tijd te vinden zijn. De functie CURDATE() geeft enkel de huidige datum terug.
De gebruik ervan in de query is bijvoorbeeld als volgt:
Op deze manier zou je dus de huidige datumtijd-stempel in kunnen voegen in de database. Ga dus niet meer in PHP met de date() functie aan de gang, dit is veel makkelijker.
Meer datum- en tijdfuncties
De toepassingen van het rekenen met data en tijdens die ik hier heb laten zien, zijn nog maar een klein topje van een hele grote ijsberg. Dit onderwerp is veel te uitgebreid om hier te bespreken, dus wil ik je graag doorverwijzen naar een van de volgende links:
[li]Datum- en tijdfunctie in MySQL (tutorial)[/li]
[li]MySQL handleiding[/li]
Om goed te kunnen werken met data en tijden is het van belang dat je het juiste datatype kiest voor deze gegevens. Een datum sla je op in een DATE veld, een tijd in een TIME veld en een combinatie van een datum en tijd komt in een DATETIME veld te staan.
Opmerking: deze datatypen zijn specifiek voor MySQL. Andere databases kennen weer andere datatypes voor data en tijden.
Formatteren van een datum (SQL)
De geboortedatum van onze werknemers staat nu in het formaat yyyy-mm-dd in de tabel, overigens het enige geldige formaat voor een datum. Maar de kans is groot dat we de datum niet in dit formaat op het scherm willen zetten.
Met behulp van de MySQL functie DATE_FORMAT() kunnen we daarom het formaat van de datum aanpassen bij het selecteren van de gegevens. Dit doen we als volgt:
SELECT
voornaam,
DATE_FORMAT(geboortedatum, '%d-%m-%Y') AS newDate
FROM
werknemers
+----------+------------+
| voornaam | newDate |
+----------+------------+
| Nico | 24-03-1958 |
| Tim | 30-01-1982 |
| Pim | 20-12-1980 |
| Joost | 20-10-1974 |
| Thomas | 12-06-1970 |
| Niels | 18-08-1979 |
+----------+------------+
De functie DATE_FORMAT() accepteert twee parameters. Allereerst de kolom waarin de datum opgeslagen is en ten tweede het formaat waarin de datum gezet moet worden. Een overzicht met alle parameters die je daarin kunt gebruiken zijn te vinden in de MySQL handleiding.
Nadat je de functie toegepast hebt, geef je dat gegeven een alias. Op die manier kun je hem selecteren in de resultaat set. Zorg dat je de naam van de alias niet hetzelfde kiest als een bestaande kolomnaam, aangezien dat verderop in de query problemen kan opleveren.
In de output zien we dat de datum nu inderdaad in het nieuwe formaat weergegeven wordt.
Formatteren van een datum (PHP)
Als we dit willen gebruiken in PHP verandert er natuurlijk niets aan de query. Het zal er dus als volgt uit komen te zien:
<?php
require_once 'db_config.php';
$sql = "
SELECT
voornaam,
DATE_FORMAT(geboortedatum, '%d-%m-%Y') AS newDate
FROM
werknemers
";
if(!$res = mysql_query($sql))
{
trigger_error(mysql_error().'<br />In query: '.$sql);
}
elseif(mysql_num_rows($res) == 0)
{
echo 'Geen records gevonden.';
}
else
{
while($row = mysql_fetch_assoc($res))
{
echo $row['voornaam'].' ('.$row['newDate'].') <br />';
}
}
?>
Nico (24-03-1958)
Tim (30-01-1982)
Pim (20-12-1980)
Joost (20-10-1974)
Thomas (12-06-1970)
Niels (18-08-1979)
Rekenen met data en tijden (SQL)
Het is mogelijk om de geboortedatum kolom op te nemen in een WHERE clausule en er een vergelijking mee uit te voeren. Stel dat we bijvoorbeeld de werknemers op willen halen die na 1 januari 1980 geboren zijn:
SELECT
voornaam,
DATE_FORMAT(geboortedatum, '%d-%m-%Y') AS newDate
FROM
werknemers
WHERE
geboortedatum > '1980-01-01'
+----------+------------+
| voornaam | newDate |
+----------+------------+
| Tim | 30-01-1982 |
| Pim | 20-12-1980 |
+----------+------------+
Zoals verwacht zijn in de output alleen de gegevens van de werknemers, die in 1980 of later geboren zijn, te zien.
Huidige datum en tijd (SQL)
MySQL kent een tweetal functies die de de huidige datum en tijd teruggeven. De functie NOW() geeft een datumtijd-stempel terug waarin dus zowel een datum als tijd te vinden zijn. De functie CURDATE() geeft enkel de huidige datum terug.
De gebruik ervan in de query is bijvoorbeeld als volgt:
INSERT INTO tabel
(
datumtijdveld,
naam
)
VALUES
(
NOW(),
'Jan'
)
Op deze manier zou je dus de huidige datumtijd-stempel in kunnen voegen in de database. Ga dus niet meer in PHP met de date() functie aan de gang, dit is veel makkelijker.
Meer datum- en tijdfuncties
De toepassingen van het rekenen met data en tijdens die ik hier heb laten zien, zijn nog maar een klein topje van een hele grote ijsberg. Dit onderwerp is veel te uitgebreid om hier te bespreken, dus wil ik je graag doorverwijzen naar een van de volgende links:
[li]Datum- en tijdfunctie in MySQL (tutorial)[/li]
[li]MySQL handleiding[/li]
Pagina 11
Aggregate functies en GROUP BY
Een van de laatste aspecten die ik in deze tutorial wil behandelen is het gebruik van verzamelingsfuncties en de bijbehorende GROUP BY clausule.
Verzamelingsfuncties worden gebruikt om gegevens over groepen records te bepalen. Denk dan bijvoorbeeld aan het aantal werknemers per salaris schaal of de gemiddelde salaris schaal van alle werknemers. Deze berekeningen worden allemaal uitgevoerd aan de hand van verzamelingsfuncties (aggregate functies) waarvan we hier enkele voorbeelden zullen bekijken.
Het aantal werknemers per salaris schaal (SQL)
Om een aantal records te bepalen gebruiken we de functie COUNT():
In de SELECT clausule selecteren we de salaris_schaal en gebruiken we COUNT(id) om het aantal werknemers te bepalen. Door de GROUP BY die we aan het eind van de query toevoegen wordt het aantal werknemers gegroepeert per salaris_schaal.
Er is een belangrijke regel voor het gebruik van GROUP BY:
Bij het gebruik van GROUP BY moeten alle kolommen die in de SELECT clausule voorkomen en geen onderdeel zijn van een aggregate functie, opgenomen zijn in de GROUP BY clausule
We zien dat bovenstaande query aan deze regel voldoet. De kolom salaris_schaal komt voor in de SELECT en wordt niet gebruikt in een aggregate functie. Daarom staat hij ook in de GROUP BY. In dit eenvoudige voorbeeldje is het wellicht nog logisch dat die kolom in de GROUP BY moet staan, maar naarmate je queries langer worden is deze regel een goede controle.
Gemiddelde salaris schaal
Om de gemiddelde salaris schaal van alle werknemers te bepalen, gebruiken we de volgende query:
In deze query nemen we geen GROUP BY clausule op, simpelweg omdat er geen kolom is om op te groeperen. We willen dan ook het gemiddelde van alle werknemers ophalen.
Meer toepassingen van aggregate functies
Deze twee voorbeeldjes zijn wederom maar het kleine topje van een hele grote ijsberg. Maar ook dit onderwerp is veel te uitgebreid om in deze tutorial te behandelen. Mocht je meer over dit onderwerp willen lezen, dan verwijs ik je graag naar mijn tutorial over het juiste gebruik van GROUP BY.
Verzamelingsfuncties worden gebruikt om gegevens over groepen records te bepalen. Denk dan bijvoorbeeld aan het aantal werknemers per salaris schaal of de gemiddelde salaris schaal van alle werknemers. Deze berekeningen worden allemaal uitgevoerd aan de hand van verzamelingsfuncties (aggregate functies) waarvan we hier enkele voorbeelden zullen bekijken.
Het aantal werknemers per salaris schaal (SQL)
Om een aantal records te bepalen gebruiken we de functie COUNT():
SELECT
salaris_schaal,
COUNT(id) AS aantal
FROM
werknemers
GROUP BY
salaris_schaal
+----------------+--------+
| salaris_schaal | aantal |
+----------------+--------+
| 10 | 3 |
| 15 | 1 |
| 18 | 1 |
| 20 | 1 |
+----------------+--------+
In de SELECT clausule selecteren we de salaris_schaal en gebruiken we COUNT(id) om het aantal werknemers te bepalen. Door de GROUP BY die we aan het eind van de query toevoegen wordt het aantal werknemers gegroepeert per salaris_schaal.
Er is een belangrijke regel voor het gebruik van GROUP BY:
Bij het gebruik van GROUP BY moeten alle kolommen die in de SELECT clausule voorkomen en geen onderdeel zijn van een aggregate functie, opgenomen zijn in de GROUP BY clausule
We zien dat bovenstaande query aan deze regel voldoet. De kolom salaris_schaal komt voor in de SELECT en wordt niet gebruikt in een aggregate functie. Daarom staat hij ook in de GROUP BY. In dit eenvoudige voorbeeldje is het wellicht nog logisch dat die kolom in de GROUP BY moet staan, maar naarmate je queries langer worden is deze regel een goede controle.
Gemiddelde salaris schaal
Om de gemiddelde salaris schaal van alle werknemers te bepalen, gebruiken we de volgende query:
SELECT
AVG(salaris_schaal) AS gemiddelde
FROM
werknemers
+------------+
| gemiddelde |
+------------+
| 13.8333 |
+------------+
In deze query nemen we geen GROUP BY clausule op, simpelweg omdat er geen kolom is om op te groeperen. We willen dan ook het gemiddelde van alle werknemers ophalen.
Meer toepassingen van aggregate functies
Deze twee voorbeeldjes zijn wederom maar het kleine topje van een hele grote ijsberg. Maar ook dit onderwerp is veel te uitgebreid om in deze tutorial te behandelen. Mocht je meer over dit onderwerp willen lezen, dan verwijs ik je graag naar mijn tutorial over het juiste gebruik van GROUP BY.
Pagina 12
Debuggen: het oplossen van SQL fouten
Het laatste onderwerp dat ik in deze tutorial wil behandelen, is het herkennen en oplossen van fouten die in SQL queries op kunnen treden.
Doordat we een nette foutafhandeling toepassen en altijd controleren of een query wel gelukt is, kunnen we ook een nette foutmelding geven als dat niet het geval is. Met behulp van mysql_error() is de foutmelding die MySQL geeft te achterhalen. Debuggen gaat steeds makkelijker naarmate je meer ervaring krijgt, dus laten we maar eens naar een aantal voorbeelden gaan kijken.
Query syntax error
Dit is de standaard syntax waarin een foutmelding over de query syntax gegeven wordt. Eigenlijk is het enige nuttige van deze foutmelding het laatste gedeelte, vanaf 'near'. Dat is namelijk het gedeelte dat aangeeft waar de fout opgetreden is.
Als we inderdaad naar FROM in de query kijken, zien we dat we daarvoor vergeten zijn om de betreffende kolomnamen te noemen.
Unknown column in field list
Deze foutmelding geeft aan dat de kolom 'naam' die we in de SELECT noemen, niet bestaat. En ja, dat klopt ook. In onze tabel hebben we enkel de kolommen voornaam en achternaam.
No GROUP BY clause
Deze foutmelding wordt gegeven als we in de SELECT clausule zowel een kolomnaam opgeven als een aggregate functie gebruiken. Het is dan namelijk verplicht om een GROUP BY clausule met daarin de kolomnamen die niet in de aggregate functie voorkomen, op te nemen.
Onvolledige GROUP BY
Deze foutmelding krijgen we alleen als we MySQL in ANSI mode dwingen. De foutmelding geeft aan dat er een bepaalde kolom wel in de SELECT clausule voorkomt maar niet in de GROUP BY, terwijl dat wel zou moeten.
Dit waren enkele voorbeelden van SQL foutmeldingen. Zoals je ziet zijn ze redelijk duidelijk, dus het vinden van fouten in een query mag dan ook niet al teveel problemen opleveren.
Doordat we een nette foutafhandeling toepassen en altijd controleren of een query wel gelukt is, kunnen we ook een nette foutmelding geven als dat niet het geval is. Met behulp van mysql_error() is de foutmelding die MySQL geeft te achterhalen. Debuggen gaat steeds makkelijker naarmate je meer ervaring krijgt, dus laten we maar eens naar een aantal voorbeelden gaan kijken.
Query syntax error
SELECT
FROM
werknemers
Notice: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM werknemers' at line 1
Dit is de standaard syntax waarin een foutmelding over de query syntax gegeven wordt. Eigenlijk is het enige nuttige van deze foutmelding het laatste gedeelte, vanaf 'near'. Dat is namelijk het gedeelte dat aangeeft waar de fout opgetreden is.
Als we inderdaad naar FROM in de query kijken, zien we dat we daarvoor vergeten zijn om de betreffende kolomnamen te noemen.
Unknown column in field list
SELECT
naam
FROM
werknemers
Notice: Unknown column 'naam' in 'field list'
Deze foutmelding geeft aan dat de kolom 'naam' die we in de SELECT noemen, niet bestaat. En ja, dat klopt ook. In onze tabel hebben we enkel de kolommen voornaam en achternaam.
No GROUP BY clause
SELECT
salaris_schaal,
COUNT(id)
FROM
werknemers
Notice: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
Deze foutmelding wordt gegeven als we in de SELECT clausule zowel een kolomnaam opgeven als een aggregate functie gebruiken. Het is dan namelijk verplicht om een GROUP BY clausule met daarin de kolomnamen die niet in de aggregate functie voorkomen, op te nemen.
Onvolledige GROUP BY
SELECT
voornaam,
salaris_schaal,
COUNT(id)
FROM
werknemers
GROUP BY
salaris_schaal
Notice: 'databasenaam.werknemers.voornaam' isn't in GROUP BY
Deze foutmelding krijgen we alleen als we MySQL in ANSI mode dwingen. De foutmelding geeft aan dat er een bepaalde kolom wel in de SELECT clausule voorkomt maar niet in de GROUP BY, terwijl dat wel zou moeten.
Dit waren enkele voorbeelden van SQL foutmeldingen. Zoals je ziet zijn ze redelijk duidelijk, dus het vinden van fouten in een query mag dan ook niet al teveel problemen opleveren.
Pagina 13
Slotwoord en referenties
Tot zover dan deze beginners handleiding over het gebruik van SQL. Ik hoop dat je na het lezen van deze tutorial redelijke begrijpt hoe je met SQL en databases te werk gaat. Natuurlijk is deze tutorial nog maar het allereerste begin, een eerste opstapje in de wereld van SQL.
Zoals je misschien wel gemerkt hebt in de tutorial, ben ik niet zo'n grote fan van MySQL. De manier waarop deze database met de standaarden omspringt, brengt niet echt veel zekerheid met zich mee. Ook het ontbreken van een goede relationele ondersteuning (InnoDB komt niet eens in de buurt van andere databases) is voor mij een reden om liever geen MySQL te gebruiken. Helaas ondersteunen veel webhosts nog geen alternatieven, dus vandaar toch maar deze tutorial over MySQL.
Ik wil benadrukken dat je eigenlijk te allen tijde MySQL in de ANSI mode moet dwingen. Op die manier heb je iets meer zekerheid over de uitgevoerde queries en krijg je in meer gevallen een foutmelding als een query niet klopt. Standaard zal MySQL in veel vallen namelijk geen foutmelding geven en gewoon willekeurige data gaan teruggeven.
Na het lezen van deze tutorial wil je de wereld van SQL misschien wel verder ontdekken. Daarom volgen hier een aantal links naar handleidingen die je daarbij kunnen helpen:
Databases en SQL
[li]Normaliseren[/li]
[li]Datum- en tijdfuncties in MySQL[/li]
[li]GROUP BY: Het juiste gebruik ervan[/li]
[li]MySQL Indexes[/li]
[li]Tweakers.net SQL FAQ[/li]
[li]PostgreSQL voor beginners[/li]
[li]MySQL Handleiding[/li]
[li]PostgreSQL Handleiding[/li]
PHP gerelateerde onderwerpen
[li]PDO - Verbinden met verschillende databases[/li]
[li]MySQLi - Een nieuwe manier van interactie tussen PHP en MySQL[/li]
[li]Foutafhandeling in PHP[/li]
Opmerkingen, vragen en/of suggesties zijn natuurlijk altijd welkom. Deze tutorial is ook hier te vinden: SQL Beginners handleiding
Zoals je misschien wel gemerkt hebt in de tutorial, ben ik niet zo'n grote fan van MySQL. De manier waarop deze database met de standaarden omspringt, brengt niet echt veel zekerheid met zich mee. Ook het ontbreken van een goede relationele ondersteuning (InnoDB komt niet eens in de buurt van andere databases) is voor mij een reden om liever geen MySQL te gebruiken. Helaas ondersteunen veel webhosts nog geen alternatieven, dus vandaar toch maar deze tutorial over MySQL.
Ik wil benadrukken dat je eigenlijk te allen tijde MySQL in de ANSI mode moet dwingen. Op die manier heb je iets meer zekerheid over de uitgevoerde queries en krijg je in meer gevallen een foutmelding als een query niet klopt. Standaard zal MySQL in veel vallen namelijk geen foutmelding geven en gewoon willekeurige data gaan teruggeven.
Na het lezen van deze tutorial wil je de wereld van SQL misschien wel verder ontdekken. Daarom volgen hier een aantal links naar handleidingen die je daarbij kunnen helpen:
Databases en SQL
[li]Normaliseren[/li]
[li]Datum- en tijdfuncties in MySQL[/li]
[li]GROUP BY: Het juiste gebruik ervan[/li]
[li]MySQL Indexes[/li]
[li]Tweakers.net SQL FAQ[/li]
[li]PostgreSQL voor beginners[/li]
[li]MySQL Handleiding[/li]
[li]PostgreSQL Handleiding[/li]
PHP gerelateerde onderwerpen
[li]PDO - Verbinden met verschillende databases[/li]
[li]MySQLi - Een nieuwe manier van interactie tussen PHP en MySQL[/li]
[li]Foutafhandeling in PHP[/li]
Opmerkingen, vragen en/of suggesties zijn natuurlijk altijd welkom. Deze tutorial is ook hier te vinden: SQL Beginners handleiding
Reacties
0