PHP Operacións BD Mysql

Introdución

• Como comentamos no punto anterior, para conectarnos a un xestor de base de datos específico imos ter que ter instalados os drivers correspondentes.

Un driver é o software que nos permite comunicarnos cun xestor de base de datos, pero normalmente non imos facer dita comunicación utilizando o driver directamente se non que o usaremos a través dunha API, que son un conxunto de clases, funcións,... que nos permiten facer uso do driver e conectar a un xestor de base de datos.

• Existen dous tipos de API´s:

• Procedimentais: Facemos uso de funcións para realizar as operacións contra a base de datos.
• Orientadas a obxectos: Instanciamos clases e chamamos a métodos dos obxectos creados.

Normalmente faremos uso desta segunda opción.

• En PHP, ditas API´s se 'activan' facendo uso de Extensións.

Indicar que unha API non é o mesmo que unha extensión en PHP, xa que algunhas extensións non proporcionan ningunha API ao usuario (por exemplo a extensión do driver PDO MySQL, non proporciona ningunha API ó programador PHP).

• Existen tres APIs principais para conectar a un servidor de bases de datos MySQL:

• Extensión MySQL de PHP
• Extensión mysqli de PHP
• Obxectos de Datos de PHP (PDO)

• Para conectarnos imos utilizar as funcións mysqli (mysql improved) que foron introducidas a partires de Mysql 4.1

A extensión mysqli ven incluída nas versiones PHP 5 e posteriores.

Algunha das melloras de dita extensión con respecto á extensión mysql:

• Interfaz orientada a obxectos
• Soporte para declaracións preparadas.
• Soporte para Transaccións

Dita extensión tamén ten unha versión procedural, de tal forma que imos poder chamar ás funcións de dúas maneiras diferentes:

Exemplo de función cun estilo procedimental que pecha unha conexión a unha base de datos ($link é un identificador cando se fai a conexión contra a base de datos):

1 mysqli_close($link);

Exemplo de función cun estilo obxectual que pecha unha conexión a unha base de datos:

1 mysqli::close();

• Aquí tedes un cadro comparativo obtido de php.net onde se ve o comentado anteriormente:

Nota: Máis información neste enlace.

Conexión

• Partimos da base de que xa temos instalado:

• O xestor de bases de datos Mysql
• Unha base de datos creada. Neste manual vai ter de nome PHP. Facer que o 'default collation' sexa utf8_spanish2_ci.
• Un usuario / password que teña todos os permisos sobre a base de datos creada. Neste manual o usuario/password creado son user_php/user_php.

Todo o anterior xa está indicado no punto anterior da wiki.

• Utilizando procedementos: mysqli_connect
• Utilizando obxectos: mysql::_construct

• Sintaxe (se indica a sintaxe do construtor e polo tanto será equivalente a facer un 'new' da clase mysqli):

1 mysqli::__construct ([ string $host = ini_get("mysqli.default_host") [, string $username = ini_get("mysqli.default_user") [, string $passwd = ini_get("mysqli.default_pw") [, string $dbname = "" [, int $port = ini_get("mysqli.default_port") [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )

Todos eles son optativos, pero normalmente indicaremos o nome de host, login, password e base de datos.

Os parámetros son:

• Host a conectar (cadea): Pode ser o nome ou a dirección IP. Normalmente imos poñer 'localhost' (se o xestor de bases de datos está instalado no mesmo equipo que o servidor web).
• Nome do usuario con acceso á base de datos (cadea): Nome do usuario que ten acceso á base de datos.
• Password (cadea): Password do usuario.
• Dbname (cadea): Nome da base de datos sobre a que imos traballar.
• Porto (numérico): Porto no que está escoitando o xestor de base de datos. No caso de mysql ó o 3306.
• Socket (cadea): Socket ou tubería para conectar có xestor de bases de datos. En Linux é un tipo especial de arquivo que serve para 'transmitir' información, neste caso, cara o xestor de bases de datos.

• Aplicado ao noso caso, teremos a seguinte orde:

1 $conex = new mysqli('localhost','user_php','user_php','PHP');

• En caso de erro, $conex é un obxecto que represente a conexión á base de datos.

• En caso de ter unha versión PHP anterior á PHP 5.2.9 e 5.3.0 teremos que chamar á función mysqli_connect_error() desta forma:

1 $conex = new mysqli('localhost','user_php','user_php','PHP'); 
2 if (mysqli_connect_error()){
3    die('Erro de Conexión (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
4 }

• En caso de ter unha versión PHP posterior á PHP 5.2.9 e 5.3.0:

1 $conex = new mysqli('localhost','user_php','user_php','PHP'); 
2 if ($conex->connect_error) {
3    die('Erro de Conexión (' . $conex->connect_errno . ') ' . $conex->connect_error);
4 }

Como vemos amosamos por un lado a mensaxe de erro que devolve o xestor de bases de datos e o número de erro asociado a esa mensaxe.

Podemos consultar os número de erro e mensaxes asociadas neste enlace.

• Por exemplo, imos modificar o password do usuario por un que non exista e veremos o resultado:

1 $conex = new mysqli('localhost','user_php','password_non_existe','PHP'); 
2 if (mysqli_connect_error()){
3    die('Erro de Conexión (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
4 }

Dará como resultado:

Gardando os datos de conexión nun lugar seguro

• Como é obvio os datos de base de datos, usuario e password son moi importantes e non debemos permitir que alguén poida descubrilos.

• O que faremos será o seguinte:

• Definiremos en forma de constantes ditos datos nun arquivo de include.
• Gardaremos dito arquivo fora do cartafol de acceso do noso sitio web.

• O primeiro punto, definir en forma de constante e nun arquivo de include os datos da conexión:

Arquivo: bd.inc.php:

1 <?php
2 
3 define ('SERVIDOR','localhost');
4 define ('USUARIO','user_php');
5 define ('PASSWORD','user_php');
6 define ('BD','PHP');

• Faremos un require deste arquivo en todas as páxinas que necesiten unha conexión á base de datos. Desta forma, se necesitamos cambiar algo soamente o cambiaríamos nun arquivo.

• O segundo punto consiste en descubrir cal é a ruta física á que apunta o noso servidor web e colocar o arquivo de include 'fora' desa ruta.

• Temos neste punto da Wiki información sobre como funciona Apache e como saber a onde apunta o servidor web no seu arquivo de configuración.

• Temos varias opcións:

• Facendo uso da constante __DIR__.
• Facendo uso do array global $_SERVER e dentro deste a entrada $_SERVER['DOCUMENT_ROOT'.

Se facemos un echo destas variables dentro dunha páxina do noso sitio web, obteremos unha ruta, coma por exemplo:

1 /var/www/html/

Nota: Se o facemos coa constante __DIR__ e a páxina que está a facer o echo se atopa dentro dun cartafol, tamén aparecerá o cartafol (por exemplo /var/www/html/cartafol/).

• Isto quere dicir que o servidor web (e polo tanto calquera usuario de Internet) vai poder acceder as páxinas que se atopan no cartafol /var/www/html e calquera dentro del.

Unha medida de seguridade é colocar o arquivo de include 'fora' desta ruta. Por exemplo, podemos crear un arquivo no cartafol /var/www/confBD

A dito cartafol ten que poder acceder o usuario que utiliza o Apache para acceder aos cartafoles a nivel do S.O. (no caso de Apache soe ser www-data, podedes miralo no punto da wiki indicado anteriormente).

• Creando o cartafol onde se vai gardar o arquivo de configuración
• 

  Facemos un cd /var/www. Creamos o cartafol: sudo mkdir confBD. Movemos o arquivo de include creado no paso anterior ao cartafol: mv /cartafol_arquivo_include/bd.inc.php /var/www/confBD.

• 

  Cambiamos os permisos: sudo chmod -R 550 /var/www/confBD e cambiamos o propietario: sudo chown -R www-data:www-data /var/www/confBD. Nota: O usuario e grupo pode variar dependendo do S.O. En Linux podedes sabelo mirando este enlace da wiki. En Windows o podedes saber neste enlace.

• Agora, calquera páxina que faga uso destes datos terá que facer o seguinte require:

1 <?php
2 
3 //error_reporting(0);  // Inabilita os erros de Apache se temos activada a directiva display_erros no php.ini
4 
5 
6 require ('/var/www/confBD/bd.inc.php');
7 $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);

O xogo de caracteres

• Debemos ter en conta que unha páxina web está 'aberta' a todo o mundo que se conecte a ela.

Cada persoa en diferentes países terá diferente xogos de caracteres no que cada carácter está relacionado cun código numérico.

• Se gardamos como dato dentro da base de datos unha cadea cun 'ñ' entre os seus datos, dito carácter estará asociado a un código nun xogo de caracteres. Se o usuario que vai cargar a páxina o carga cun xogo de caracteres diferente non vai poder visualizalo.

• Cando creamos aplicacións en PHP con acceso a base de datos teremos que ter en conta os seguintes xogos de caracteres:

• Xogo de caracteres da base de datos (no noso caso Mysql): É o que se coñece como Charset/Collation.

Se vos fixades cando creamos a base de datos en Mysql, escollemos o charset (primeira columna) e o collation (segunda columna).

Como indicamos antes, cando creamos a base de datos o charset/collation indicado foi utf8/utf8_spanish2_ci.

O charset é como mysql garda internamente os caracteres e os relaciona cun código numérico e o collation indica como comparar e ordenar o texto. Podedes consultar esta blog para obter máis información.

• Xogo de caracteres da páxina HTML.

Na páxina web indicamos coa etiqueta <meta> o xogo de caracteres para que o navegador web poida visualizalos correctamente.

No caso que nos ocupa teremos que poñer: <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Máis información en w3schools.

• No código PHP ao conectar coa base de datos.

Cando conectemos coa base de datos teremos que indicarlle que imos utilizar o xogo de caracteres utf8 con esta liña:

1 $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
2 $conex->set_charset('utf8');

• E FUNDAMENTAL QUE NOS TRES SITIOS ESTEA POSTO O MESMO XOGO DE CARACTERES.

Creando as táboas en Mysql

• Unha vez temos a base de datos teremos que facer o Modelo EE/R, pasalo ao Modelo Relacional e facer un Proceso de Normalización ata polo menos a forma normal de Boyce-Codd.

• Imos implementar unha base de datos para xestionar a venda de libros de segunda mano.

O modelo EE/R é o seguinte:

Nota:

• A táboa ESTADOS podería eliminarse e crear un campo estado de tipo enum en Mysql se ditos 'estados' van ser sempre os mesmos e non van modificarse...
• O campo 'imaxe' da táboa EXEMPLARES está nesta táboa e non na táboa LIBROS, xa que a idea é amosar ao comprador unha imaxe do exemplar para amosar o seu estado.

• O modelo relacional impplementando nunha base de datos Mysql é o seguinte:

CREATE TABLE `PHP`.`LIBROS` (
  `id_libro` INT NOT NULL AUTO_INCREMENT,
  `descripcion` VARCHAR(300) NOT NULL,
  PRIMARY KEY (`id_libro`));

CREATE TABLE `PHP`.`ESTADOS` (
  `id_estado` INT NOT NULL AUTO_INCREMENT,
  `descripcion` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id_estado`));

 CREATE TABLE `PHP`.`EXEMPLARES` (
  `libro_id` INT NOT NULL,
  `examplar_id` INT NOT NULL,
  `imaxe` VARCHAR(100) NULL,
  `prezo` DECIMAL(6,2) NOT NULL,
  `estado_id` INT NULL,
  INDEX `fk_EXEMPLARES_LIBROS_idx` (`libro_id` ASC),
  INDEX `fk_EXEMPLARES_ESTADOS_idx` (`estado_id` ASC),
  PRIMARY KEY ( `libro_id`,`examplar_id`),
  CONSTRAINT `fk_EXEMPLARES_LIBROS`
    FOREIGN KEY (`libro_id`)
    REFERENCES `PHP`.`LIBROS` (`id_libro`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_EXEMPLARES_ESTADOS`
    FOREIGN KEY (`estado_id`)
    REFERENCES `PHP`.`ESTADOS` (`id_estado`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE);

CREATE TABLE `PHP`.`AUTORES` (
  `id_autor` INT NOT NULL,
  `apelido` VARCHAR(200) NOT NULL,
  `nome` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id_autor`));


CREATE TABLE `PHP`.`LIBROS_AUTORES` (
  `libro_id` INT NOT NULL,
  `autor_id` INT NOT NULL,
  PRIMARY KEY (`libro_id`, `autor_id`),
  INDEX `fk_LIBROS_AUTORES_AUTORES_idx` (`autor_id` ASC),
  CONSTRAINT `fk_LIBROS_AUTORES_LIBROS`
    FOREIGN KEY (`libro_id`)
    REFERENCES `PHP`.`LIBROS` (`id_libro`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_LIBROS_AUTORES_AUTORES`
    FOREIGN KEY (`autor_id`)
    REFERENCES `PHP`.`AUTORES` (`id_autor`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE);

• Para ter algún dato inicial imos a executar estas ordes:

INSERT INTO `ESTADOS` VALUES (1,'NOVO'),(2,'COMO NOVO'),(3,'DETERIODADO'),(4,'CON ANOTACIÓNS');


INSERT INTO `PHP`.`AUTORES` (`apelido`, `nome`) VALUES ('Drumont', 'Eduardo');

INSERT INTO `PHP`.`LIBROS` (`descripcion`) VALUES ('El caso Dreyfus, expresión del antisemitismo, temas, Gallica, BNF');
INSERT INTO `PHP`.`LIBROS` (`descripcion`) VALUES ('Les amis');

INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `examplar_id`, `imaxe`, `prezo`, `estado_id`) VALUES ('1', '1', 'dreyfus.jpg', '20,52', '2');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `examplar_id`, `imaxe`, `prezo`, `estado_id`) VALUES ('1', '2', 'dreyfus.jpg', '15,34', '3');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `examplar_id`, `imaxe`, `prezo`, `estado_id`) VALUES ('2', '1', 'amis.jpg', '35,35', '1');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `examplar_id`, `imaxe`, `prezo`, `estado_id`) VALUES ('2', '2', 'amis.jpg', '20,25', '4');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `examplar_id`, `imaxe`, `prezo`, `estado_id`) VALUES ('2', '3', 'amis.jpg', '25,34', '3');

• Agora faremos o seguinte:

• Crearemos dentro do noso sitio web (normalmente /var/www/html/) un cartafol de nome IMAXES
• Copiamos as imaxes que veñen a continuación a dito cartafol.
• Cambiaremos o propietario coa orde chown -R www-data:www-data IMAXES/

Nome: amis.jpg Nome: dreyfus.jpg

Regras de nomeado de táboas e columnas

• Aínda que non existen 'regras' únicas sobre como nomear as táboas e campos nos imos seguir as seguintes:

• Nomes de táboas en PLURAL e con MAIÚSCULAS. Desta forma distinguiremos facilmente o nome da táboa e os campos nunha consulta SQL.
• Nomes de campos en minúscula e singular.

• Se o campo é a clave primaria terá de nome: id_nometáboa_en_singular
• Se o campo é unha clave foránea terá de nome: nometáboa_en_singular_id
• Se o nome de campo está formado por varias palabras, estarán separadas por un guión baixo ( _ )

• Non usar til nin caracteres 'raros' como espazos en branco, interrogacións,...
• As claves foráneas irán ao final da lista de columnas da táboa.

Consulta

• Nota: Na cadea de conexión xa indicamos a base de datos á que queríamos conectarnos.

Se queremos escoller outra diferente teremos que facer uso da función mysqli::select_db.

Por exemplo:

1 $conex = new mysqli('localhost','user_php','user_php','PHP');
2 $conex->set_charset('utf8');
3 
4 if (!$mysqli->select_db('OutraBD')){
5      die('Erro ao cambiar de base de datos');
6 }

• Para conectarnos primeiro teremos que ter a consulta SQL.
• Indicar que o SQL (Structure Query Language) é a linguaxe utilizada para obter datos dunha base de datos relacional.

Imos crear unha consulta que nos devolva todos os libros da táboa LIBROS:

Para non ter as consultas no medio do código PHP das diferentes páxinas, imos definila no arquivo bd.inc.php creado no punto anterior.

define ('CONSULTAR_LIBROS','select id_libro,descripcion from LIBROS order by descripcion');

• Normas nos nomes das constantes:

• Comenzarán cun verbo que vai poder ser: CONSULTAR, INSERIR, MODIFICAR e ELIMINAR
• Seguirá cun guión baixo.
• En caso de consultar os datos dunha única táboa se indicará o nome da táboa. En caso de consultar datos de varias táboas se indicará con varias palabras separadas con guión baixo que indiquen o obxectivo da consulta.

• Funcións para realizar consultas a base de datos e funcións relacionadas:

• mysqli::query: Realiza a consulta
• mysqli_result: En caso de realizar unha consulta de tipo 'select' este obxecto representa o conxunto de resultados obtidos.

• int $mysqli_result->num_rows: Devolve o número de filas devoltas en caso de realizar unha consulta.
• array mysqli_result::fetch_assoc(void): Devolve nun array asociativo unha das filas do resultado da consulta e 'pasa' á fila seguinte. Cando remate e xa non teña máis filas devolve NULL.

• Aplicado ao exemplo que estamos a seguir:

 1 <?php
 2 
 3 require ('/var/www/confBD/bd.inc.php');
 4 //error_reporting(0);
 5 
 6 $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
 7 $conex->set_charset('utf8');
 8 
 9 if ($conex->connect_error){
10      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
11 }
12 ?>
13 
14 
15 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
16     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
17 <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
18     <head>
19         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
20         <title>Operacións BD</title>
21     </head>
22 
23     <body>
24 <?php        
25         
26         $result=$conex->query(CONSULTAR_LIBROS);
27         if ($result->num_rows>0){
28             echo "<table border='1'>";
29             while($row = $result->fetch_assoc()) {
30                 echo '<tr>';
31                 printf("<td>%s</td>",$row["descripcion"]);
32                 echo '</tr>';
33             }
34             echo '</table>';
35             $result->free();  // Liberamos da memoria os recursos
36         }
37         else {
38             echo "Non hai datos que amosar!!!!";
39         }
40         $conex->close();  // Pechamos a conexión
41         
42 ?>        
43     </body>    
44 </html>

• Resultado:

• Fixarse como neste exemplo, aínda que a consulta devolve o id_libro, como non o necesitamos non o amosamos ao usuario, xa que dito dato é un código interno que utilizamos como clave primaria.

• Imos facer agora unha modificación e amosar os datos nunha combobox (<select> en html), de tal forma que cando o usuario escolla un libro, amose os exemplares do mesmo.

Neste caso, na combo imos gardar o código do libro xa que cando escollamos un, necesitamos obter dito código para facer outra consulta buscando os exemplares dese libro...

 1     <body>
 2 <?php        
 3         
 4         $result=$conex->query(CONSULTAR_LIBROS);
 5         if ($result->num_rows>0){
 6             echo "<select name='lstLibros'>";
 7             echo "<option value='-1'></option>";
 8             while($row = $result->fetch_assoc()) {
 9                 printf("<option value='%d'>%s</option>",$row["id_libro"],$row["descripcion"]);
10             }
11             echo '</select>';
12             $result->free();  // Liberamos da memoria os recursos
13         }
14         else {
15             echo "Non hai datos que amosar!!!!";
16         }
17         $conex->close();  // Pechamos a conexión
18         
19 ?>        
20     </body>

• Resultado:

Permisos mínimos

-- Ángel D. Fernández González -- (2016).