Imagine a seguinte situação: você precisa fazer um sistema de cadastro de veículos para uma vitrine virtual.
O nosso sistema venderá “carros famosos”, por isso cada carro possui um nome (Batmóvel 1941, Tumbler, Herbie, Dick Vigarista, Penélope Charmosa…), um ano de fabricação (1941, 2008…) e um modelo (esporte, clássico, corrida, tanque de guerra…).
Um das primeiras coisas que vêm a nossa mente é modelar os atributos dessa entidade veículo.
mysql> DESCRIBE vehicles_wrong; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | year | int(4) | YES | | NULL | | | model | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
Tranquilo, certo? Um id para identificarmos cada veículo como único, o nome, ano e modelo. Mas o Batmóvel de 1941 tem acessórios diferentes do Tumbler de 2008 (o tanque de guerra do filme Batman Begins), certo?
E precisamos mostrar isso para nossos usuários, dizendo se o veículo possui ou não: ar condicionado, travas elétricas, tiro de canhão, moto acoplada para fuga… no caso do carro do Dick Vigarista, turbinas propulsoras!
O problema
Poderíamos adicionar estes itens à entidade veículos:
mysql> DESCRIBE vehicles_wrong; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | year | char(4) | YES | | NULL | | | model | varchar(30) | YES | | NULL | | | air_con | bit(1) | YES | | NULL | | | bluetooth | bit(1) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
Só que aí precisamos ainda colocar:
mysql> DESCRIBE vehicles_wrong; +------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | year | char(4) | YES | | NULL | | | model | varchar(30) | YES | | NULL | | | air_con | bit(1) | YES | | NULL | | | bluetooth | bit(1) | YES | | NULL | | | parking_sensors | bit(1) | YES | | NULL | | | escape_motorcyle | bit(1) | YES | | NULL | | | plasma_cannon | bit(1) | YES | | NULL | | | cruise_control | bit(1) | YES | | NULL | | | leather | bit(1) | YES | | NULL | | | metallic_paint | bit(1) | YES | | NULL | | | electric_locks | bit(1) | YES | | NULL | | | xenon_lights | bit(1) | YES | | NULL | | | nitro | bit(1) | YES | | NULL | | | wings | bit(1) | YES | | NULL | | +------------------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
E para cada opcional extra que precisamos cadastrar, iríamos adicionando uma nova coluna na tabela veículos, mesmo que o motor de propulsão seja um item específico do carro do Dick Vigarista, e os demais carros irão sempre deixar essa coluna em branco (como false). Isso, por si só, já mostra um grave problema de modelagem: não está escalável!
Ter que alterar o modelo e possuir diversas colunas sem valores no banco de dados é um exemplo de modelagem desnormalizada que devemos evitar.
A solução
Em vez de ficarmos criando novas colunas a cada opcional novo que precisamos listar, temos que modelar melhor nossa entidade e dividir a entidade veículos em duas: veículos e opcionais, veja:
mysql> DESCRIBE optional; DESCRIBE vehicle_optional; DESCRIBE vehicles; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) +-------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+-------+ | id_vehicle | int(11) | YES | | NULL | | | id_optional | int(11) | YES | | NULL | | +-------------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | year | char(4) | YES | | NULL | | | model | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
Assim, fica a cargo de uma terceira tabela de relacionamento o cruzamento da informação, sobre quais opcionais cada carro tem. E para cada novo opcional extra que tivermos que cadastrar no sistema, apenas adicionamos um linha (registro) na tabela optional, em vez de uma coluna na tabela veículos. Entendeu a diferença?
- Não precisamos alterar nada no banco de dados para disponibilizar um novo opcional.
- Não precisamos mexer nos nossos códigos backend.
- Nem na nossa query SQL!
O impacto de adicionar opcionais (mostrar mais checkboxes) é apenas cadastrar um novo registro na tabela.
mysql> SELECT * FROM optional; +----+------------------+ | id | name | +----+------------------+ | 1 | air_con | | 2 | bluetooth | | 3 | parking_sensors | | 4 | escape_motorcyle | | 5 | plasma_cannon | | 6 | cruise_control | | 7 | leather | | 8 | metallic_paint | | 9 | electric_locks | | 10 | xenon_lights | | 11 | nitro | | 12 | wings | +----+------------------+ 12 rows in set (0.00 sec)
Show me the code
Iremos escrever enfim os códigos para fazer esse CRUD com checkboxes.
Lembrando que o intuito do artigo não é ensinar programação orientada a objetos. Então irei deixar o script php o mais simples possível, para que você possa extrair e então montar o seu dentro da sua estrutura/framework.
Listando os opcionais do banco
A listagem dos itens é super trivial:
<?php
$query = $mysqli->query('SELECT id, name FROM optional');
while($row = $query->fetch_object()) {
?>
<label>
<input type="checkbox" name="optional[]" value="<?php echo $row->id; ?>" />
<?php echo $row->name; ?></label>
<?php
}
?>
Apenas conecto no MySQL com a lib mysqli e imprimo um checkbox para cada registro da tabela.
DESCRIBE TABLE optional_vehicle
Ainda não estamos preocupados com trazer os checkboxes marcados, pois precisamos entender como utilizar a tabela optional_vehicle. Ela faz um relacionamento 1:N (um veículo para N opcionais).
Para inserir nessa tabela, precisamos mais ou menos da seguinte string sql:
INSERT INTO vehicle_optional (id_vehicle, id_optional) VALUES (42, 1),(42 2)
Nessa query acima, estamos inserindo o opcional 1 e 2 para o veículo 42. Se quiséssemos inserir mais o opcional 5, bastaria:
INSERT INTO vehicle_optional (id_vehicle, id_optional) VALUES (42, 1),(42, 2),(42, 5)
Okay?
Fazer essa string é uma simples manipulação de array. Lembra que o name do checkbox é name=”optional[]” então, no php, irá chegar um array:
$_POST['optional'][0], $_POST['optional'][1], $_POST['optional'][2]
Visto isso, vamos montar o INSERT:
//optional insert
$values = [];
foreach($optionals AS $id_optional) {
$values[] = "({$id}, {$id_optional})";
}
$values = implode(',', $values);
$sql = "INSERT INTO vehicle_optional (id_vehicle, id_optional)
VALUES {$values}";
echo $sql;
$query = $mysqli->query($sql)or die($mysqli->error);
Simples, não? A saída do echo $sql, será a string que escrevi acima. Comecei pelos opcionais para matar logo a sua curiosidade, mas para deixar registrado o INSERT simples do veículo há apenas as colunas que são atributos da entidade veículo:
$sql = "INSERT INTO vehicles (id, name, year, model)
VALUES(NULL, '{$name}', '{$year}', '{$model}')";
echo $sql, '<br /><br />';
$query = $mysqli->query($sql)or die($mysqli->error);
$id = $mysqli->insert_id;//ultimo id inserido no banco
Os truques
As duas queries acima são bem triviais, e você já está mais do que acostumado com elas no seu dia a dia. Só existem dois truques no server-side para essa modelagem.
O primeiro é:
Delete tudo, depois insira novamente
O comportamento do input type=”checkbox” do html é enviar para o server-side apenas os checkboxes marcados. Então aqueles que não estiverem no estado checked não serão enviados.
Tente imaginar a lógica de atualizar os opcionais de um carro. Ele já possui alguns cadastrados no banco. No submit do formulário, você precisa remover os que não foram marcados, inserir os novos e com os que não alteraram não fazer nada. Complexo, não?
Um truque muito mais simples é remover tudo e depois inserir tudo o que vier.
DELETE FROM vehicle_optional WHERE id_vehicle = 42; INSERT INTO vehicle_optional (id_vehicle, id_optional) VALUES (42, 1),(42, 2),(42, 5);
Marque os checkboxes
Agora precisamos marcar os checkboxes na listagem durante a edição de um registro. O truque aqui é puramente SQL e se baseia em uma subquery.
SELECT `id`, `name`, (SELECT 'checked' FROM vehicle_optional WHERE id_vehicle = 1 AND id_optional = optional.id) AS `checked` FROM `optional`;
Pense no seguinte:
- Temos que listar todos os opcionais – fácil, já tinhamos feito isso.
- E dizer quem está marcado ou não – aqui que entra a subquery.
(SELECT 'checked' FROM vehicle_optional WHERE id_vehicle = 1 AND id_optional = optional.id)
ela vai trazer a string ‘checked’ apelidada como a coluna `checked` AS `checked` apenas nos registros do veiculo id = 1 que tiverem marcados. o/
mysql> SELECT `id`, `name`, (SELECT 'checked' FROM vehicle_optional WHERE id_vehicle = 1 AND id_optional = optional.id) AS `checked` FROM `optional`; +----+------------------+---------+ | id | name | checked | +----+------------------+---------+ | 1 | air_con | checked | | 2 | bluetooth | checked | | 3 | parking_sensors | NULL | | 4 | escape_motorcyle | NULL | | 5 | plasma_cannon | NULL | | 6 | cruise_control | checked | | 7 | leather | NULL | | 8 | metallic_paint | NULL | | 9 | electric_locks | NULL | | 10 | xenon_lights | NULL | | 11 | nitro | NULL | | 12 | wings | NULL | +----+------------------+---------+ 12 rows in set (0.00 sec)
E então vamos simplesmente imprimir isto no nosso input:
<input type="checkbox" name="optional[]" value="<?php echo $row->id; ?>" <?php echo $row->checked; ?>/>
Mágica.
O código está disponível no GitHub.
De 0 a 10, o quanto você recomendaria este artigo para um amigo?