Insert data into sql after validation

Asked

Viewed 73 times

1

I need to perform a validation where I can only enter the registration of a race in the database if the id driver and user are present in their respective seats.

What is the best algorithm to deal with this situation?

Follow the tables of the bank:

CREATE TABLE `corridas` (
  `id_corrida` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `m_id` int(11) NOT NULL,
  `p_id` int(11) NOT NULL,
  `c_valor` int(9) NOT NULL,
  PRIMARY KEY (`id_corrida`),
  KEY `fk_motorista` (`m_id`),
  KEY `fk_passageiros` (`p_id`)
);

CREATE TABLE `motorista` (
  `m_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `m_nome` varchar(255) NOT NULL,
  `m_nasc` datetime NOT NULL,
  `m_cpf` int(11) NOT NULL,
  `m_modeloCarro` varchar(255) DEFAULT NULL,
  `m_sexo` char(1) DEFAULT NULL,
  `m_status` char(1) DEFAULT NULL,
  PRIMARY KEY (`m_id`)
);

CREATE TABLE `passageiro` (
  `p_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `p_nome` varchar(255) NOT NULL,
  `p_nasc` datetime NOT NULL,
  `p_cpf` int(11) NOT NULL,
  `p_sexo` char(1) DEFAULT NULL,
  PRIMARY KEY (`p_id`)
);
  • Are you working with PHP? Wouldn’t be the ids in their respective tables?

  • I’m using php but as I’m studying I’ve never done anything like this

  • Edit your question and place the tables in the bank.

2 answers

1


On the bank side what maintains the consistency of the relationships are constraints of the kind Foreign Key (foreign key):

ALTER TABLE corridas ADD CONSTRAINT fk_motorista FOREIGN KEY (m_id) REFERENCES motorista(m_id);
ALTER TABLE corridas ADD CONSTRAINT fk_passageiro FOREIGN KEY (p_id) REFERENCES passageiro(p_id);

As KEYS that you added to your table are just indexes.

Foreign Keys do not work in tables that use the Storage engine Myisam, being recommended the use of Innodb.

To know what kind of engine your tables are using use the following query:

SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA IN ('corridas', 'motorista', 'passageiro');

If it is necessary to amend the engine:

ALTER TABLE passageiro ENGINE=InnoDB;
ALTER TABLE motorista ENGINE=InnoDB;
ALTER TABLE corridas ENGINE=InnoDB;

If you are using both Innodb KEYS originals are unnecessary since Mysql indexes Foreign Keys automatically.

0

You will not need to do this kind of validation, since you will list drivers and users and then select them, correct?

When you bring that data to the form, just bring the names and the ids of both. For example you can list each of them in their respective select.

<select name="id_motorista">
    <option value="1">Motorista 1</option>
    <option value="2">Motorista 2</option>
</select>

<select name="id_usuario">
        <option value="1">Usuario 1</option>
        <option value="2">Usuario 2</option>
</select>
  • Only the change in the layer of view is not sufficient to ensure data integrity. A malicious user can easily forge a request with others ids or even modify the form in question using tools such as Chrome Devtools or Firebug. It is important to check entries in the other layers.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.