Accés a dades. DML

Mitjançant el següent script PHP d’exemple, veurem com inserir, actualitzar i eliminar dades d’una taula d’una base de dades SQLite3:

<?php
 
//PAS 1: Connexió de la base de dades passada per paràmetre
$baseDades = new SQLite3("/home/fta/BaseDadesSQLite/RRHH.db");
 
if (!$baseDades) { //No s'ha pogut obrir la base de dades
    echo "La base de dades no s'ha pogut obrir.</br>";
} else { //S'ha pogut obrir la base de dades
    echo "La base de dades s'ha obert correctament.</br>";
}
 
/* PAS 2: Inserim dos treballadors en la taula treballadors de la base de dades RRHH.db
 * És recomanable incloure totes les consultes relacionades entre les ordres SQLite3 BEGIN i COMMIT.
 * Si no ho fem així, SQLite barreja automàticament totes les consultes
 * alentint notablement tot el procés.
 */
 
//Executem l'ordre BEGIN. 
//Inici del procés (transacció)
$baseDades->exec('BEGIN');
 
//Inserim les dades
$baseDades->exec('INSERT INTO "treballadors" ("nif", "nom", "edat", "baixa") VALUES ("22222222J", "Pep Mesquida", "22", "0")');
$baseDades->query('INSERT INTO "treballadors" ("nif", "nom", "edat", "baixa") VALUES ("11111111H", "Paula Sastre", "24", "0")');
$baseDades->query('INSERT INTO "treballadors" ("nif", "nom", "edat", "baixa") VALUES ("44444444A", "Pere Cullera", "25", "0")');
 
//Executem l'ordre COMMIT. 
//Tanca el procés i confirma els canvis a la base de dades
$baseDades->exec('COMMIT');
 
 
$baseDades->exec('BEGIN');
 
//Modifiquem el camp baixa de tots els registres amb valor 0 pel valor 1
if ($baseDades->query('UPDATE "treballadors" SET "baixa" = 0')) {
 
    echo "S'han modificat tots els registres amb èxit.</br>";
 
} else { //S'ha pogut obrir la base de dades
 
    echo "No s'han pogut modificar els registres.</br>";
 
}
 
$baseDades->exec('COMMIT');
 
 
/*$baseDades->exec('BEGIN');
 
//Eliminem la treballadora amb NIF 00000000T
if ($baseDades->exec('DELETE FROM treballadors WHERE nif = "11111111H"')) {
 
    echo "S'ha eliminat la treballadora amb èxit.</br>";
 
} else { //S'ha pogut obrir la base de dades
 
    echo "La treballadora no s'ha pogut modificar.</br>";
 
}
 
$baseDades->exec('COMMIT');*/
 
 
$baseDades->close(); //Tanquem la base de dades

Si ens fixem en l’script de l’exemple, veurem que tant per inserir, com per actualitzar, com per eliminar dades de la taula treballadors de la base de dades RRHH.db, sempre fem servir el mètode exec o query de la classe SQLite3 de PHP. Tant l’un com l’altre fan el mateix, retornen verdader si l’operació feta en la taula ha estat un èxit, i fals en cas contrari. A tots dos mètodes com a paràmetre els hi hem de passar la sentència SQL que farà l’operació sobre la base de dades, en el cas de la inserció fem servir INSERT, en el de l’actualització UPDATE i en el de l’eliminació DELETE.

Per finalitzar, en l’script podem veure que fem més d’una consulta a la base de dades mitjançant els mètodes query o exec, i que les consultes relacionades entre si les escrivim entre les ordres BEGIN i COMMIT, això és així perquè quan treballem amb SQLite3, si en un mateix script hi ha consultes que no estan relacionades entre si sense agrupar, SQLite3 les barreja automàticament alentint notablement tot el procés. Per solucionar això, el que fem és executar mitjançant el mètode exec l’ordre BEGIN que indica al sistema que s’iniciarà una transacció, executar les consultes relacionades entre si, en l’script mitjançant els mètodes exec o query, i finalment, executar l’ordre COMMIT també amb el mètode exec, que tanca la transacció i confirma els canvis a la base de dades.

Mitjançant el següent script PHP d’exemple, veurem com fer una consulta simple mitjançant la sentència SELECT de SQL a una taula d’una base de dades SQLite3:

<?php
 
//PAS 1: Connexió de la base de dades passada per paràmetre
$baseDades = new SQLite3("/home/fta/BaseDadesSQLite/RRHH.db");
 
if (!$baseDades) { //No s'ha pogut obrir la base de dades
    echo "La base de dades no s'ha pogut obrir.</br>";
} else { //S'ha pogut obrir la base de dades
    echo "La base de dades s'ha obert correctament.</br>";
}
 
//PAS 2: Consultem el nom i NIF dels treballadors majors de 22 anys
 
//Fem la consulta. A la variable $resultat, guardarem un objecte de la classe SQLite3Result
$resultat = $baseDades->query('SELECT "nif", "nom" FROM "treballadors" WHERE "edat" > 22');
 
//PAS 3: LLegim el resultat de la consulta per mostrar el NIF i nom
while ($registre = $resultat->fetchArray()) { 
 
       echo "NIF: " . $registre["nif"] . " - Nom: " . $registre["nom"] . "</br>"; 
 
}
 
//PAS 4: Tanquem la base de dades
$baseDades->close(); //Tanquem la base de dades

Si ens fixem en l’script de l’exemple, veurem que per realitzar una consulta simple mitjançant la sentència SQL SELECT, fem servir el mètode query de la classe SQLite3 de PHP. En aquest cas el mètode query, retornarà el resultat de la consulta, que en el cas de l’script és el nom i NIF de tots els treballadors i treballadores majors de 22 anys de la taula treballadors de la base de dades RRHH.db. Aquest resultat el retorna en format d’objecte de la classe SQLite3Result de PHP i el guarda en la variable $resultat.

Per poder llegir el contingut d’aquest objecte, farem servir el mètode fetchArray de la classe SQLite3Result de PHP al qual cridarem mitjançant la variable $resultat (objecte de la classe SQLite3Result) i l’operador →. Aquest mètode ens retorna en format d’array indexat associativament i numèricament, el contingut (en el cas de l’script NIF i nom) de cadascun dels registres de la taula (en el cas de l’script la taula treballadors) que ens retorna la consulta feta mitjançant el mètode query. Per tant, si l’escrivim dins d’una estructura iterativa, com el ‘while’ de l’script, anirà assignant a la variable $registre l’array indexat associativament i numèricament amb el NIF i nom dels treballadors i treballadores de la taula treballadors majors de 22 anys, fins a l’últim treballador retornat per la consulta.

Per accedir a les dades retornades per la consulta, no ens caldrà més que anar consultant el contingut de la variable $registre, que no és més que consultar el contingut de l’array que se li ha assignat mitjançant els índexs associatius i els numèrics. En cas de fer els índexs associatius, hem de tenir en compte que aquests tindran el mateix nom del camp del valor que tenen associat, en l’script d’exemple a l’índex “nif” li correspondrà el valor del camp “nif” de la taula treballadors i a l’índex “nom” el valor del camp “nom” de la taula treballadors.

Les consultes agregades SQLite3 són aquelles que fan servir les seves funcions agregades, què són les funcions que en retornen un únic valor escalar com la mitjana d’un conjunt de valors.

Mitjançant el següent script PHP d’exemple, veurem com fer una consulta agregada mitjançant la sentència SELECT de SQL a una taula d’una base de dades SQLite3:

<?php
 
//PAS 1: Obrim la base de dades passada per paràmetre
$baseDades = new SQLite3("/home/fta/BaseDadesSQLite/RRHH.db");
 
if (!$baseDades) { //No s'ha pogut obrir la base de dades
    echo "La base de dades no s'ha pogut obrir.</br>";
} else { //S'ha pogut obrir la base de dades
    echo "La base de dades s'ha obert correctament.</br>";
}
 
 
/* PAS 2: Consultem la mitjana d'edat dels treballadors i treballadores mitjançant la
 * funció agregada AVG*/
$resultat = $baseDades->query('SELECT AVG("edat") FROM "treballadors"');
 
 
//PAS 3: LLegim el resultat de la consulta per mostrar el resultat
$resultatArray=$resultat->fetchArray(); 
 
echo "la mitjana d'edat dels treballadors i treballadores és: ".$resultatArray[0];
 
//PAS 4: Tanquem la base de dades
$baseDades->close(); //Tanquem la base de dades

Si ens fixem en l’script de l’exemple, veurem que per realitzar una consulta agregada ho fem exactament igual que com ho fem amb les consultes simples, l’única diferència la trobem a l’hora de llegir el seu resultat. Com només està retornant un valor escalar, el mètode fetchArray només retornarà un array numèric; ja que no hi ha cap camp de la taula al qual pertanyi aquest valor, per tant, és impossible que existeix un índex associatiu amb el mateix nom que el camp. De la mateixa manera, aquest únic array numèric, contindrà un únic element amb índex 0 i com a contingut el valor escalar que retorna la consulta feta amb el mètode query.

En el cas de l’script d’exemple, el valor escalar que se’ns retornarà, és la mitjana d’edat de tots els treballadors i treballadores de la taula treballadors de la base de dades RRHH.db. La consulta la farem mitjançant la funció agregada AVG de SQLite3 què és la que ens retorna la mitjana d’un conjunt de valors, en aquest cas els de les edats dels treballadors i treballadores que trobem en el camp “edat” de la taula.

Per accedir a les dades retornades per la consulta mitjançant el mètode fetchArray, no caldrà utilitzar una estructura iterativa com ‘while’, ja que només ens retornarà un array, él que guarda el valor escalar, en el cas de l’script d’exemple la mitjana d’edat dels treballadors i treballadores, guardat en la posició 0 de l’array guardat en la variable $resultatArray.

Mitjançant el següent script PHP d’exemple, veurem com fer una consulta mitjançant la sentència SELECT de SQL a més d’una taula d’una base de dades SQLite3:

<?php
 
//PAS 1: Obrim la base de dades passada per paràmetre
$baseDades = new SQLite3("/home/fta/BaseDadesSQLite/RRHH.db");
 
if (!$baseDades) { //No s'ha pogut obrir la base de dades
    echo "La base de dades no s'ha pogut obrir.</br>";
} else { //S'ha pogut obrir la base de dades
    echo "La base de dades s'ha obert correctament.</br>";
}
 
// PAS 2: Consultem el NIF, nom i departament de tots els treballadors
 
//Fem la consulta. A la variable $resultat, guardarem un objecte de la classe SQLite3Result
$resultat = $baseDades->query('SELECT t.nif, t.nom, d.nom FROM treballadors t, departaments d WHERE t.departament= d.codi');
 
//PAS 3: LLegim el resultat de la consulta
while ($arrayResultat = $resultat->fetchArray()) {
 
       echo "NIF: " . $arrayResultat[0] . " - Nom: " . $arrayResultat[1] ." - Department: " . $arrayResultat[2] . "</br>"; 
 
}
 
//PAS 4: Tanquem la base de dades
$baseDades->close(); //Tanquem la base de dades

Si ens fixem en l’script de l’exemple, veurem que per realitzar una consulta a més d’una taula ho fem exactament igual que com ho fem amb les consultes simples, però utilitzant una sentència SELECT que consulti més d’una taula. Només hem de tenir en compte que a l’hora de llegir el resultat, si hi ha un camp d’algunes de les taules de la consulta amb el mateix nom, com en el cas de l’script de l’exemple el camp “nom” de la taula treballadors i el camp “nom” de la taula departaments, els arrays que ens retornarà el mètode fetchArray estaran indexats associativament i numèricament, menys en el cas dels camps duplicats, on només tindrà índex associatiu el camp d’una de les taules, la resta només tindran índexs numèrics.

En el cas de l’script d’exemple, consultem el NIF, nom i departament de tots els treballadors i treballadores de la taula treballadors que tenen assignat un departament de la taula departaments mitjançant el camp “departament” que és clau forana al camp “codi” de la taula departaments.

Així doncs, el mètode fetchArray ens retornarà per cada resultat de la consulta, un array format per un índex numèric 0 i associatiu “nif” on es guardarà el NIF, un índex numèric 2 i associatiu “nom” on es guardarà el nom del departament i un índex numèric 1 on es guardarà el nom del treballador o treballadora, ja que el nom del camp on guardem aquest valor, coincideix amb el nom del camp on guardem el nom del departament. En aquest cas, per accedir a les dades retornades per la consulta mitjançant el mètode fetchArray, el millor és utilitzar els índexs numèrics dels arrays per evitar confusions.