Inserting data in sql after validation

Question

Inserting data in sql after validation

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

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

What is the best algorithm to handle this situation?

Follow the database tables:

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')
);

mysql

asked by anonymous 04.02.2018 / 20:57

2 answers

0

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

When you bring this data to the form, just bring the names and 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>

04.02.2018 / 21:25

Set up style sheet with PHP Get the selected item in iView's TreeView

score 1 · Accepted Answer

On the side of the database what maintains the consistency of the relations are constraints of type 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);

The KEYS you added to your table are indexes only.

Foreign Keys do not work on tables that use the storage engine MyISAM , being recommended to use 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 you need to change the engine :

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

If you are using InnoDB, the original two% of% are unnecessary since MySQL indexes Foreign Keys automatically.