Error inserting data into table!

Question

In the student registration system has this logical scheme:

How can I enter the correct student data ?

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

    ?>

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)"