Error inserting data into PHP PDO database

Question

I am trying to create a new register in the bank with the following code and returns the error: Insert value list does not match column list or os the record is created but blank, since the fields are NOT NULL, I’ve tried other options here on the forum and nothing.

I’ve researched, I’ve re-checked and nothing.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/twitter-bootstrap/2.3.2/css/bootstrap-combined.min.css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>

<div class="row">
  <div class="center-block form-novo-cad">
    <form method="post" action="novoCadSend.php">
      <div class="form-group">
        <label for="name">Nome:</label>
        <input type="text" class="form-control" id="name">
      </div>
      <div class="form-group">
        <label for="email">Email:</label>
        <input type="email" class="form-control" id="email">
      </div>
      <div class="form-group">
        <label for="telefone">Telefone:</label>
        <input type="text" class="form-control" id="telefone">
      </div>
      <div class="form-group">
        <label for="endereco">Endereço:</label>
        <input type="text" class="form-control" id="endereco">
      </div>
      <div class="col-xs-6  col-sm-6    col-md-6    col-lg-6" style="margin-left: -15px;">
        <div class="form-group">
          <label for="numero">Número:</label>
          <input type="text" class="form-control" id="numero">
        </div>
      </div><br><br><br><br>
      <div class="form-group">
        <label for="bairro">Bairro:</label>
        <input type="text" class="form-control" id="bairro">
      </div>
      <div class="form-group">
        <label for="cidade">Cidade:</label>
        <input type="text" class="form-control" id="cidade">
      </div>
      <button type="submit" class="btn btn-default">Criar Cadastro</button>
    </form>
  </div>
</div>

//novoCadSend.php
// inclui o arquivo de inicialização
require 'init.php';

// resgata variáveis do formulário
$name = isset( $_POST[ 'name' ] ) ? $_POST[ 'name' ] : '';
$email = isset( $_POST[ 'email' ] ) ? $_POST[ 'email' ] : '';
$telefone = isset( $_POST[ 'telefone' ] ) ? $_POST[ 'telefone' ] : '';
$endereco = isset( $_POST[ 'endereco' ] ) ? $_POST[ 'endereco' ] : '';
$numero = isset( $_POST[ 'numero' ] ) ? $_POST[ 'numero' ] : '';
$bairro = isset( $_POST[ 'bairro' ] ) ? $_POST[ 'bairro' ] : '';
$cidade = isset( $_POST[ 'cidade' ] ) ? $_POST[ 'cidade' ] : '';

$PDO = db_connect();
try {
  $PDO->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
  $stmt = $PDO->prepare( 'INSERT INTO users VALUES(:name, :email, :telefone, :endereco, :numero, :bairro, :cidade)' );
  $stmt->execute( array(
    //':id'   => $id,
    ':name' => $name,
    ':email' => $email,
    ':telefone' => $telefone,
    ':endereco' => $endereco,
    ':numero' => $numero,
    ':bairro' => $bairro,
    ':cidade' => $cidade,
  ) );
  echo $stmt->rowCount();
} catch ( PDOException $e ) {
  echo 'Error: ' . $e->getMessage();
}

Answer 1

Explaining:

As the error message already says, the problem is that you are not specifying the column names in the Insert.

To make the Insert in a table without specifying the columns, you must place the fields in the sequence that the table is defined, but as you want to ignore the column id (which is probably auto-incremented), you must specify the column names.

Take this example:

Table users

id  | nome

I can do the Insert as follows:

INSERT INTO usuarios 
VALUES (1, 'Fulano');

But if I don’t want to inform the id I’m obliged to do so:

INSERT INTO usuarios (nome)
VALUES ('Fulano');

---

Solving your problem:

If the name of your columns in the database is the same name as the variables, replace the row:

$stmt = $PDO->prepare( 'INSERT INTO users VALUES(:name, :email, :telefone, :endereco, :numero, :bairro, :cidade)' );

For:

$stmt = $PDO->prepare( 'INSERT INTO users (name, email, telefone, endereco, numero, bairro, cidade) VALUES(:name, :email, :telefone, :endereco, :numero, :bairro, :cidade)' );

Or as the rray spoke just unzip the id and put as null.