SQL

Envoudige SQL en SQL met PHP

PHP en de database

PHP kent een paar eigen commando's die je gebruikt om te communiceren met de database. Eerst moet je contact maken met de database en zodra dat gebeurt is kan je sql opdrachten (query's) naar de database sturen. De gegegevens uit de database kan je opslaan in een variabele, en deze kan je vervolgens verwerken.

Deze les een klein voorbeeld op basis van een database met een tabel berichten:

  --------------------------------------
  Berichten
  --------------------------------------
  id (int, autoincrement, primairy key)
  naam (varchar, 30)
  bericht (text)
  --------------------------------------
  

Maak daarna een php-file die je config.php noemt, met de volgende code:

<?php 
	$DBhost = "localhost";
	$DBuser = "root";
	$DBpass = "";
	$DBname = "test";

	mysql_connect($DBhost,$DBuser,$DBpass) or die("Kan geen connectie maken met de databaseserver");
	mysql_select_db("$DBname") or die("Kan de database $DBname niet vinden");
?>

Bovenstaande code is voor als je werkt op je localhost. Ik weet niet hoe je database heet. Kijk voor deze naam even in je phpmyadmin. Als je op de cmd server werkt gebruikt je de volgende gegevens:

	$DBhost = "sql.cmd.hro.nl";
	$DBuser = "studnr";
	$DBpass = "wachtwoord van school";
	$DBname = "studnr";

Vervolgens maak je een nieuwe php file. En hiermee ga je de data tonen op het scherm. Deze kan je berichten.php noemen. Hierin komt de volgende code:

<?php
include('config.php');

$result = mysql_query("SELECT * FROM berichten");
  while ($row = mysql_fetch_array($result)) {
  echo $row['naam'];
  echo '<br>';
  echo $row['bericht'];
  echo '<br><br>';
}

Je kan dus eerst de config file toevoegen aan je code. Dit maakt dat je deze niet iedere keer opnieuw hoeft te schrijven. Daarna kan je een query schrijven. Hoe deze wordt verwerkt leg ik in de les uit.

SQL

SQL staat voor structured query language. Deze taal is al een standaard sinds 1986. Met deze taal kan je databases maken, tabellen maken, deze tabellen vullen, wijzigen of verwijderen. Als je werkt met PHP stuur je vanuit php commando's in deze taal. Deze commando's heten queries. Deze query stuur je naar een op SQL gebaseerde datatabase, bijvoorbeeld mySQL. Deze database is in principe niet meer dan een verzameling queries. De nieuw aangekomen query wordt verwerkt door de database.

Het is ook mogelijk gedeeltes informatie selecteren. Je verstuurd dan een query en deze stuurt de door jou geselecteerde gegevens terug. Deze kan je vervolgens in php gebruiken. Hier onder een overzicht van de basis commado's in SQL. Deze commando's zijn gebaseerd op een fictieve database met een tabel die ik Docenten heb genoemd, zie plaatje:

id Voornaam Achternaam Waarvan
1 Marije Smits CMD
2 Carolien van de Akker CMD
3 Will Adolfs CMD

Met SELECT kan je data uit een tabel selecteren

SELECT Voornaam,Achternaam FROM Docenten
>

Met WHERE kan je aan de selectie uit een tabel een criteria toekennen.
Bijvoorbeeld: 'ik wil alleen de achternaam van alle mensen die als voornaam jan hebben.' In onderstaand voorbeeld betekend het sterretje 'alle rijen'.

SELECT * FROM Docenten WHERE Waarvan='CMD'

met INSERT INTO kan je een nieuwe rij met gegevens toevoegenaan je database.

INSERT INTO Docenten VALUES (Marije, Smits, CMD)
INSERT INTO Docenten (Voornaam, Achternaam) VALUES (Marije, Smits)

met UPDATE kan je een bestaan de rij met gegevens updatenof wijzigen.

UPDATE Docenten SET Voornaam = 'Carolien' WHERE Achternaam = 'van de Akker'
met DELETE kan je een rij gegevens uit een tabel verwijderen.

DELETE FROM Docenten WHERE Achternaam = 'van de Akker'
      

zie voor meer voorbeelden: http://www.w3schools.com

SQL

SQL is een taaltje, dat je kunt gebruiken om vanuit een programma (bijvoorbeeld vanuit PHP) vragen (queries) te stellen of opdrachten te geven aan een relationele database.

SQL is gestandaardiseerd door de American National Standards Institute (ANSI) en door de International Organisation for Standardization (ISO). SQL wordt dus ondersteund door alle relationele database managment systemen (R-DBMS) die er zijn, maar dat weerhoudt producenten van database systemen er natuurlijk niet van met eigen aanvullingen op / dialecten van de standaard te komen.

SQL is vrij eenvoudig te leren, omdat de gramatica leesbaar is en er maar een beperkt aantal commando's te geven zijn.
Er zijn twee hoofdgroepen opdrachten:

SQL Data Definition Language (DDL):

bevat opdrachten om de structuur van de database aan te passen. Dit wordt dus vooral gebruikt door

Commando's uit deze groep zijn:

Hoeveel verschillende gebruikers zal de database van een webapplicatie hebben?

Deze opdrachten moet je wel een beetje kennen, maar niet uit je hoofd kunnen gebruiken. Daar heb je phpmyadmin voor. Belangrijker voor jullie zijn de opdrachten in de groep:

SQL Data Manipulation Language (DML)

Opdrachten in deze groep zijn er om de gegevens in de database te manipuleren. Dat heb je dus nodig als je een PHP webapplicatie maakt, die gegevens uit de database moet halen of erin moet stoppen.

Commando's in deze groep zijn:

SELECT statement:

Het select statement gebruikte je dus om gegevens uit de database te halen. De algemene gramatica (syntax) van dit statement is:

SELECT kolomnamen FROM tabelnamen WHERE voorwaarden

Als je informatie uit een database wil halen moet je je afvragen:

Als je de alle records uit een tabel wil ophalen en je dus geen voorwaarden stelt waar de opgehaalde records aan moeten voldoen, dan mag je de query (vraag aan de database) inkorten door het WHERE (voorwaarden) stuk weg laten. Stel dat je bijvoorbeeld de namen van alle studenten wil zou willen opvragen dan zou de querie luiden:

SELECT firstName, lastName FROM Student 

Als antwoord van de database zou je bijvoorbeeld de volgende lijst met gegevens terug kunnen krijgen:

firstName lastName
Eva de Boer
Bart Haanstra
Adriaan Bakker
Bart van der Molen
Danielle Bakker

Met het keyword DISTINCT kun je dubbele resultaten (antwoorden) weglaten. Stel dat je alle verschillende voornamen wil weten die er op school in omloop zijn dan zou je kunnen vragen:

SELECT DISTINCT firstName FROM Student 

en als antwoord terugkrijgen:

firstName
Eva
Bart
Adriaan
Danielle

Als je niet alleen de naam van de studenten wil opvragen, maar alle beschikbare velden uit de Student tabel, dan kun je in plaats van alle veldnamen uit te schrijven deze samenvatten met een sterretje *

SELECT * FROM Student

geeft dus alle velden (kolommen) terug, omdat je * selecteert
en alle records (rijen) omdat je geen WHERE-clause hebt.
Kortom: de hele tabel

studentNr firstName lastName gender woonplaats
0777777 Eva de Boer v 300
0777778 Bart Haanstra m 105
0777779 Adriaan Bakker m 300
0777780 Bart van der Molen m 300
0777781 Danielle Bakker v 78

WHERE clause

Meestal zul je niet de alle records uit een tabel willen ophalen. Wie is er geinteresseert in een lijst van alle studenten op deze school? Te algemene vragen kunnen er toe leiden dat je lang moet wachten op een onoverzichtelijk lange lijst. Stel je voor dat je aan google vraagt om alle 20 miljoen webpagina's over webdesign voor je op te halen. De kunst is dus de vraag zo te stellen dat je precies het goede antwoord krijgt.

Voorwaarden stellen aan de resultaten die je terugkrijgt doe je met een WHERE statement en dat werkt volgens het zelfde principe als een if-statement in PHP. Als de records voldoen aan de door mij gestelde voorwaarden wil ik ze ophalen uit de database en anders niet. Stel dat ik een lijst wil van alle vrouwlijke studenten, dan zou de querie kunnen luiden:

SELECT studentNr, firstName, lastName
FROM Student 
WHERE gender = 'v'

en het volgende resultaat teruggeven:

studentNr firstName lastName
0777777 Eva de Boer
0777781 Danielle Bakker

Zoals in PHP een conditie uit drie delen bestaat (een variabele, een operator en een waarde), zo bestaat in SQL een conditie meestal uit een veldnaam, een operator en een waarde. Wel zijn de operatoren soms iets anders dan in PHP

Operator PHP equivalent Omschrijving voorbeeld
= = = is gelijk aan
WHERE firstName = 'Piet'
<>  != is ongelijk aan
WHERE gender <> 'm'
> is groter dan
WHERE studentNr > 777776
< is kleiner dan
WHERE studentNr < 777782
>= >= is groter dan of gelijk aan
WHERE studentNr >= 777777
<= <= is kleiner dan of gelijk aan
WHERE studentNr <= 777781

Je moet er rekening mee houden dat strings in SQL tussen enkele quotes moeten staan en getallen net als in PHP zonder quotes geschreven horen te worden.

Een bijzondere operator is LIKE. Hiermee kan je aangeven dat het veld aan een bepaald patroon moet voldoen:

Operator voorbeeld betekenis
LIKE
WHERE firstName LIKE 'B%'

waarvan de voornaam met een 'B' begint

 
WHERE lastName LIKE '%r'
waarvan de achternaam op een 'r' eindigt
 
WHERE firstName LIKE '%e%'
wiens voornaam een 'e' bevat

En wat merkwaardig is de IS operator, om te controleren of een (niet verplicht) veld een waarde heeft. Je kan daar niet gewoon de equals (=) operator voor gebruiken, omdat iets (een veld) nooit gelijk kan zijn aan niets (NULL)

Operator voorbeeld betekenis
IS
WHERE phoneNumber IS NULL

waarvan het telefoonnummer niet is ingevuld

IS NOT
WHERE phoneNumber IS NOT NULL
waarvan het telefoonnummer wel is ingevuld

Soms wil je voorwaarden ook kunnen combineren:

Operator PHP equivalent Omschrijving voorbeeld
AND && beide voorwaarden gelden
WHERE lastName = 'Bakker' AND gender = 'v'
OR || minimaal een van beide voorwaarden geldt
WHERE gender = 'v' OR firstName LIKE 'B%'

Maar wat nou te doen als je wil weten welke studenten er in Rotterdam wonen? Je hebt dan informatie nodig uit zowel de Student tabel als uit de Stad tabel. In de student tabel staan alleen nummertjes om de stad aan te duiden.

studentNr firstName lastName gender woonplaats
0777777 Eva de Boer v 300
0777778 Bart Haanstra m 105
0777779 Adriaan Bakker m 300
0777780 Bart van der Molen m 300
0777781 Danielle Bakker v 78
 
stadID naam
300 Rotterdam
105 Schiedam
300 Rotterdam
300 Rotteram
78 Gouda

Er zijn twee manieren om dit op te lossen. De gangbare manier is door de tabellen als het ware tijdelijk aan elkaar vast te plakken (JOINen) tot een grote tabel. Meestal worden de tabellen aan elkaar geplakt door de foreign key in de ene tabel aan de primary key in de andere tabel te plakken, zoals dat bij het ontwerp van de database was bedacht. De query zou er dan uit komen te zien als:

SELECT Student.firstName, Student.lastName
FROM Student
JOIN City ON Student.woonplaats = Stad.stadID
WHERE Stad.naam = 'Rotterdam'

Let op: bovenstaande gramatica is de officiele ANSI/ISO standaard. Veel mensen koppelen de tabellen nog in de WHERE clause. Dat lijkt makkelijker, maar is uiteindelijk minder duidelijk, foutgevoeliger en dus niet de bedoeling.

De andere manier is door de vraag in twee stukken te knippen en de uitkomst van beide queries met elkaar te vergelijken. Je vraagt dus eerst het stadID op van Rotterdam en vervolgens zoek je de studenten met dat woonplaatsnummer. Dit heet een subquerie. Sommige vragen kun je alleen stellen met een subquery, maar het kan geen kwaad dit alleen te gebruiken als je niet anders kunt.

Operator syntax betekenis
IN
WHERE veldnaam IN (subquery)

een veld komt voor in het resultaat van de subquery

NOT IN
WHERE veldnaam NOT IN (subquery)
een veld komt niet voor in het resultaat van de subquery

bijvoorbeeld:

SELECT firstName, lastName 
FROM Student 
WHERE woonplaats NOT IN (
	SELECT stadID
	FROM City
	WHERE Naam = 'Rotterdam'
)

herschrijf bovenstaande query met subquery naar een gewone query met gejoinde tabellen

GROUP BY clause

Soms wil je niet de inhoud van een veld ophalen, maar globalere informatie. Daarvoor zijn speciale functies:

functie betekenis voorbeeld voorbeeld resultaat
COUNT(*) het aantal records (rijen) SELECT COUNT(*) FROM Student 5
SUM(veldnaam) de waardes bij elkaar opgeteld SELECT SUM(cityID) FROM Student 1083
AVG(veldnaam) de gemiddelde waarde SELECT AVG(studentNr) FROM Student 777779
MAX(veldnaam) de hoogste waarde SELECT MAX(studentNr) FROM Student 777777
MIN(veldnaam) de laagste waarde SELECT MIN(studentNr) FROM Student 777781

Let op: Met deze functies krijg je maar 1 record antwoord terug dat er 5 studenten zijn en niet 5 records.

count(*)
5

Het komt vaak voor dat je wil weten hoe vaak bepaalde waardes voorkomen in de database. Stel dat je wil weten hoe vaak de verschillende voornamen voorkomen. Het zou wat omslachtig zijn om voor elke mogelijke voornaam een query te maken.

SELECT COUNT(*) 
FROM Student
WHERE studentNr >= 777777 
  AND studentNr <= 777781
  AND firstName = 'Eva'

Daarom kun je eerst de resultaten groeperen op voornaam, om dan per groep te vragen hoeveel resultaten er zijn:

SELECT firstName, COUNT(*) 
FROM Student
WHERE studentNr >= 777777 
  AND studentNr <= 777781
GROUP BY firstName
firstName count(*)
Eva 1
Bart 2
Adriaan 1
Danielle 1

(als je in de bovenstaande query de GROUP BY clause weg laat krijg je een error, omdat SELECT firstName 5 resultaten teruggeeft en SELECT COUNT(*) maar 1 resultaat)

ORDER BY clause

In de database worden alle records bewaard in de volgorde waarin ze erin zijn gekomen. Als de primary key een auto-increment nummer is, is dat dus in volgorde van de primary key. Voor gebruikers van de database is die volgorde zelden betekenisvol. Daarom kun je zelf aangeven in welke volgorde je de resultaten wil terugkrijgen.

SELECT firstName, lastName, woonplaats 
FROM Student 
WHERE studentNr >= 777777 
  AND studentNr <= 777781
ORDER BY firstName 
firstName lastName woonplaats
Adriaan Bakker 300
Bart van der Molen 300
Bart Haanstra 105
Danielle Bakker 78
Eva de Boer 300

Standaard wordt oplopend (ascending) gesorteerd (bij cijfers maar ook bij letters), maar als je aflopend (descending) wil sorteren kan dat ook:

SELECT firstName, lastName, woonplaats 
FROM Student 
WHERE studentNr >= 777777 
  AND studentNr <= 777781 
ORDER BY lastName DESC
firstName lastName woonplaats
Bart van der Molen 300
Bart Haanstra 105
Eva de Boer 300
Adriaan Bakker 300
Danielle Bakker 78

Ten slotte is het mogelijk op meerdere kolommen te sorteren. Bijvoorbeeld (lekker onlogisch): eerst aflopend op woonplaats, maar daarbinnen oplopend op achternaam

SELECT firstName, lastName, woonplaats 
FROM Student 
WHERE studentNr >= 777777 
  AND studentNr <= 777781 
ORDER BY woonplaats DESC, lastName ASC
firstName lastName woonplaats
Adriaan Bakker 300
Eva de Boer 300
Bart van der Molen 300
Bart Haanstra 105
Danielle Bakker 78

INSERT statement

Het INSERT statement wordt vooral gebruikt om 1 record tegelijk aan 1 tabel tegelijk toe te voegen. Bijvoorbeeld het inschrijven van een nieuwe student:

INSERT 
INTO Student (studentNr, firstName, lastName, gender, woonplaats) 
VALUES (0777782, 'Jan', 'Klaassen', 'm', 123)

Als je primary key een auto-increment veld is en je wil niet onthouden wat het laatste volgnummertje was, dan kun je dat auto-increment veld beter weg laten. De database doet het dan voor jou automatisch goed.

INSERT 
INTO Student (firstName, lastName, gender, woonplaats) 
VALUES ('Jan', 'Klaassen', 'm', 123) 

Aangezien je ook geen zin hebt om eerst te moeten uitzoeken welk ID bij iemands woonplaats hoort, kun je hier goed van een subquery gebruik maken:

INSERT 
INTO Student (firstName, lastName, gender, woonplaats) 
VALUES ('Jan', 'Klaassen', 'm', (SELECT stadID FROM Stad WHERE naam = 'Lutjebroek')) 

DELETE statement

Het DELETE statement wordt gebruikt om records uit een tabel te verwijderen. Stel dat je de stad Rotterdam zou willen verwijderen dan zou de query luiden:

DELETE 
FROM Stad
WHERE naam = 'Rotterdam' 

Net als bij het SELECT statement mag je de WHERE clause weglaten als je geen voorwaarden stelt en dus alles in de tabel wil weggooien. Het is dus zaak de WHERE clause niet per ongeluk te vergeten en goed op te letten dat je precies de goede voorwaarden stelt (specifiek genoeg bent).

Om te voorkomen dat er studenten plotseling in een niet bestaande stad wonen (hun woonplaats was 300, maar stadID 300 is weggegooid) - om de integriteit (correctheid) van de database te waarborgen - controleert de database van te voren of er foreign keys zijn die naar de primary key van een weg te gooien record verwijzen. De database kan op 3 manieren reageren op een dergelijk DELETE opdracht:

UPDATE statement

Het UPDATE statement wordt gebruikt om gegevens in de database te wijzigen. Bijvoorbeeld als iemand verhuist of om fouten te corrigeren:

UPDATE Student 
SET firstName = 'Daniel', gender='m' 
WHERE studentNr = 0777781 

Ook hier kan de WHERE clause worden weggelaten en is het dus zaak specifiek genoeg te zijn (de goede voorwaarden te stellen), want voordat je het weet heet iedereen in je database 'Daniel'

Ook hier gelden regels om de integriteit van de database te waarborgen. Neem bijvoorbeeld:

UPDATE Stad
SET stadID = 123
WHERE stadID = 300

Ook met deze querie zouden studenten met woonplaats 300 plotseling in een niet bestaande stad komen te wonen. Bovendien zouden er twee steden kunnen ontstaan met hetzelfde ID, waardoor je niet meer zou weten waar studenten met woonplaats 123 wonen. Ook hier kan de database op 3 manieren op reageren: