Met PHP kun je gegevens uit je database als CSV-bestand aan je bezoekers aanbieden. CSV (Comma Separated Values) is een simpel formaat waarbij de waarden door een komma of puntkomma worden gescheiden. Excel opent het zonder omhaal, en daarna kun je er analyses of grafieken van maken.
Hieronder bouwen we een script dat de uitslagen van cursusexamens uit de database haalt en als CSV terugstuurt. Daarna volgen de manieren waarop je hetzelfde doet zonder PHP, want in veel gevallen is dat de kortste weg.
Verbinden met PDO
Voor de verbinding gebruiken we PDO. Dat heeft vier voordelen boven de oude functies: het werkt op ruim tien databasesystemen, dus je code hoeft niet mee te verhuizen als de database dat doet; het ondersteunt prepared statements met benoemde parameters, waarmee je SQL-injectie voorkomt; het geeft je fouten als exception in plaats van als returnwaarde; en je kunt rijen direct als array of als object ophalen.
$db_user = getenv('DB_USER');
$db_pass = getenv('DB_PASS');
$db_name = 'uitslagen';
$db_host = 'localhost';
try {
$db = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4",
$db_user,
$db_pass,
[
// bij een fout gooit PDO een exception
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
$stmt = $db->prepare("SELECT id, naam, email, resultaat
FROM Uitslagen
WHERE cursus_id = :cursus");
$stmt->execute(['cursus' => $cursusId]);
} catch (PDOException $ex) {
// niet de databasefout aan de bezoeker tonen
error_log($ex->getMessage());
http_response_code(500);
exit('Er ging iets mis bij het ophalen van de gegevens.');
}
Drie dingen die hier bewust anders zijn dan in het gemiddelde voorbeeld op internet. De inloggegevens staan niet in het script maar in de omgeving, zodat ze niet in je versiebeheer belanden. De foutmelding gaat naar het log en niet naar de bezoeker, want een databasefout op het scherm vertelt een aanvaller precies hoe je database in elkaar zit. En de query is een prepared statement, ook al komt de waarde uit je eigen code: dat is een gewoonte die je een keer redt.
Let ook op utf8mb4 in plaats van utf8. Het oude utf8 in MySQL is geen volledige UTF-8 en struikelt over tekens buiten de basisreeks, emoji bijvoorbeeld.
De HTTP-headers
We willen dat de browser het bestand downloadt in plaats van het te tonen. Dat regel je met twee headers, en die moeten worden gestuurd voordat er ook maar één teken uitvoer is geweest.
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="uitslagen.csv"');
Zet de bestandsnaam tussen dubbele aanhalingstekens en sluit ze ook: in het oorspronkelijke voorbeeld ontbrak dat sluitteken, en dan verschilt de bestandsnaam per browser.
Het bestand schrijven
We schrijven rechtstreeks naar de uitvoer, dus zonder eerst een bestand op schijf te maken.
$fp = fopen('php://output', 'w');
// BOM, zodat Excel de UTF-8 codering herkent
fputs($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));
// koprij
fputcsv($fp, ['id', 'naam', 'email', 'resultaat'], ';');
while ($uitslag = $stmt->fetch()) {
fputcsv($fp, $uitslag, ';');
}
fclose($fp);
Die BOM aan het begin is nodig omdat Excel anders aanneemt dat het bestand in de lokale codering staat, en dan wordt "Müller" ineens "Müller".
Gebruik fputcsv ook voor de koprij, en niet fputs met een zelf getypte regel. In het oude voorbeeld stond "id, naam, email, resultaat" met spaties na de komma's, en die spaties worden deel van je kolomnamen.
Het derde argument is het scheidingsteken. Op een Nederlandse Windows-installatie is de puntkomma de juiste keuze: Excel gebruikt daar de komma als decimaalteken en zet bij een komma-CSV alles in één kolom. Wie het bestand aan anderen geeft, kan beter de puntkomma nemen dan uitleggen hoe de importwizard werkt.
Waar het in de praktijk stukloopt
Bij een export van honderdduizenden rijen loopt het geheugen vol als je eerst alles ophaalt. Met fetch() in een lus, zoals hierboven, gebeurt dat niet, maar zet er ook set_time_limit(0) bij en stuur de uitvoer meteen door met flush(). En zorg dat er niets anders wordt afgedrukt, geen witregel na de laatste ?> in een include, want dat ene teken maakt je CSV kapot.
Nog een klassieker: een cel die met =, +, - of @ begint, wordt door Excel als formule gelezen. Bij gegevens die gebruikers zelf hebben ingevoerd, is dat een reëel veiligheidsrisico. Zet in dat geval een apostrof voor de waarde.
En zonder PHP
Vaak heb je helemaal geen script nodig. Vier kortere wegen, afhankelijk van waar je zit.
SQL Server. In SQL Server Management Studio kun je het resultaat van een query opslaan als bestand, en met bcp of de wizard exporteer je een hele tabel. Vanuit PowerShell werkt Invoke-Sqlcmd | Export-Csv -Delimiter ';' -Encoding UTF8.
MySQL en MariaDB. Met SELECT ... INTO OUTFILE aan de serverkant, of met mysql -e "SELECT ..." --batch aan de clientkant.
PostgreSQL. Met \copy (SELECT ...) TO 'uitslagen.csv' WITH (FORMAT csv, HEADER, DELIMITER ';') in psql.
Python. Drie regels met pandas: pd.read_sql(query, conn).to_csv('uitslagen.csv', sep=';', index=False, encoding='utf-8-sig'). Dat utf-8-sig zet de BOM er voor je in.
Moet het periodiek en geautomatiseerd, dan is een script in Python of PowerShell in een geplande taak makkelijker te onderhouden dan een pagina die iemand moet aanklikken.
SQL leren
Wie zijn eigen exports wil maken, heeft vooral SQL nodig. In de tweedaagse cursus SQL Basis leer je gegevens opvragen, filteren, sorteren en combineren, en werk je met je eigen vragen. Cursisten gaven de training een 8,4, gemiddeld over 74 beoordelingen.
Bouw je zelf webapplicaties, dan sluit de cursus PHP aan, en wil je exports automatiseren, dan is Python de kortste weg.