sql bajo el entorno php/mysql mi juan pablo torres herrera universidad autÓnoma de baja california...

SQL BAJO EL ENTORNO PHP/MYSQL


MI JUAN PABLO TORRES HERRERA

UNIVERSIDAD AUTÓNOMA DE BAJA CALIFORNIA

FACULTAD DE INGENIERÍA ENSENADA

Mayo de 2005.

1. PROPÓSITO DE LA EXPOSICIÓN.

2. CONSIDERACIONES.

3. CONCEPTOS BÁSICOS.

4. ESTÁNDAR SQL BÁSICO.

5. NOCIONES DE PHP.

6. GESTIONANDO MYSQL EN LINUX.

7. FUNCIONES BÁSICAS DE MYSQL EN PHP.

8. DATOS BINARIOS CON MYSQL Y PHP.

9. ALTERNATIVAS A MYSQL Y PHP.

10. GESTIONANDO ARCHIVOS EN UN SERVIDOR LINUX.

11. RESUMEN DE LA EXPOSICIÓN.

12. BIBLIOGRAFÍA.


CONTENIDO

1. PROPÓSITO DE LA EXPOSICIÓN.


Describir mediante ejemplos la aplicación de lenguaje de consulta estructurada SQL con el soporte del lenguaje cliente servidor PHP y las funciones de conectividad a un servidor de bases de datos MYSQL.

Para entender SQL bajo el entorno PHP/MYSQL se requiere de conocimientos básicos acerca de:●Modelo de bases de datos relacional.●Programación estructurada.●Sistema operativo Unix.●HTML.

2. CONSIDERACIONES.

El estándar SQL es muy extenso y las funciones de MYSQL en PHP son muchas, por lo que no se cubre en su totalidad en esta exposición.

3. CONCEPTOS BÁSICOS.


SQL: Acrónimo de Structured Query Language o Lenguaje de Consulta Estructurada, el estándar actual fue escrito en 2003, sin embargo el más utilizado es la recomendación de 1999.

PHP: Acrónimo de Hypertext Preprocessor, es un lenguaje de "código abierto" interpretado, de alto nivel, incrustado en páginas HTML y ejecutado de lado del servidor.

MYSQL:Sistema administrador de bases de datos bajo arquitectura cliente servidor, del tipo “código abierto”, emplea el modelo relacional e interpreta la mayoria de las recomendaciones SQL.

4. ESTÁNDAR SQL BÁSICO.


proyecto persona

Modelo Entidad Relación

M:M

Diccionario de datos

id_proyecto Numero Llave primaria nombre_proy Caracter 30

id_persona Numero Llave primaria nombre Caracter 30salario Numero 9,2categoria Caracter 30edad Numeroestado_civil Caracter 15

Campo Tipo Longitud Regla integridad



persona

proyecto

persona_proyecto

id_persona Numero Llave primariaid_proyecto Numero Llave primaria

Veremos en este apartado la creación y modificación de tablas, inserción, modificación y borrado de registros, y consultas y subconsultas a tablas.Ejemplo 1: Modelo entidad relación y diccionario de datos para guardar proyectos.

CREATE TABLE proyecto ( id_proyecto INTEGER PRIMARY KEY AUTO_INCREMENT, nombre_proy VARCHAR(30) NOT NULL);

CREATE TABLE persona ( id_persona INTEGER PRIMARY KEY, nombre VARCHAR(30) NOT NULL, salario INTEGER(9), categoria VARCHAR(15), edad INTEGER, estado_civil VARCHAR(15) );

CREATE TABLE persona_proyecto ( id_persona INTEGER NOT NULL REFERENCES persona(id_persona), id_proyecto INTEGER NOT NULL REFERENCES proyecto(id_proyecto), PRIMARY KEY (id_persona, id_proyecto) );

4.1. CREAR TABLAS.SQL BAJO EL ENTORNO PHP/MYSQL

La cláusula Create Table permite crear tablas.Ejemplo 2: Creación de las tablas: proyecto, persona y persona_proyecto.

4.2. MODIFICAR LA ESTRUCTURA DE UNA TABLA.

ALTER TABLE proyecto ADD fecha DATE;

ALTER TABLE persona MODIFY nombre VARCHAR(50);

ALTER TABLE persona DROP estado_civil;


Modificar un campo puede ocasionar pérdida de información.

La cláusula Alter table permite agregar, modificar y borrar un campo.

Ejemplo 3: Agregar el campo fecha a la tabla proyecto.

Ejemplo 4: Modificar el campo nombre de la tabla persona cambiando el numero de caracteres de 30 a 50.

Ejemplo 5: Borrar el campo estado_civil de la tabla persona.

4.3. INSERTAR UN REGISTRO A UNA TABLA.

INSERT INTO persona VALUES ( 1, 'Angy', 50, 'aprendiz', 20, 'Casada');

INSERT INTO persona (id_persona, nombre, salario, categoria) VALUES ( 2, 'Benjamin', 100, 'aprendiz');


INSERT INTO proyecto (nombre_proy) VALUES ( 'Omega' );

El campo id_proyecto es autonúmerico y se asigna un valor automático

La cláusula Insert Into permite insertar un registro a una tabla.

Ejemplo 6: Agregar un registro a la tabla persona.

Ejemplo 7: Agregar un registro excepto el estado civil.

Ejemplo 8: Agregar un registro a la tabla proyecto.

4.4. MODIFICAR REGISTROS DE UNA TABLA.

UPDATE persona SET salario = 100 WHERE id_persona = 1;

DELETE persona.* FROM persona WHERE id_persona = 1;


4.5. BORRAR REGISTROS DE UNA TABLA.

DELETE proyecto.* FROM proyecto;

UPDATE persona SET salario = 200 ;

La cláusula Update permite modificar el valor de un campo de una tabla, sobre uno o varios registros.Ejemplo 9: Modificar el salario a 100 de la persona con el id_persona igual a 1.

Para borrar una tabla se emplea Drop Table.

La cláusula Delete permite borrar uno o varios registros.

Ejemplo 11: Borrar el registro de la persona con id_persona igual a 1.

Ejemplo 12: Borrar todos los registros de la tabla persona.

Ejemplo 10: Modificar el salario a 200 de todas las personas.

SELECT persona.* FROM persona;

SELECT persona.nombre, persona.salario FROM persona WHERE persona.salario > 100 ORDER BY persona.nombre;

SELECT MAX(p.salario) AS mayor FROM persona AS p;

mayor------140

nombre salario--------- --------Angy 100Caro 140Ely 100

id_persona nombre salario categoria edad estado_civil ------------- --------- --------- ----------- ----- --------------Angy 100 aprendiz 24 casada

SQL BAJO EL ENTORNO PHP/MYSQL4.6. CONSULTAR UNA TABLA.La cláusula Select From permite mostrar los registros de una tabla.

Ejemplo 13: Mostrar todos los registros de la tabla persona.

Ejemplo 15: Mostrar el salario mayor.

Ejemplo 14: Mostrar nombre y salario ordenados por nombre de las personas que ganan mas de 100.

Select soporta funciones estadisticas tales como: Sum, Max, Min, Avg, y Count, además de otras funciones como las de tipo fecha, ejemplo: Today.

SELECT p.categoria AS categoria, count(*) AS num_pers FROM persona AS p GROUP BY p.categoria ORDER BY p.categoria;

SELECT p.categoria AS categoria, count(*) AS num_pers FROM persona AS p GROUP BY p.categoria HAVING count()>1;

Continuación ...

categoria num_pers----------- ------------Aprendiz 1 Experto 1General 2

categoria num_pers----------- ------------General 2


Se permite agrupar el resultado de una consulta asociando una función estadistica sobre un mismo campo.

Ejemplo 16: Mostrar el número de personas por cada categoría.

Ejemplo 17: Mostrar el número de personas por cada categoría, pero solo aquellas categorias que cuenten con mas de un miembro.

La orden Having funciona como filtro a nivel de grupo y la funcion asociada debe ser igual a la empleada en el agrupamiento. Observe el alias para ser referencia a cada tabla

4.7. CONSULTAR DOS TABLAS.SQL BAJO EL ENTORNO PHP/MYSQL

TABLA RANCHOS TABLA PRODUCTOSclave nombre area clave producto------ --------- ---- ----- -------- 1 El mejor 130 1 Clavel 2 Margarita 150 1 Gladiola 3 Amigos 50 2 Clavel 4 Amanecer 90 3 Clavel 5 La rosa 80 3 Rosa 4 Rosa 5 Rosa

SELECT r.nombre AS RANCHO, p.producto AS PRODUCTO FROM ranchos AS r, productos AS p WHERE r.clave = p.clave;

RANCHO PRODUCTO------------- ---------------- El mejor Clavel El mejor GladiolaMargarita Clavel Amigos ClavelAmigos RosaAmanecer RosaLa rosa Rosa

La clave primaria de la tabla productos esta compuesta por clave y producto.

La consulta de dos tablas se efectua por medio de un campo en común, se emplea INNER o también WHERE.Ejemplo 18: Mostrar los productos por cada rancho.

a) Where.

b) Inner.

SELECT r.nombre AS RANCHO, p.producto AS PRODUCTO FROM ranchos AS r INNER JOIN productos AS p ON r.clave = p.clave;

SELECT r.nombre AS RANCHO, p.producto AS PRODUCTO FROM ranchos AS r, productos AS p WHERE r.clave = p.clave;

TABLA PERSONAid_persona nombre salario categoria edad------------- ----------- -------- ----------- ---- 1 Ely 100 Experto 19 2 Angy 100 General 22 3 Caro 140 General 23 4 David 80 Aprendiz 20 5 Pablo 90 Aprendiz 19

4.8. SUBCONSULTAS DE TABLAS.SQL BAJO EL ENTORNO PHP/MYSQL

SELECT p.nombre AS MEJORPAGADAFROM persona AS pWHERE p.salario = (SELECT MAX(p.salario) FROM persona AS p);

MEJORPAGADA----------------------Caro

Una consulta SQL puede emplear el resultado de una subconsulta como filtro.

Ejemplo 19: Mostrar la persona mejor pagada.

Ejemplo 20: Mostrar quienes son de mayor edad que todos los que tienen categoría Aprendiz.SELECT p.nombre AS MAYORFROM persona AS pWHERE p.edad > ALL ( SELECT p.edad FROM persona AS p WHERE p.categoria =‘Aprendiz’);

SQL emplea operadores que permiten más de un valor como entrada en una expresión, tal es caso de ALL el cual se combina con otros como mayor que >.

MAYOR-----------------AngyCaro

clave descripcion categoria fecha precio------ ------------ --------- ---------- ------1 Filete carnes 01/01/01 1002 Refresco bebidas 01/02/01 10


DELETE articulos.* FROM articulosWHERE fecha IN (SELECT a.fecha FROM articulos AS a) WHERE a.fecha>=TODAY()-7;

UPDATE articulosSET precio = precio * 1.10WHERE precio = (SELECT min(a.precio) FROM articulos AS a);

Una subconsulta también puede emplearse en una cláusula Delete y Update.

Ejemplo 21: Borrar los articulos con fecha de más 7 dias.

Ejemplo 22: Aumentar a un 10% el precio de los articulos más baratos.

Continuación ...

5. NOCIONES DE PHP.SQL BAJO EL ENTORNO PHP/MYSQL

• Libre, abierto y de código fuente disponible.

• Diseñado para la World Wide Web.

• Multiplataforma de Hardware y multisistema Operativo.

• Soporte para varios servidores Web y Bases de Datos.

• Buena documentación, y muchos ejemplos disponibles.

• Excelente integración con Apache y Mysql.

• Sintaxis clara, bien definida y bastante sencillo de aprender y utilizar.

• Parecido a C y PERL.

• Basado en módulos.

• Seguro.

• Amplia base de usuarios.

• No dependes de un único proveedor de servicios.

5.1. CARACTERÍSTICAS GENERALES DE PHP.

Las aplicaciones que se generan con PHP son también en su mayoria: libres, abiertos y de código abierto disponible.


<form action="comentarios.php" method="POST"> Su nombre: <input type="text" name="nombre" /> Email: <input type="text" name="email" /><br> Comentarios: <textarea cols="30" rows="3" name="comentario" /><br> <input type="submit" value="Enviar comentario" ></form>

<?//comentarios.php$correodestino="[email protected]";if (($nombre) && ($email) && ($comentario)) {$mail("$correodestino","Comentario","$nombre\n$email\n$comentario\n\n");} elseprint “Regresa para capturar todos los datos”; ?>

5.2. EJEMPLO SENCILLO EN PHP.

Ejemplo 23: Programa PHP que permite enviar un mensaje a un correo electrónico después de ser capturado en un formulario HTML.

Formulario HTML

PHP usa los servicios WEB tales como el electrónico.

6. GESTIONANDO MYSQL EN LINUX.SQL BAJO EL ENTORNO PHP/MYSQL

c) Línea de comandos MYSQL.

b) phpMyAdmin.a) Webmin.

[mysql@pablotorres root]$ mysql

6.1. OPCIONES.

En lugar de Linux hay paquetes para windows que incluyen: servidor apache, módulo php, mysql y phpMyAdmin.

La gestión de Mysql se puede hacer por medio de diversos programas. Para ello se requiere ser usuario root o con privilegios de superusuario.


a) Línea de comandos MYSQL. b) phpMyAdmin.

c) Webmin.

[mysql@pablotorres root]$ mysqlmysql> create database paginaweb;

6.2. CREAR UNA BASES DE DATOS MYSQL.

Para respaldar la base de datos se emplea la orden MYSQLDUMP o por medio de Webmin o phpMyAdmin.

Ejemplo 24: Crear la base de datos paginaweb.

Se emplea la cláusula Create Database para crear una base de datos, o por medio de un programa como Webmin o phpMyAdmin.


c) Línea de comandos MYSQL.

b) phpMyAdmin.a) Webmin.

[mysql@pablotorres root]$ mysqlmysql> GRANT all privileges ON paginaweb.contactos TO ventas@localhost IDENTIFIED BY '*****';

6.3. CREAR UN USUARIO MYSQL CON PRIVILEGIOS.

Ejemplo 25: Otorgar todos los privilegios sobre la tabla contactos de la base de datos paginaweb al usuario ventas conectado desde el servidor local (localhost).

Se emplea la cláusula Grant para crear usuarios y otorgar privilegios.

7. FUNCIONES BÁSICAS DE MYSQL EN PHP.SQL BAJO EL ENTORNO PHP/MYSQL

<? $basededatos="paginaweb"; $usuario="ventas";$clave="****"; $servidor="localhost";$consulta = "CREATE TABLE mensajes (Nombre varchar(50), Email varchar(50), Comentario varchar(120))";$conec = @mysql_connect($servidor,$usuario,$clave);if (!$conec){ print "<h5>Error de conección</h5>"; }else{ @mysql_select_db($basededatos); @mysql_query ($consulta); $ID = @mysql_insert_id(); if ($ID<>0) { print "<h5>>Error al crear la tabla.</h5>"; } else { print "<h5>La tabla ha sido creada.</h5>"; } mysql_close ($conec); }?>

7.1. CREAR UNA TABLA MYSQL CON PHP.

Ejemplo 26: Programa PHP que crea la tabla mensajes.

PHP permite conectarse a Mysql mediante un usuario y realizar operaciones tales como crear tablas, siempre y cuando se tenga los privilegios para ello.

mysql_connect: Conectar a Mysql.mysql_select_db: Elegir base de datos.mysql_query: Realizar orden SQL.mysql_insert_id: Número de operación.mysql_close: Terminar conección.

GRANT Create ON paginaweb TO ventas@localhost;


<? $basededatos="paginaweb"; $usuario="ventas"; $clave="****"; $servidor="localhost"; $consulta = "INSERT INTO mensajes VALUES ('$nombre', '$email', '$comentario')"; $conec = @mysql_connect($servidor,$usuario,$clave); if (!$conec) { print "<h5>Error en la conección al servidor</h5>"; } else { @mysql_select_db($basededatos); @mysql_query ($consulta); $ID = @mysql_insert_id(); if ($ID<>0) { print "<h5>Error al momento de guardar el comentario.</h5>"; } else { print "<h5>Se guardó el comentario con éxito.</h5>"; } @mysql_close ($conec); }?>

7.2. INSERTAR UN REGISTRO A UNA TABLA MYSQL CON PHP.

Formulario HTML

PHP puede recibir valores de por medio de un formulario HTML y luego emplearlos para realizar operaciones sobre tablas..Ejemplo 27: Programa PHP que guarda un comentario en la tabla mensajes.


<? $require(“../private/config.php”); $consulta = "DELETE mensajes.* FROM mensajes;)"; $conec = @mysql_connect($servidor,$usuario,$clave); if (!$conec){ print "<h5>Error en la conección al servidor</h5>"; }else{ @mysql_select_db($basededatos); @mysql_query($consulta); $ID = @mysql_insert_id(); if ($ID<>0) { print "<h5>Error al momento de borrar los comentarios.</h5>"; } else { print "<h5>Se han borrado todos los comentarios.</h5>"; } @mysql_close($conec);}?>

7.3. BORRAR REGISTROS DE UNA TABLA MYSQL CON PHP.

Por seguridad suele ubicarse los datos de conección en un archivo aparte y oculto. Este se incluye con la orden include o require.

Ejemplo 28: Programa PHP que borra todos los registros de la tabla mensajes.


<? $require(“../private/config.php”); $consulta = "UPDATE mensajes SET nombre='anónimo' WHERE mensajes.nombre IN (’grosería’,’me’,’yo’);"; $conec = @mysql_connect($servidor,$usuario,$clave); if (!$conec){ print "<h5>Error en la conección al servidor</h5>"; }else{ @mysql_select_db($basededatos); @mysql_query($consulta); $ID = @mysql_insert_id(); if ($ID<>0) { print "<h5>Error al momento de modificar los nombres.</h5>"; } else { print "<h5>Se han modificado todos los nombres.</h5>"; } @mysql_close($conec);}?>

7.4. MODIFICAR UN REGISTRO DE UNA TABLA MYSQL CON PHP.

Ejemplo 29: Programa PHP que modifica el nombre en los registros de la tabla mensajes que estén una lista de nombres no deseados.

La arroba precedida de una función inhibe el desplegado de mensajes de error en tiempo de ejecución.


$consulta = "SELECT * FROM mensajes";$resultado = @mysql_query($consulta) or die("La consulta falló: " . @mysql_error());echo "<table>\n";while ($linea = @mysql_fetch_array($resultado, MYSQL_ASSOC)) { echo "\t<tr>\n"; foreach ($linea as $valor_col) { echo "\t\t<td>$valor_col</td>\n"; } echo "\t</tr>\n";}echo "</table>\n";

7.5. CONSULTAR UNA TABLA MYSQL CON PHP.

Nombre Email ComentarioHugo [email protected] Muy bien !Paco [email protected] Hi !

<table><tr><td>Nombre</td><td>Email</td><td>Comentario</td></tr><tr><td>Hugo</td><td>[email protected]</td><td>Muy bien !</td></tr><tr><td>Paco</td><td>[email protected]</td><td>Hi !</td></tr></table>

Tabla HTML

Vista en un navegador WEB

Ejemplo 30: Código de un programa PHP que muestra todos los registros de la tabla mensaje y los desplega en una tabla HTML, se emplea la función mysql_fetch_array para obtener cada registro en un arreglo y despues mostrar cada campo en un ciclo foreach.

8. DATOS BINARIOS CON MYSQL Y PHP.SQL BAJO EL ENTORNO PHP/MYSQL

8.1. ESTRUCTURA DE UNA TABLA MYSQL CON UN DATO BINARIO.

CREATE TABLE datos_binarios (id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,descripcion CHAR(50),dato_binario LONGBLOB,nombre_archivo CHAR(50),tamano_archivo CHAR(50),tipo_archivo CHAR(50));

Ejemplo 31: Cláusula Create Table con un campo de tipo LONGBLOB.Un dato binario se almacena en una tabla con campos tipo BLOB o derivados.

Vista del programa phpMyAdmin con una tabla con un dato binario.


8.2. AGREGAR UN REGISTRO A LA TABLA MYSQL CON DATO BINARIO.

<?phpif ($enviado) { @mysql_connect("localhost","ventas","****"); @mysql_select_db("paginaweb"); $data = addslashes(fread(fopen($form_data, "r"), filesize($form_data))); $resultado=@mysql_query("INSERT INTO datos_binarios (descripcion, dato_binario, nombre_archivo, tamano_archivo, tipo_archivo ) VALUES ('$form_descripcion','$data','$form_data_name','$form_data_size','$form_data_type')"); $id= @mysql_insert_id(); print "<p>Este archivo tiene este número de identificación: <b>$id</b>"; @mysql_close();} else {?> <form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data"> Descripción del archivo:<br> <input type="text" name="form_descripcion" size="40"> <input type="hidden" name="MAX_FILE_SIZE" value="1000000"> <br>Archivo para agregar en la base de datos:<br> <input type="file" name="form_data" size="40"><br> <input type="submit" name="enviado" value="Enviar"> </form><?php } ?>

Formulario HTML

Ejemplo 32: PHP manipula el archivo enviado por el formulario HTML con funciones basicas del sistema de archivos tradicional.


8.3. CONSULTAR LA TABLA MYSQL CON UN DATO BINARIO.

<?php//consultar.phpif($id) { @mysql_connect("localhost","ventas","*****"); @mysql_select_db("paginaweb"); $consulta= "select dato_binario, tipo_archivo from datos_binarios where id=$id"; $resultado = @mysql_query($consulta); $dato = @mysql_result($resultado,0,"dato_binario"); $tipo = @mysql_result($resultado,0,"tipo_archivo"); Header( "Content-type: $tipo"); echo $dato;};?> Vista en un

navegador WEB

Ejemplo 33: Programa PHP que muestra un archivo binario almacenado en una tabla, el programa recibe el número de identificación del registro a consultar, ejemplo: consultar.php?id=1.

9. ALTERNATIVAS A MYSQL Y PHP.


Lenguajes Cliente Servidor

DBMS PERL C PHP JAVA ASPMYSQL X X X X XPOSTGRESQL X X X X XORACLE X X X X XSQLSERVER X X X X XACCESS X X X X X

JDBC: JavaDataBaseConnectivity

ODBC: ObjectDataBaseConnectivity

Funciones nativas

Libres (gratuitos)Ha crecido la aceptación en las comunidades de desarrolladores

No confundir los lenguajesde programación con los paquetes de desarrollo o los compiladores.

WINDOWSWINDOWSLINUXLINUX

10. GESTIONANDO ARCHIVOS EN UN SERVIDOR LINUX.


A) Cliente FTP Filezilla

B) FTP del navegador Internet Explorer D) Interfaz WEB WEB-FTP.

C) Interfaz WEB Usermin.

11. RESUMEN DE LA EXPOSICIÓN.


● Estándar SQL básico.

● Gestión de Mysql.

● Funciones básicas de Mysql en PHP.

● Datos binarios con Mysql y PHP.

● Alternativas a Mysql y PHP.

● Gestión de archivos en un servidor Linux.

12. BIBLIOGRAFÍA.●Florian Dittmer, “Binary Data + MySQL + PHP, How to Store Images Directly in the Sql Database”, Disponible en World Wide Web: <http://www.phpbuilder.com/columns/florian19991014.php3>, [Consulta: 19 Mayo de 2005].●Mehdi Achour, “Manual de PHP”, 2005, Disponible en World Wide Web: <http://de3.php.net/manual/es/>, [Consulta: 19 Mayo de 2005].●Paul DuBois, Stefan Hinz, Mike Hillyer, Jon Stephens, Russell Dyer, “MySQL Reference

Manual”, 2005, Disponible en World Wide Web: < http://dev.mysql.com/doc/mysql/en/>, [Consulta: 19 Mayo de 2005].

sql bajo el entorno php/mysql mi juan pablo torres herrera universidad autÓnoma de baja california...

Documents