Hoje vou mostrar uma Function para converter o nome dos produtos, categorias, páginas e demais seções de um site em “nomes” para serem usados na url amigável do seu site.

Para quem não saber, url amigável é utilizado para deixar as url’s de um site mais “bonitas“. Exemplo: temos um produto TÊNIS, sem url amigável o seu sistema provavelmente iria ter como url algo como (http://www.seusite.com.br/index.php?product_id=123) que iria indicar o ID do produto para que você possa encontrar qual produto exibir na tela do usuário. Usando url amigável você transforma a url em algo que diz um pouco mais sobre o produto (http://www.seusite.com.br/produto/tenis) dessa forma conseguimos deixar uma url mais facil de decorar, entender e divulgar, sem contar que melhora a indexação do seu site em SEO(Search Engine Optimization), pois da mesma forma que fica mais fácil para um usuário entender do que se trata a url, também fica mais fácil para o Google ou qualquer outro sistema de busca categorizar as suas páginas.

Existem várias maneiras de fazer url amigável, a maneira que utilizo é guardando no banco de dados o nome que desejo para o produto como url. Então se o nome do produto é TÊNIS MARCA X PRETO no campo Url Amigável eu preencho tenis_marca_x_preto que será utilizado na url. No meu sistema eu deixo esse campo a ser preenchido pelo Cliente(olha o perigo rss) na própria adminstração do produto, porém como se é de imaginar, o cliente não tem visão de que esse é um campo importante e acaba deixando em branco ou pior, quando travo como um campo obrigatório e valido o campo para não permitir espaços, acentos e outros caracteres especiais o cliente simplesmente não consegue cadastrar o produto, pois não consegue gerar uma url válida.

Sendo assim resolvi criar uma FUNCTION em MySql que converta automaticamente o nome do produto, categoria, etc em uma url amigável válida. Também criei uma TRIGGER que valida se o usuário preencheu o campo, caso não tenha preenchido a trigger dispara a function convertendo o nome e gravando o campo url amigável.

Bom chega de histórinhas e vamos ao que interessa, como criar a tal função e o tal gatilho.

A FUCNTION funciona de uma maneira bem simples, basicamente troco os caracteres indesejádos por caracteres válidos para uma url e retorno o nome já convertido para ser usado.

CREATE FUNCTION urlAmigavel(name VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
	DECLARE campo VARCHAR(255);
	SET campo = TRIM(UCASE(name));
	SET campo = REPLACE(campo ,'Á','A');
	SET campo = REPLACE(campo ,'À','A');
	SET campo = REPLACE(campo ,'Ã','A');
	SET campo = REPLACE(campo ,'Â','A');
	SET campo = REPLACE(campo ,'É','E');
	SET campo = REPLACE(campo ,'È','E');
	SET campo = REPLACE(campo ,'Ê','E');
	SET campo = REPLACE(campo ,'Í','I');
	SET campo = REPLACE(campo ,'Ì','I');
	SET campo = REPLACE(campo ,'Î','I');
	SET campo = REPLACE(campo ,'Ó','O');
	SET campo = REPLACE(campo ,'Ò','O');
	SET campo = REPLACE(campo ,'Ô','O');
	SET campo = REPLACE(campo ,'Õ','O');
	SET campo = REPLACE(campo ,'Ú','U');
	SET campo = REPLACE(campo ,'Ù','U');
	SET campo = REPLACE(campo ,'Û','U');
	SET campo = REPLACE(campo ,'Ü','U');
	SET campo = REPLACE(campo ,'Ç','C');
	SET campo = REPLACE(campo ,'  ','_');
	SET campo = REPLACE(campo ,' ','_');
	SET campo = REPLACE(campo ,'+','');
	SET campo = REPLACE(campo ,'_-_','-');
	SET campo = REPLACE(campo ,'/','');
	SET campo = REPLACE(campo ,'\\','');
	SET campo = REPLACE(campo ,'?','');
	SET campo = REPLACE(campo ,'&','');
	SET campo = REPLACE(campo ,'__','_');
	RETURN LCASE(campo);
END;

OBS: Caso queria alterar a FUNCTION depois de já ter criado ela no seu banco de dados, é necessário apagar a função e criar novamente. O comando para apagar seria o (DROP FUNCTION `nomeDaFuncao`;)

Após criada a função podemos testar, executando a função na tabela de produtos e no campo nome, assim é possível visualizar como vai ficar os nomes antes de atualizar.

SELECT urlAmigavel(campoNomeProduto) FROM tabelaProduto

Veja que o retorno desta query é o nome do produto já retirando os espaços, acentos e caracteres especiais. Neste exemplo coloquei os caracteres que achei necessário, caso tenha necessidade de substituir mais algum outro caracter que não conste nessa lista, você pode adicionar seguindo como exemplo os outros caracteres que foram tratados na função.

No meu caso o data base do cliente já estáva com vários produtos cadastrados então tive que atualizar o campo url_amigavel do meu banco com o nome dos produtos antes de gerar a trigger que irá fazer isso automaticamente. Para atualizar todos os registro a sintaxe fica mais ou menos como mostrado abaixo, bastando trocar pelo nome dos seus campos e tabelas.

UPDATE tabelaProduto SET campo_url_amigavel = urlAmigavel(campo_nome_produto) WHERE campo_url_amigavel IS NULL

Dessa forma iremos atualizar os produtos já existentes. No meu caso as url’s ficam separadas em uma tabela e o nome do produto fica em outra tabela, mas por essa query vocês podem entender o funcionamento e adaptar para as suas necessidades.

Agora vamos criar a TRIGGER no evento BEFORE INSERT, ou seja, toda vez que um novo produto for cadastrado.

DELIMITER $$
CREATE TRIGGER trgGeraUrlAmigavel BEFORE INSERT ON tabela_produto
FOR EACH ROW
BEGIN
	IF(NEW.campo_url_amigavel IS NULL) THEN
		SET NEW.campo_url_amigavel = urlAmigavel(NEW.campo_nome_produto);
	END IF;
END$$

Conforme pode ser visto na trigger, é validado se o campo veio nulo, caso sim, insere o retorno da função para ser inserido como valor para a url amigável.

Espero que tenham gostado do post, qualquer sugestão, dúvida ou até crítica sobre o conteúdo, fiquem a vontade para comentar, a medida do possível vou respondendo os comentário. Toda contribuição é válida.

Caso tenha uma sugestão para novos posts sobre MySql, ORACLE, PHP, CSS, Ajax ou Jquery por favor mandem e-mail para blog@roberson.com.br que se eu tiver domínio do assunto em questão, com certeza irei criar um post sobre, e se não tiver domínio sobre o assunto, provavelmente vou estudar sobre e postar meus comentários, quem saber até um post mais complexo.

Até o próximo post.

Olá galera, dando continuidade à série de validação de dados. Agora vou falar como criar a validação de CPF usando um gatilho(trigger) e uma função(function) em MySQL. Para esse post eu usei o script de validação de CPF deste artigo aqui escrito pelo Cristiano Martins Alves, no artigo do Cristiano ele mostra uma função para validação em SQL Server, eu apenas adaptei o código dele para que rode em MySQL, as mudanças foram poucas, quase todas foram em função da diferença de sintaxe(ponto e virgula e loops), da função CONVERT para CAST e da função LEN para LENGTH. Depois das alterações o código ficou da seguinte maneira.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `validarCpf`(CPF CHAR(11)) RETURNS double
BEGIN
	DECLARE INDICE INT;
	DECLARE SOMA INT;
	DECLARE DIG1 INT;
	DECLARE DIG2 INT;
	DECLARE CPF_TEMP VARCHAR(11);
	DECLARE DIGITOS_IGUAIS CHAR(1);
	DECLARE RESULTADO CHAR(1);

	SET RESULTADO = FALSE;

	/*
	Verificando se os dígitos são iguais
	A Principio CPF com todos o números iguais são Inválidos
	apesar de validar o Calculo do digito verificado
	EX: O CPF 00000000000 é inválido, mas pelo calculo
	Validaria
	*/

	SET CPF_TEMP = SUBSTRING(CPF,1,1);

	SET INDICE = 1;
	SET DIGITOS_IGUAIS = 'S';

	WHILE (INDICE <= 11) DO
		IF (SUBSTRING(CPF,INDICE,1) <> CPF_TEMP) Then
			SET DIGITOS_IGUAIS = 'N';
		END IF;
		SET INDICE = INDICE + 1;
	END WHILE;

	/*Caso os dígitos não sejam todos iguais Começo o calculo do dígitos*/
	IF (DIGITOS_IGUAIS = 'N') THEN
		/*Cálculo do 1º dígito*/
		SET SOMA = 0;
		SET INDICE = 1;
		WHILE (INDICE <= 9) DO 			SET Soma = Soma + CAST(SUBSTRING(CPF,INDICE,1) AS UNSIGNED) * (11 - INDICE); 			SET INDICE = INDICE + 1; 		END WHILE; 		SET DIG1 = 11 - (SOMA % 11); 		IF (DIG1 > 9) THEN
			SET DIG1 = 0;
		END IF;

		-- Cálculo do 2º dígito }
		SET SOMA = 0;
		SET INDICE = 1;
		WHILE (INDICE <= 10) DO 			SET Soma = Soma + CAST(SUBSTRING(CPF,INDICE,1) AS UNSIGNED) * (12 - INDICE); 			SET INDICE = INDICE + 1; 		END WHILE; 		SET DIG2 = 11 - (SOMA % 11); 		IF DIG2 > 9 THEN
			SET DIG2 = 0;
		END IF;

		-- Validando
		IF (DIG1 = SUBSTRING(CPF,LENGTH(CPF)-1,1)) AND (DIG2 = SUBSTRING(CPF,LENGTH(CPF),1)) THEN
			SET RESULTADO = TRUE;
		ELSE
			SET RESULTADO = FALSE;
		END IF;

	END IF;
	RETURN RESULTADO;
END

Executando esse código naquela nossa base de testes (download e leitura da parte 01) a função validaCpf() vai ser criada no seu banco de dados, para executar a função basta executar um SELECT normal chamando a função validaCpf(), como no exemplo abaixo.

SELECT validarCpf('02452345798') FROM dual

Nesse caso irá retornar 0(zero) que corresponde a false/falso, caso você coloque um cpf válido no teste ele irá retornar 1(um) que corresponde a true/verdadeiro. Sendo assim agora basta colocarmos na nossa trigger essa chamada para a validação.

-- Trigger DDL Statements
DELIMITER $$

USE `testes_roberson`$$CREATE DEFINER=`root`@`localhost` TRIGGER trgValidacaoEmail BEFORE INSERT ON clientes
FOR EACH ROW BEGIN
    -- Declaro variável para identificar se está válido ou não
    DECLARE email DOUBLE DEFAULT FALSE;
    DECLARE cpf DOUBLE DEFAULT FALSE;

    -- Verificar se e-mail é válido
	IF (NOT EXISTS (SELECT NULL FROM Clientes WHERE  NEW.Email REGEXP '^[a-zA-Z0-9]{1}([\._a-zA-Z0-9-]+)(\.[_a-zA-Z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+){1,3}$')) THEN
        -- Caso seja inválido atribuo true para a variável email
        SET email = TRUE;
	END IF;

	IF ((SELECT validarCpf(NEW.Cpf) FROM dual) = 0 ) THEN
        -- Caso seja inválido atribuo true para a variável cpf
		SET cpf = TRUE;
	END IF;

    -- Verifico se as duas variaveis (email e cpf) estão como true, e mostro o erro
	IF (email && cpf) THEN
        SELECT 0 FROM `Não foi possível inserir os dados! E-mail e CPF inválidos`INTO @error;
    END IF;
    -- Verifico se a variável email está como true, e mostro o erro
    IF (email) THEN
        SELECT 0 FROM `Não foi possível inserir os dados! E-mail inválido`INTO @error;
    END IF;
    -- Verifico se a variável cpf está como true, e mostro o erro
    IF (cpf) THEN
        SELECT 0 FROM `Não foi possível inserir os dados! CPF inválido`INTO @error;
	END IF;
END$$

Criando essa trigger ou alterando a existente do exemplo anterior, a trigger será disparada todas as vezes que algum dado for ser inserido no banco e verificará tanto e-mail quanto CPF. Eu gostaria de ter feito esse exemplo de uma maneira um pouco diferente, queria armazenar em uma variável a string com o erro a cada verificação, e ao final concatenar elas e exibir o erro, porem não consegui que o CONCAT funciona-se dentro da clausula FROM. Caso alguem consiga algo do tipo, por favor me envie que eu atualizo aqui no script, e eu também vou continuar tentando fazer essa alteração.

O exemplo php da parte 01 continua funcionando da mesma maneira, para testarmos basta mudar no insert para um cpf inválido. Quando o E-mail e/ou CPF forem inválidos o erro será mostrado.

Espero ter contribuido com algo, sugestões, criticas ou elogios são muito bem aceitos. Espero que aproveitem e sucesso a todos.

Veja também:
Validando dados inseridos no banco MySql – Parte 01