Webbserverprogrammering01 [wesweb01]

Databas och PHP, en tutorial

Introduktion

Eftersom kursen är fokuserad på webbserverprogrammering så kommer tyvärr databasdelen få stryka på foten. Det är dock vikigt att få en genomgång och möjlighet att jobba med ett exempel för att lära sig hantera detta moment. I alla genomgångar har vi tidigare jobbat med vårt bibliotek som ett exempel men det är i vissa fall lite för svårt för att verkligen kunna förstå och jobba med uppgifterna på ett bra sätt.
Som alternativ har jag nu skapat en annan uppgift där vi bygger en enkel applikation kring bilar och ägare till bilarna. Denna tutorial kommer gå igenom hela processen från tanke till färdig produkt, här kommer det finnas exempel på olika typer av frågor samt all kod som behövs för att kunna skapa, uppdatera och ta bort data från vår applikation via php.

Om du känner dig osäker på databasteorin så finns detta i moment05.

Databasmodellering

För att kunna översätta verkligheten till ett databaschema så använder vi oss av databasmodellering. Databasmodelleringen sker i fera steg och vi börjar med att skapa ett ER-diagram.

ER-diagram

Vi börjar med att identifiera det "saker" som skall finnas i vårt system, i detta fall så kan vi se att det bör finnas bil och person, som är ägare till bilen. Vi väljer att skriva dessa entitetstyper i singular, på engelska, och ramar in dem i en rektangel.

Vi ritar nu ut sambanden mellan våra entiteter. Sambandet binds ihop med hjälp av en utdragen romb eller en diamantform som det ofta benäms som. I detta fallet så är vårt samband mellan Bil och Person ett ägande av bilen.

Dags att sätta sambandstyp mellan bil och person, här gäller sambandet;
En bil kan ägas av en person, en person kan äga flera bilar.

Dags att kika på vilka attribut (ett attribut är en egenskap som är kopplad till en entitet eller ett samband) som skall kopplas till våra entiteter. Vi ringar in attributen och drar streck till den entitet, eller det samband, som den tillhör.

Vi måste välja något attribut i varje entitet som skall kunna identifiera en post på något unikt sätt. När vi har valt detta attribut så stryker vi under det. Ibland är det enkelt att välja rätt attribut, ibland är det svårare. När det gäller person skulle vi kunna ha ett personnummer som unik nyckel, men ett personnummer är ganska känslig information så det vill vi inte använda i onödan. Registreringsnummer kunde vi ha använt som unik identifierare för bilen, men jag personligen gillar att ha unika nycklar utan meningsfullt värde, så kallade nonsensvärden. Den enda uppgiften för ett sådant värde är att skapa en unik nyckel som är lätt att länka ihop tabeller med.

Datamodell

ER-diagramet används för att få en bättre förståelse för verkligheten, nu skall vi se hur vi kan få in detta i en datamodell vilket sedan blir grunden för våra tabeller i databasen.
Det första vi skall göra är att göra om varje entitet till en tabell och låta attributen till entiteterna bilda kolumner.

Som du ser i vår datamodell så anger vi attributet, datatyp samt vilken typ av nyckel det är. Det går också att ange om ett visst attribut får innehålla "null", ett personId i tabellen car innebär att denna bilen inte har en ägare.

Databasen och SQL

Med hjälp av Vertabelo så kan vi skapa ett script som automatiskt skapar en databas åt oss. Detta förenklar vårt liv avsevärt, speciellt om vi senare inser att vi vill förändra databasstrukturen. Scriptet är bara att köra mot vår MySQL-databas i phpMyAdmin eller något annat verktyg.

Importera databasen

-- Created by Vertabelo (http://vertabelo.com)
-- Script type: create
-- Scope: [tables, references, sequences, views, procedures]
-- Generated at Wed Mar 18 14:45:50 UTC 2015


-- Prepare the database import
-- Drop database
DROP DATABASE IF EXISTS carDemo;

-- Create the database
CREATE DATABASE carDemo DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci;

-- Make the database active
use carDemo;

-- tables
-- Table car
CREATE TABLE car (
    carId int    NOT NULL  AUTO_INCREMENT,
    personId int    NULL ,
    registrationNr varchar(10)    NOT NULL ,
    make varchar(20)    NOT NULL ,
    model varchar(20)    NOT NULL ,
    year int    NOT NULL ,
    price int    NOT NULL ,
    CONSTRAINT car_pk PRIMARY KEY (carId)
) COLLATE utf8_swedish_ci
;
INSERT INTO `car` (`carId`, `personId`, `registrationNr`, `make`, `model`, `year`, `price`) VALUES
(1, 1, 'ABC123', 'Volvo', 'V70', 2012, 199000),
(2, NULL, 'DEF456', 'Saab', '95', 2008, 145000),
(3, 2, 'GHI789', 'Renault', 'Clio', 2010, 95000),
(4, 4, 'JKL987', 'Volvo', 'S40', 2005, 75000),
(5, 6, 'QWERTY', 'Mercedes-Benz', 'B 180', 2012, 185500),
(6, 1, 'BKW127', 'BMW', '320d', 2006, 89000),
(7, 4, 'ACO112', 'BMW', '520', 2011, 185000),
(8, 9, 'KAS911', 'Volvo', 'V70', 2001, 18000),
(9, 7, 'BUY111', 'VOLVO', 'V60', 2011, 159900),
(10, NULL, 'DYR BIL', 'Jaguar', 'F-Type', 2014, 789000);

-- Table person
CREATE TABLE person (
    personId int    NOT NULL  AUTO_INCREMENT,
    firstName varchar(30)    NOT NULL ,
    familyName varchar(30)    NOT NULL ,
    birthDate date    NOT NULL ,
    hasLicence bool    NOT NULL ,
    CONSTRAINT person_pk PRIMARY KEY (personId)
) COLLATE utf8_swedish_ci
;
INSERT INTO `person` (`personId`, `firstName`, `familyName`, `birthDate`, `hasLicence`) VALUES
(1, 'Johan', 'Hällgren', '1975-11-03', 1),
(2, 'Anders', 'Larsson', '1996-10-12', 1),
(3, 'Lisa', 'Larsson', '1955-07-14', 1),
(4, 'Klas', 'Klasson', '2000-01-01', 0),
(5, 'Bo', 'Bengtsson', '1980-11-14', 1),
(6, 'Tom', 'Tén', '1986-12-24', 0),
(7, 'Tand', 'Fé', '1904-05-12', 0),
(8, 'Claes', 'Kent', '1995-09-14', 1),
(9, 'Anna', 'Henriks', '1965-08-09', 1),
(10, 'Anna', 'Bo-Bengts', '1992-03-30', 1);





-- foreign keys
-- Reference:  owner (table: car)


ALTER TABLE car ADD CONSTRAINT owner FOREIGN KEY owner (personId)
    REFERENCES person (personId);



-- End of file.

Då går vi kort igenom den kod som har genererats från Vertabelo.

-- En kodrad som börjar med -- hanteras som en kommentar och kommer inte att köras.
Varje SQL-sats som exekveras avslutas med semikolon (;), det innebär att du kan skriva kod på en rad eller på flera rader, allt som finns innan ett semikolon kommer att köras.

I Vertabelo kan man välja att lägga till egen kod som skall köras, på raderna 7-15 har jag skrivit egna sql-kommandon;
Rad 09: Ta bort databasen carDemo, om den redan finns.
Rad 12: Skapa databasen carDemo med teckenkode utf-8.
Rad 15: Välj att jobba med databasen carDemo, vilket krävs för att phpMyAdmin vet vilken databas den skall jobba mot. Annars kommer alla andra scriptrader köras rakt ut i luften.

På raderna 21-31 skapas tabellen car, nyckelkommandot här är CREATE TABLE, sedan listas de attribut som finns i tabellen, sedan anges vilken datatyp attributet har samt om det är tillåtet att lagra NULL i attributet. Vi har valt att ge våra primärnyckar ett automatiskt ID, detta sköts av "AUTO_INCREMENT", vilket innebär att det är en räknare som hela tiden räknar upp det värdet automatiskt. När alla attribut är skapade anges vilket attribut som skall vara primärnyckel och slutligen anges vilken teckenkod som skall användas på tabellen.
Övriga tabeller skapas på samma sätt.

På rad 32-42 och 54-64 så har jag skapat en SQL-sats som populerar mina två tabeller med 10 poster i varje tabell. Eftersom man ofta skapar om tabellerna under utveckling så är det skönt att slippa knacka in dessa personer och bilar gång på gång när vi genererar ett nytt script från Vertabelo. Även dessa SQL-satser har jag lagt in i Vertabelo.

På rad 74-75 skapas kopplingen mellan tabellerna, här anges vilka tabeller som skall kopplas ihop och vilka attribut det är som kopplar ihop tabellerna.

All den kod som nu är genererad som ett SQL-skript kan vi naturligtvis skapa själva eller med hjälp av tex phpMyAdmin. I denna kurs behöver du inte lära dig att skriva all denna kod men du måste kunna skapa tabeller, attribut, nycklar och kopplingar för dina databaser till dina applikationer. Hur du gör det är inte lika viktigt.

INSERT

Insert använder vi för att populera, alltså lägga in data i en tabell.

INSERT INTO `person` (`personId`, `firstName`, `familyName`, `birthDate`, `hasLicence`)
VALUES
(NULL, 'Johan', 'Hällgren', '1975-11-03', 1);

Det som händer här är att vi med nyckelorder INSERT talar om att vi skall lagra något i tabellen Person. Vi visar också vilja attribut som skall få värden, här är det viktigt att attributnamn och värden har samma ordning i den första parentesen och i parentesen efter "VALUES", sker inte detta kommer data röras ihop och antigen får vi ett error eller så kommer felaktigt data lagras i fel attribut.

Kolla på värdet för personId som är satt till NULL, det innebär att vi inte skriver in något värde, istället kommer databasen ge detta attribut ett automatiskt värde, i detta fall ett inkrementerat värde, alltså ett nummer högre än värdet på posten innan.

Det går att skriva denna kod lite kortare, det kan ibland vara skönt när vi sedan skall skicka koden från php. Här gäller det dock att vara noga att vi har skrivit attributen i samma ordning som de är registrerade i tabellen.Felmarginalen här är noll, så var säker på vad du gör.

INSERT INTO person VALUES (NULL, 'Johan', 'Hällgren', '1975-11-03', 1);

DELETE

Delete är ett kommando som gör precis vad vi tror, det tar bort en, eller flera poster, i en tabell.

DELETE FROM car WHERE carId = 3;

I koden ovan så tar vi bort bilen som har carId = 1, vi kan välja att ta bort flera poster genom att t.ex. ta bort alla Volvobilar.

DELETE FROM car WHERE make = 'Volvo';

Var försiktig med delete, så länge du inte använder transaktionshantering så går det inte att återskapa några borttagna poster.

Säkerhetskopiera

I phpMyAdmin finns möjligheten att ta ut hela databasen som ett sql-script vilket innebär att alla tabeller, nyckar, relationer, andra strukturer samt tabellernas innehåll kan exporteras och enkelt flyttas till en annan databashanterare, eller läggas tillbaka i den egna databashanteraren.

SQL - söka efter data

Kör nu scriptet i MySQL på din egen webbserver. Det gör att du nu kan prova mina SQL-satser lokalt på din egna dator och kolla att du får samma svar, samt att du har möjlighet att förändra i SQL-satsen för att se vad som händer.

SQL, att ställa frågor

Sök med *

Nu skall vi lära oss att söka med SQL i vår tabell car.

SELECT *
FROM car

* (stjärna) betyder här att vi hämtar alla attribut från tabellen car.

carId personId registrationNr make model year price
1 1 ABC123 Volvo V70 2012 199000
2 nullDEF456 Saab 95 2008 145000
3 2 GHI789 Renault Clio 2010 95000
4 4 JKL987 Volvo S40 2005 75000
5 6 QWERTY Mercedes-Benz B 180 2012 185500
6 1 BKW127 BMW 320d 2006 89000
7 4 ACO112 BMW 520 2011 185000
8 9 KAS911 Volvo V70 2001 18000
9 7 BUY111 VOLVO V60 2011 159900
10 nullDYR BIL Jaguar F-Type 2014 789000

Om vi bara vill ha bilar som är av märke Volvo, skriver vi så här;

SELECT *
FROM car
WHERE make = 'Volvo';

Att skriva koden på tre eller en rad spelar ingen roll. Allt som skrivs innan semikolonet kommer att köras som en händelse.

SELECT * FROM car WHERE make = 'Volvo';
carId personId registrationNr make model year price
1 1 ABC123 Volvo V70 2012 199000
4 4 JKL987 Volvo S40 2005 75000
8 9 KAS911 Volvo V70 2001 18000
9 7 BUY111 VOLVO V60 2011 159900

Flera villkor

Om man vill använda flera olika villkor så går det enkelt att lägga till ett AND-villkor.

SELECT *
FROM car
WHERE make = 'Volvo'
AND model = 'V70';
carId personId registrationNr make model year price
1 1 ABC123 Volvo V70 2012 199000
8 9 KAS911 Volvo V70 2001 18000

Eller ett OR-villkor om det är ok med den ena eller den andra.

SELECT *
FROM car
WHERE make = 'Volvo'
OR make = 'BMW';
carId personId registrationNr make model year price
1 1 ABC123 Volvo V70 2012 199000
4 4 JKL987 Volvo S40 2005 75000
6 1 BKW127 BMW 320d 2006 89000
7 4 ACO112 BMW 520 2011 185000
8 9 KAS911 Volvo V70 2001 18000
9 7 BUY111 VOLVO V60 2011 159900

Sortera

Vi kan sortera genom att ange ORDER BY och sedan vilket attribut vi skall sortera på följt av ASC (ascending/stigande) eller DESC (decending/fallande) beroende på i vilken riktning vi vill sortera. Här går det också att sortera på olika attribut, men sorteringsordningen är samma ordning som du skriver dem.

Här sorterar vi alla Volvo och BMW på tillverkningsåret.

SELECT *
FROM car
WHERE make = 'Volvo'
OR make = 'BMW'
ORDER BY year ASC;
carId personId registrationNr make model year price
8 9 KAS911 Volvo V70 2001 18000
4 4 JKL987 Volvo S40 2005 75000
6 1 BKW127 BMW 320d 2006 89000
7 4 ACO112 BMW 520 2011 185000
9 7 BUY111 VOLVO V60 2011 159900
1 1 ABC123 Volvo V70 2012 199000

Här sorterar vi först på bilmärke och sedan på tillverkningsåret.

SELECT *
FROM car
WHERE make = 'Volvo'
OR make = 'BMW'
ORDER BY make ASC, year ASC;
carId personId registrationNr make model year price
6 1 BKW127 BMW 320d 2006 89000
7 4 ACO112 BMW 520 2011 185000
8 9 KAS911 Volvo V70 2001 18000
4 4 JKL987 Volvo S40 2005 75000
9 7 BUY111 VOLVO V60 2011 159900
1 1 ABC123 Volvo V70 2012 199000

Välj attribut

Eftersom ett resultset skall skickas från databasen till php-motorn så vill vi inte ha med data än vad som behövs. Därför är det viktigt att kunna skriva rätt SQL-sats så att vi bara får de attribut vi vill använda. För att göra detta så byter vi ut stjärnan mot de attribut som skall användas.

SELECT registrationNr, make, model, year
FROM car
ORDER BY year DESC;
registrationNr make model year
DYR BIL Jaguar F-Type 2014
ABC123 Volvo V70 2012
QWERTY Mercedes-Benz B 180 2012
ACO112 BMW 520 2011
BUY111 VOLVO V60 2011
GHI789 Renault Clio 2010
DEF456 Saab 95 2008
BKW127 BMW 320d 2006
JKL987 Volvo S40 2005
KAS911 Volvo V70 2001

SQL - söka från flera tabeller

Att söka från en tabell har bara en viss mängd av svårighet, det blir klurigare när vi skall slå ihop flera tabeller för att ställa frågor. För att ställa frågor över flera tabeller så behöver vi använda JOIN som nyckelbegrepp.

JOIN

INNER JOIN

Inner join används när vi bara vill ha resultat som kopplas. Ett exempel är en lista på alla bilar samt bilarnas ägare.

SELECT c.registrationNr, c.make, c.model, c.year, p.firstName, p.familyName
FROM car c
INNER JOIN person p
ON c.personId = p.personId
ORDER BY p.familyname, p.firstname, c.registrationNr;
registrationNr make model year firstName familyName
BUY111 VOLVO V60 2011 Tand
KAS911 Volvo V70 2001 Anna Henriks
ABC123 Volvo V70 2012 Johan Hällgren
BKW127 BMW 320d 2006 Johan Hällgren
ACO112 BMW 520 2011 Klas Klasson
JKL987 Volvo S40 2005 Klas Klasson
GHI789 Renault Clio 2010 Anders Larsson
QWERTY Mercedes-Benz B 180 2012 Tom Tén

Detta kräver kanske en förklaring: Vi söker efter info om både bilen och ägaren till bilen.
INNER JOIN talar om hur kopplingen ser ut, FROM car, INNER JOIN person och koppla "ON" personId i bägge tabellerna.
När jag talar om att jag skall använda car och person så ger jag också dessa tabeller ett alias som gör att jag slipper skriva ut hela tabellnamnet så fort jag använder ett attribut för någon tabell.

Skulle jag få oändligt med rader i mitt resultset, så har jag troligtvis gjort en felaktig JOIN och lyckats multiplicera tabellerna istället för att länka dem. 10 bilar * 10 personer gerr 100 poster i vårt resultset.

SELECT *
FROM car, person;

OUTER JOIN, LEFT/RIGHT

I vårt exempel så har vi bilar som inte ägs av någon och vi har personer som inte äger någon bil. INNER JOIN tar bara fram de resultat som har en fungerande koppling mellan två tabeller.
Ofta kan det finnas mening att ta fram en lista där kopplingen inte finns, vi kanske vill veta vilka bilar vi har i registret och om det finns en ägare vill vi se det, eller så vill vi ha en lista på alla personer och se om de har någon bil, senare kanske vi vill räkna hur många bilar varje person har.
För att lösa detta behöver vi jobba med OUTER JOIN, jag viasar med två exempel.

Först alla bilar som finns i vår tabell med eventuell ägare

SELECT c.registrationNr, c.make, c.model, c.year, c.price, p.firstName, p.familyName
FROM car c
LEFT OUTER JOIN person p
ON c.personId = p.personId
ORDER BY c.registrationNr;
registrationNr make model year price firstName familyName
ABC123 Volvo V70 2012 199000 Johan Hällgren
ACO112 BMW 520 2011 185000 Klas Klasson
BKW127 BMW 320d 2006 89000 Johan Hällgren
BUY111 VOLVO V60 2011 159900 Tand
DEF456 Saab 95 2008 145000 nullnull
DYR BIL Jaguar F-Type 2014 789000 nullnull
GHI789 Renault Clio 2010 95000 Anders Larsson
JKL987 Volvo S40 2005 75000 Klas Klasson
KAS911 Volvo V70 2001 18000 Anna Henriks
QWERTY Mercedes-Benz B 180 2012 185500 Tom Tén

Vi använder här en LEFT OUTER JOIN vilket innebär att vi vill hämta alla poster som finns i den "vänstra" tabellen, i detta fall car eftersom den skrivs först. Vi kan säga att hämta allt i tabellen car som uppfyller våra villkor och fyll sedan på med den informationen som eventuellt finns i tabellen person.

Vi tar ett exempel till, nu vänder vi på det så att det är den högra tabellen, person som blir huvudtabell och avgör vad som skall visas.

SELECT p.familyName, p.firstName, c.registrationNr, c.make, c.model, c.year, c.price
FROM car c
RIGHT OUTER JOIN person p
ON c.personId = p.personId
ORDER BY p.familyname, p.firstName;
familyName firstName registrationNr make model year price
Bengtsson Bo nullnullnullnullnull
Bo-Bengts Anna nullnullnullnullnull
Tand BUY111 VOLVO V60 2011 159900
Henriks Anna KAS911 Volvo V70 2001 18000
Hällgren Johan ABC123 Volvo V70 2012 199000
Hällgren Johan BKW127 BMW 320d 2006 89000
Kent Claes nullnullnullnullnull
Klasson Klas JKL987 Volvo S40 2005 75000
Klasson Klas ACO112 BMW 520 2011 185000
Larsson Anders GHI789 Renault Clio 2010 95000
Larsson Lisa nullnullnullnullnull
Tén Tom QWERTY Mercedes-Benz B 180 2012 185500

Nu visas alla personer som finns i tabellen person, har personen bil(ar) så kommer varje bil visas på en egen rad. Det innebär att de personer som har flera bilar följdaktligen kommer visas på flera rader. Personer som inte äger bilar har bara sitt namn och "null" i de rutor som visar info om eventuell bil.

SQL-funktioner

Du kanske undrar varför man vill ha en lista med alla personer om de inte har någon bil, men det är användbart om man vill ha en personallista och ta reda på det sammanlagda värdet för varje persons bilpark.
Jag skall bara visa detta exemplet, men det finns oändligt många möjligheter att i SQL använda sig av funktioner som räknar ut allt från antal, till medel-, max-, min-värden eller annat som kan behövas i ett system.

SELECT p.familyName, p.firstName, SUM(c.price) valueOfCars
FROM car c
RIGHT OUTER JOIN person p
ON c.personId = p.personId
GROUP BY p.firstName, p.familyName
ORDER BY valueOfCars DESC, p.familyName, p.firstName;
familyName firstName valueOfCars
Hällgren Johan 288000
Klasson Klas 260000
Tén Tom 185500
Tand 159900
Larsson Anders 95000
Henriks Anna 18000
Bengtsson Bo null
Bo-Bengts Anna null
Kent Claes null
Larsson Lisa null

Detta var bara ett snabbt exempel på vad man kan göra, för den som är intresserad finns det mycket mer avancerade funktioner att jobba med, det går också att slå ihop flera funktioner och även olika resultset som slås ihop till ett enda osv, osv...

Vyer

En vy fungerar som en virtuell tabell. Om vi ofta ställer en SQL-fråga som använder data från flera olika tabeller så blir denna SQL-fråga ofta ganska så stor, då kan vi välja att skapa en vy istället, vilket gör att vyn innehåller SQL-frågan och vi kan skicka en enklare SQL-sats till databasen. Vi visar med ett exempel.

Vi tar exemplet ovan där jag räknar ut summan av bilarnas värde för varje person.

Vi börjar med att skapa vyn, det innebär att vi lagrar sql-kommandot i databasen.

CREATE VIEW valueOfCars AS
SELECT p.familyName, p.firstName, SUM(c.price) valueOfCars 
FROM car c 
RIGHT OUTER JOIN person p 
ON c.personId = p.personId 
GROUP BY p.firstName, p.familyName
ORDER BY valueOfCars DESC, p.familyName, p.firstName;

Samma SQL-sats som tidigare förutom första raden där vi talar om att sql-satsen skall skapa en vy. Denna vyn kommer nu ligga i databasen och när vi vill ställa en fråga till vyn så skriver vi bara följande kod;

SELECT * 
FROM valueOfCars;

.... med resultatet....

familyName firstName valueOfCars
Hällgren Johan 288000
Klasson Klas 260000
Tén Tom 185500
Tand 159900
Larsson Anders 95000
Henriks Anna 18000
Bengtsson Bo null
Bo-Bengts Anna null
Kent Claes null
Larsson Lisa null

Det går naturligtvis att ge vyn vissa argument också....

SELECT * 
FROM valueOfCars 
WHERE valueOfCars > 0;

.... med resultatet....

familyName firstName valueOfCars
Hällgren Johan 288000
Klasson Klas 260000
Tén Tom 185500
Tand 159900
Larsson Anders 95000
Henriks Anna 18000

Det gick ju smidigt. Innebär detta att vi alltid skall skapa vyer för varje SQL-sats som skall användas i ett projekt? Nej, använd det där det behövs. Vem som bestämmer när det behövs? Det gör du själv. Fundera på vilket som är smidigast och lös problemet på det sättet.

Databasen & PHP

Uppkopplingen

PDO eller mysql(i)

Det finns olika sätt att via PHP koppla sig mot en MySQL-databas. Om du använder äldre webbapplikationer så används ofta gränsnitten mysql eller mysqli. mysql var den första varianten och den är vidareutvecklad till mysqli, i står för improved. Fördelen med mysql(i) är att de är förhållandevis enkla att jobba med, nackdelen är att de har sämre säkerhet och fungerar bara på databasen mysql. Vill du använda en annan databas så måste du använda andra funktioner som ser ut och fungerar lite annorlunda.
PHP PDO, PHP Data Object, är ett gränssnitt som är objektorienterat, det har högre inbyggd säkerhet och det jobbar på samma sätt mot olika databaser vilket innebär att du som utvecklare inte behöver bry dig om databasen är mySQL, SQLServer, Oracle eller något annat.
Det sägs också att vid nästa stora uppdatering av PHP, version 6, så kommer de äldre gränssnitten att fasas ut. Redan nu har utvecklare jobba att göra för att fixa ordning på system som använder sig av vissa mysql-funktioner som inte längre har stöd, utan behöver bytas ut mot motsvarande mysqli-funktioner.

Det enklaste sättet att lära sig jobba med PHP PDO är att göra ett praktiskt exempel. Vi har redan en exempeldatabas, Car, så vi testar att bygga en applikation i PHP som kan visa och ändra data i databasen.

Som så mycket annat i denna kursen så finns det olika sätt att göra saker på. Som utvecklare kan du gå oerhört djupt in i olika delar, så även i detta ämne. Jag tar inte ställning till vad som är bättre eller sämre än något annat, inom ramen för denna kurs är det fokus på att få det att fungera och ta bort de vanligaste och tydligaste säkerhetshålen. Lär dig kopiera vårt exempel och bygg om det för dina behov, det är gott nog så.

Koppla upp via php

Vi börjar med att sätta upp ett antal variabler, så att det är lätt att andra data för en uppkoppling. Sedan försöker vi göra en databasuppkoppling, fungerar detta så har vi skapat ett objekt. Skulle det gå fel så tar vi hand om detta och meddelar användaren att något har gått fel.

<?php
// dbConnCar.php
# Variabler
$host = "localhost"; // Den server där databasen ligger
$user = "root";      // Ditt användarnamn
$pwd  = "root";      // Ditt lösenord
$db   = "carDemo";   // Databasen vi vill jobba mot

# dsn - data source name
$dsn = "mysql:host=".$host.";dbname=".$db;

# Inställningar som körs när objektet skapas
$options  = array(
	PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'",
	PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_EMULATE_PREPARES, false);

# Skapa objektet eller kasta ett fel
try {
	$pdo = new PDO($dsn, $user, $pwd, $options);
}
catch(Exception $e) {
    die('Could not connect to the database:<br/>'.$e);
}
?>

På rad 14-16 så görs några inställningar som kommer gälla för databasuppkopplingen;
Rad 14. Här bestäms teckenkoden för objektets uppkoppling.
Rad 15. Här bestäms vilken nivå av felkoder som skall visas när dessa uppstår.
Rad 16. Här bestäms om SQL-satserna måste prepareras innan de körs.

Ställ fråga mot databasen

Nu när vi har ett databasobjekt är det detta objekt vi skall jobba med för att skapa och köra SQL-frågor.

<?php
require_once('dbConnCar.php');
$sql = "SELECT registrationNr, make, model FROM car WHERE make = 'Volvo';";
$stm = $pdo->prepare($sql);
$stm->execute();
$res = $stm->fetchAll(PDO::FETCH_ASSOC);
?>

Variabeln $sth är ett objekt av klassen PDO Statement och är objektet av den sql-fråga som vi skapar på rad 3 och som vi förbereder på rad 4. Just att köra prepare gör att vi kan dra nytta av inbyggd säkerhet för att unvika sabotage av vår databas.
På rad 5 så exekveras frågan och på rad 6 hämtar vi ut vårt resultset från databasen och lagrar detta i variabeln $res. Vi använder argumentet PDO::FETCH_ASSOC för att välja hur våra index skall namnges i arrayen. I detta fallet så får indexet samma namn som attributet i sql-frågan, jag tycker att det blir lättare att arbeta med det om man vet att bilens märke finns på indexet 'make' istället för i en siffra som kan vara 2, 3 eller 7.
Kanske läge att förklara vad som händer på rad 2. Jag har valt att läsa in filen dbConnCar.php med funktionen require_once(), filens namn med ändelsen .php använder jag för att visa att detta är en fil som aldrig körs själv utan bara inkluderas, den innehåller php-kod som vilket php-fil som helst. Funktionen require_once() används för att säkerställa att denna filen bara läses in en gång, skulle man inkludera samma fil från flera olika delar av programmet, vilket man riskerar att göra i större filer, så ser denna funktionen till att den inte läses in igen om den redan finns inläst. Require_once och include_once gör egentligen samma sak, förutom när det blir något fel med inläsningen och ett error skall genereras, då skickar include/include_once en varning medan require/require_once stoppar scriptet och skickar ett "fatal error".

Innehållet i $res är nu en tvådimensionell array som vi senare skall jobba med. Vi kikar på variabeln genom att göra en var_dump() på den.

array (
  0 => 
  array (
    'carId' => 1,
    'personId' => 1,
    'registrationNr' => 'ABC123',
    'make' => 'Volvo',
    'model' => 'V70',
    'year' => 2012,
    'price' => 199000,
  ),
  1 => 
  array (
    'carId' => 4,
    'personId' => 4,
    'registrationNr' => 'JKL987',
    'make' => 'Volvo',
    'model' => 'S40',
    'year' => 2005,
    'price' => 75000,
  ),
  2 => 
  array (
    'carId' => 8,
    'personId' => 9,
    'registrationNr' => 'KAS911',
    'make' => 'Volvo',
    'model' => 'V70',
    'year' => 2001,
    'price' => 18000,
  ),
  3 => 
  array (
    'carId' => 9,
    'personId' => 7,
    'registrationNr' => 'BUY111',
    'make' => 'VOLVO',
    'model' => 'V60',
    'year' => 2011,
    'price' => 159900,
  ),
)

I exemplet ovan så fuskade jag lite. Vi valde ut alla bilar av märket 'Volvo' och oftast när vi vill göra en sådan sökning så vill ju att bilmärket skall komma in som en parameter, säkert från en länk eller ett formulär, och inte hårdkodat som i detta fallet. Vi måste alltså lära oss att binda parameterar till vår sql-sats. I exemplet så antar vi att 'make' har kommit från ett formulär, kanske från en dropdown list. Samma exempel som ovan i övrigt.

<?php
require_once('dbConnCar.php');
$sql = "SELECT registrationNr, make, model FROM car WHERE make = :make;";
$stm = $pdo->prepare($sql);
$stm->execute( array('make' => $_POST['make']));
$res = $stm->fetchAll(PDO::FETCH_ASSOC);
?>

Hantera resultset

Nu när vi har ett fint resultset så är det inga problem att loopa igenom detta och skriva ut det som en fin tabell

<?php
// -------------------------------------------------------------------------------------------
//
// Requires and includes
//
require_once('dbConnCar.php');


// -------------------------------------------------------------------------------------------
//
// Create the content
//
$make = "Volvo";

$sql = "SELECT registrationNr, make, model FROM car WHERE make = :make;";
$stm = $pdo->prepare($sql);
$stm->execute( array('make' => $make));
$res = $stm->fetchAll(PDO::FETCH_ASSOC);

# Create the table from resultset
$table ="<table><tr><th>registrationNr</th><th>make</th><th>model</th></tr>";

foreach ($res as $row) {
  $table .= "<tr>";
  $table .= "<td>{$row['registrationNr']}</td>";
  $table .= "<td>{$row['make']}</td>";
  $table .= "<td>{$row['model']}</td>";
  $table .= "</tr>\n";
}
$table .= "</table>"; 



// -------------------------------------------------------------------------------------------
//
// Create the html-page
//
$html = <<< EOD
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>PDO, ett exempel</title>
</head>
<body>{$table}</body>
</html>
EOD;



// -------------------------------------------------------------------------------------------
//
// Print the html-page
//
echo $html;
?>

Det borde inte vara några konstigheter vad som händer i denna sidan, först ställs frågan till databasen och tabellen byggs upp. Sedan byggs hela sidan runt tabellen och slutligen så skrivs hela sidan ut på rad 36. Eftersom vi har ställt en specifik fråga så vet vi att det svar vi får är exakt det som skall skrivas ut, därför kan vi använda oss av foreach och låta hela $res loopas igenom rad för rad genom $row som vi sedan jobbar med.
Eftersom vi inte har något formulär kopplat till övningen så skapade jag en variabel $make som simulerar ett inmatat värde på vilket bilmärke som skall skrivas ut.

Och resultatet blir så här;

carId personId registrationNr make model year price
1 1 ABC123 Volvo V70 2012 199000
4 4 JKL987 Volvo S40 2005 75000
8 9 KAS911 Volvo V70 2001 18000
9 7 BUY111 VOLVO V60 2011 159900

INSERT, UPDATE & DELETE

När du nu kan skapa ett objekt som kommunicerar med databasen och hantera ett resultset från databasen så kan du börja bygga egna applikationer.

Att preparera och köra en SQL-sats mot objektet görs på samma sätt oavsett om det är INSERT, UPDATE eller DELETE du vill köra mot databasen. Genom att bygga formulär, knapar och länkar kan du nu styra din applikation precis som du vill.

Säkerhet

Genom att använda PDO har vi automatiskt skyddat oss mot första nivån av SQL-injection vilket är fullt tillräckligt för denna kursen. Det handlar om att applikationen/databasen skall vara skyddad mot intrång genom att användaren skriver en sql-sats som gör något annat än vad som är tanken. Det klassiska exemplet är att det är möjligt att logga in genom att manipulera sättet som en sql-sats byggs upp. Tanken är ju att användaren skall skriva så här, förutom att passwordsrutan skall vara type password;

User:
Pwd:

Då blir ju SQL-satsen såhär;

SELECT * 
FROM user 
WHERE username = 'admin'
AND password = 'qwerty';

Det är ju precis vad vi vill, men den som vet hur SQL är uppbyggt och vilken svaghet det finns om man inte använder sig av skydd mot SQL Injection skulle kunna skriva följande i formuläret:

User:
Pwd:

Då skulle istället följande kod skickas till databasen;

SELECT * 
FROM user
WHERE username = 'admin' OR 1 = 1; /*
AND password = '';

Vilket innebär att vi här kollar om username är admin eller om 1 = 1 sedan kommenterar vi bort resten av SQL-satsen. Detta innebär att vi troligtvis kommer få ett stort antal rader med användare som resultat på frågan. Finns det många resultat och utvecklaren har slarvat så bör hen se till att det är den första som skall användas. Vilken användare är den första att skapas i ett system? Vilka rättigheter har denna användare?

När vi jobbar med PDO så har vi extra skydd för sådana här säkerhetshål. Vissa databaser har också skapat visst skydd för det men eftersom det finns några system som har några år på nacken och som fortfarande rullar så kan man inte täppa till alla hål på en gång.