Error inserting data into table!

Asked

Viewed 118 times

0

In the student registration system has this logical scheme:

inserir a descrição da imagem aqui

How can I enter the correct student data ?

inserir a descrição da imagem aqui

SQL

-- Geração de Modelo físico
-- Sql ANSI 2003 - brModelo.



CREATE TABLE Aluno (
idaluno varchar(50) PRIMARY KEY auto_increment,
nomeAluno varchar(50),
matricula varchar(50)
);

CREATE TABLE Endereco (
idendereco varchar(50) PRIMARY KEY,
nomerua varchar(50),
cidade varchar(50),
idaluno varchar(50),
FOREIGN KEY(idaluno) REFERENCES Aluno (idaluno)
);

CREATE TABLE Turma (
idturma varchar(50) PRIMARY KEY,
codturma varchar(50),
nomeprofessor varchar(50),
quantidadealunos int,
idaluno varchar(50),
FOREIGN KEY(idaluno) REFERENCES Aluno (idaluno)
);

PHP code

<?php

        $NOME           ="";
        $MATRICULA      ="";
        $NOMERUA        ="";
        $CIDADE         ="";
        $CODTURMA       ="";
        $NOMEPROFESSOR  ="";
        #QUANTIDADE     ="";

     $query = "INSERT INTO aluno('NOME','MATRICULA') VALUES($NOME,$MATRICULA)";

    ?>
php mysql

  • What’s the mistake ?

  • The data is not being entered correctly

  • doubt this how to enter the student’s name and at the same time enter the data in the related tables: Address and Class ???

  • A question, why the tag Python ? This using PHP and Python ?

  • The system is in php.

  • Must do double Insert query ??

  • should do Query1(insert Tabela1: student) and query2 (Insert table2: address ) and query3(Tabela3:class)

  • See this tutorial: Table Relationship in Mysql

  • There is a way to insert data in, more than one table at a time ?

  • Mysql or Postgresql?

  • the database is Mysql

  • I can use INNER JOIN on Insert ??

Show 7 more comments

1 answer

1


First I took the liberty of altering its structure, like the field idaluno is AUTO INCREMENTO I think it’s best to work with the numeric type, so I switched to int:

CREATE TABLE Aluno (
  idaluno int PRIMARY KEY AUTO_INCREMENT,
  nomeAluno varchar(50),
  matricula varchar(50)
);

CREATE TABLE Endereco (
  idendereco int PRIMARY KEY AUTO_INCREMENT,
  nomerua varchar(50),
  cidade varchar(50),
  idaluno int,
  FOREIGN KEY(idaluno) REFERENCES Aluno (idaluno)
);

CREATE TABLE Turma (
  idturma int PRIMARY KEY AUTO_INCREMENT,
  codturma varchar(50),
  nomeprofessor varchar(50),
  quantidadealunos int,
  idaluno int,
  FOREIGN KEY(idaluno) REFERENCES Aluno (idaluno)
);

Now let’s get to the problem, can’t put the names of the fields between aspas simples, then your INSERT would look like this:

$query = "INSERT INTO Aluno (nomeAluno, matricula) VALUES('$NOME', '$MATRICULA')";

After that INSERT you need to retrieve the idaluno that was generated automatically:

$query = "SELECT @@IDENTITY AS id";

and then perform the INSERT in the other tables:

$query = "INSERT INTO Endereco (idendereco, nomerua, cidade, idaluno) VALUES('$NOMERUA', '$CIDADE', $IDALUNO)";
$query = "INSERT INTO Turma (codturma, nomeprofessor, quantidadealunos, idaluno VALUES ('$CODTURMA', '$NOMEPROFESSOR', $QUANTIDADE, $IDALUNO)"

  • I believe the user will use mysqli connection and so to recover the ID will be via mysqli_insert_id

  • correct. I’m sweating Mysql

  • SELECT LAST_INSERT_ID() INTO @ID; ??

  • $id = mysqli_insert_id($query); ??

Browser other questions tagged php mysql

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