PHP-lessen - les 3.2 - Werken met MySQL-databases. Gegevens invoegen met INSERT INTO. Gegevens ophalen met SELECT.

In de vorige les hebben we een tabel gemaakt voor onze website. In deze les verbeteren we die tabel en beginnen we daadwerkelijk met het werken met de database: we voegen er gegevens aan toe en halen die er ook weer uit. Ik denk niet dat het moeilijk wordt, dus laten we beginnen.

Om te beginnen wil ik onze tabel messages verbeteren. Momenteel bevat ze gegevensvelden, maar we hebben nog een extra veld nodig om de records te nummeren. Als je de Drupal-database opent, zul je zien dat de tabel node een veld nid heeft waarmee de nodes worden genummerd. We zullen iets vergelijkbaars doen met onze tabel messages.

Open phpMyAdmin (bijvoorbeeld http://localhost/tools/phpmyadmin voor Denwer) en bewerk onze tabel. Voeg een nieuwe kolom toe aan het begin van de tabel, zoals getoond op de afbeelding:

Noem de kolom mid (message id), stel het gegevenstype in op INT (integer) met een lengte van 11 tekens. Vink ook het vakje AUTO_INCREMENT aan — dit zorgt voor automatische nummering in deze kolom. Stel vervolgens Index in op PRIMARY — dat betekent dat dit de primaire sleutel van onze tabel is.

Als je je herinnert, hebben we eerder een aparte methode gemaakt in de klasse simpleCMS om de tabel aan te maken. We moeten die methode nu aanpassen zodat hij ook het veld mid toevoegt. Open de tabel messages en klik op Exporteren:

We komen op de exportpagina terecht. Exporteren gebeurt in de vorm van SQL-query’s voor het aanmaken en bewerken van tabellen. Wanneer we een tabel in phpMyAdmin wijzigen, kunnen we de SQL-query voor het aanmaken ervan kopiëren — erg handig.

Laat op de exportpagina alle instellingen op de standaardwaarden staan en klik op OK. PhpMyAdmin zal de SQL-query tonen, ofwel direct in de browser ofwel als een downloadbaar bestand. Beide opties zijn prima. Kopieer de volgende code:

Merk op dat phpMyAdmin schuine aanhalingstekens gebruikt (backticks), maar in PHP kunnen we gewone enkele aanhalingstekens gebruiken. Kopieer de tekst naar onze methode buildDB():

  public function buildDB(){
    $sql = "CREATE TABLE IF NOT EXISTS 'messages' (
			  'mid' int(11) NOT NULL AUTO_INCREMENT,
			  'title' varchar(150) DEFAULT NULL,
			  'bodytext' text,
			  'created' varchar(100) DEFAULT NULL,
			  PRIMARY KEY ('mid')
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
			
    return mysql_query($sql);    
  }

Laten we nu controleren of dit werkt. Verwijder de tabel messages in phpMyAdmin:

Bevestig de verwijdering. Wanneer we nu index.php op onze website uitvoeren, wordt de methode buildDB() aangeroepen en zou de tabel opnieuw moeten worden aangemaakt.

  public function buildDB(){

$sql = "CREATE TABLE Messages
(
mid int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(mid),
title varchar(15),
bodytext text,
created  int(11)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";

    $result = mysql_query($sql);   
    print_r($result);	
  }

Start nu index.php en je zult zien dat de tabel Messages opnieuw is aangemaakt, net als voorheen.

INSERT INTO-query’s

De database is aangemaakt — we zijn klaar om onze methoden te implementeren voor het opslaan en weergeven van berichten met behulp van de database. Laten we beginnen met het opslaan in de database. Open de methode write() en pas hem aan.

Het invoegen van gegevens in een tabel gebeurt met de INSERT INTO-operator, als volgt:

  public function write($p) { // methode om berichten op te slaan
    $sql = 'INSERT INTO Messages (title, bodytext, created) VALUES ("'. $p["title"] . '", "' . $p["bodytext"] . '", ' . time() . ')';
    return mysql_query($sql);
  } 

Laten we eens bekijken hoe waarden precies worden ingevoegd. Zoals eerder vermeld, begint een invoegquery met INSERT INTO, gevolgd door de tabelnaam Messages. Daarna geven we aan in welke kolommen we waarden invoegen. Merk op dat we mid niet opnemen, omdat MySQL dit automatisch invult dankzij de AUTO_INCREMENT-instelling. Daarna komt de operator VALUES, gevolgd door de waarden voor elke kolom in de tabel. De volgorde en het aantal kolommen en waarden moeten overeenkomen.

Wanneer we tekstwaarden invoegen, moeten we aanhalingstekens gebruiken. Let erop dat de aanhalingstekens binnen de query verschillen van de aanhalingstekens die de hele SQL-string in PHP omhullen. Bovendien, wanneer we variabelen gebruiken binnen de SQL-query, moeten we de tekstuele delen van de query omsluiten met enkele aanhalingstekens en de variabelewaarden met dubbele. Bijvoorbeeld:

"'. $p["title"] . '"

De juiste volgorde van aanhalingstekens is: dubbel, enkel, punt, variabele, punt, enkel, dubbel.

Ik denk dat één voorbeeld voorlopig voldoende is — er volgen nog meer later. Laten we nu een paar records in de tabel invoegen. Vervang de code van de methode write() door de bovenstaande versie.

SELECT-query’s

We hebben enkele rijen toegevoegd — bekijk ze in phpMyAdmin:

Nu kunnen we deze records weergeven met de methode display_public(), die we ook zullen aanpassen. We halen gegevens op uit de tabel met behulp van de SELECT-operator. Laten we beginnen met een eenvoudige selectie:

  public function display_public() { // methode om berichten weer te geven
    $content = '';

	$sql = 'SELECT * FROM Messages'; // selectquery
	$result = mysql_query($sql);  // sla het resultaat van de query op in een variabele
	
	while($row = mysql_fetch_array($result)){ // verwerk de queryresultaten met mysql_fetch_array()
	  print '<div class="post">';
	  print '<span class="time">#' . $row['mid'] . ' van ' . date('d-m-Y', $row['created']) . '</span><h2>' . $row['title'] . '</h2>';
	  print '<p>' . $row['bodytext'] . '</p>';
	  print '</div>';
	}

	$content .= '<p><a href="/index.php?admin=1">Bericht toevoegen</a></p>';
    return $content;
  }

Ik heb de methode display_public() volledig aangepast, zodat nu alle records uit de database worden weergegeven. Laten we de query uitleggen: de SELECT-query begint met het sleutelwoord SELECT, gevolgd door het sterretje (*) dat betekent dat we alle kolommen uit elke rij willen ophalen. Als we bijvoorbeeld alleen bepaalde kolommen willen selecteren, zouden we dit kunnen schrijven:

$sql = 'SELECT mid,title FROM Messages'; // selectquery
$result = mysql_query($sql);

Dat betekent dat we alleen de kolommen mid en title ophalen, en niet bodytext of created.

Na het sterretje volgt de operator FROM, waarmee we aangeven uit welke tabel we selecteren.

Het resultaat van de functie mysql_query() moet worden opgeslagen in een variabele (in ons geval $result), omdat we deze later verwerken met de functie mysql_fetch_array().

Stel dat we drie records ophalen — de functie mysql_fetch_array() retourneert telkens één array per aanroep: bij de eerste aanroep de eerste rij, bij de tweede aanroep de tweede rij, enzovoort. Wanneer er geen rijen meer zijn, retourneert de functie false. Daarom gebruiken we een while-lus — zolang het resultaat geldig is, blijven we dezelfde acties uitvoeren voor alle records.

Het lijkt misschien ingewikkeld de eerste keer, maar na wat oefening wordt het schrijven van SQL-query’s vanzelfsprekend. Er zijn niet zo veel operators, dus je zult ze snel onthouden.

SELECT-query’s met sortering (ORDER BY)

We kunnen ook de sortering van de selectie bepalen met de operator ORDER BY. Standaard wordt de sortering oplopend toegepast. Bijvoorbeeld:

$sql = 'SELECT * FROM Messages ORDER BY mid';

Dit sorteert de records op oplopende volgorde van mid. Om aflopend te sorteren, gebruiken we de extra operator DESC:

$sql = 'SELECT * FROM Messages ORDER BY mid DESC';

Zoals je ziet, is het heel eenvoudig: voor aflopende volgorde gebruiken we DESC, voor oplopende volgorde ASC:

$sql = 'SELECT * FROM Messages ORDER BY mid ASC';

Hoewel ASC (oplopend) standaard wordt toegepast.

SELECT-query’s met voorwaarden (WHERE)

Naast sorteren kunnen we query’s ook filteren met de operator WHERE om voorwaarden op te geven:

$sql = 'SELECT * FROM Messages WHERE mid<3 ORDER BY mid DESC';

Zo halen we de eerste twee records op (waar mid 1 en 2 is), als die bestaan. We kunnen ook logische operatoren gebruiken zoals AND (en) en OR (of) om meerdere voorwaarden te combineren. Met AND moeten beide voorwaarden waar zijn, met OR slechts één.

$limit_time = time() - 3600; // tijd één uur geleden
$sql = 'SELECT * FROM Messages WHERE mid<3 AND created>' . $limit_time . ' ORDER BY mid DESC'; // selectquery

Zo voegen we een extra voorwaarde toe: het bericht moet minder dan een uur geleden zijn aangemaakt. Ik denk dat dit voorlopig genoeg is over SELECT-query’s — laten we doorgaan naar de volgende les.