PDO Insert Record

Question

I’m in trouble to insert bank record with PDO, below follows the tabela and script:

Calendar table

CREATE TABLE `calendar` (
  `id` int(11) NOT NULL,
  `title` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `startdate` varchar(48) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `enddate` varchar(48) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `allDay` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  

---

php connection.

<?php
try {
    // Faz Conexão com o banco de dados
    $conectar = new PDO("mysql:host=localhost;port=3306;dbname=wsphp","root","");   
} catch (PDOException $e) {
// Caso ocorra algum erro com a conexção com o banco, exibe a mensagem
    echo 'Falha ao conectar com o banco de dados: ' . $e->getMessage(); 
}  

<?php

include_once "conexao.php";
try {
    $titulo = filter_var($_POST['titulo']); // Evita o sql injection
    $start = filter_var($_POST['start']); // Evita o sql injection
    $end = filter_var($_POST['end']); // Evita o sql injection
    $allDay = filter_var($_POST['allDay']);; // Evita o sql injection

    $sql = "INSERT INTO title, startdate, enddate VALUES (:title, :start, :end, :allDay)";
    $insert = $conectar->prepare($sql);
    $insert->bindParam(':title', $titulo); // Evita o SQL Injection
    $insert->bindParam(':start', $start);
    $insert->bindParam(':end', $end);
    $insert->bindParam(':allDay', $allDay);
    $conectar->beginTransaction();    
    $insert->execute();
    $conectar->commit();

    //header('location: index.php');
} catch (PDOException $e) {
    echo 'Erro: ' . $e->getMessage();
}  

Answer 1

In the structure of the table id is not set to auto increment and is set to NOT NULL this makes it mandatory at the time of insertion, ie it cannot be null. To fix this set the id as auto increment and how primary key, With this you will not need to pass it on insertion as it will be auto incremented by the bank itself. can see the more detailed reference on this link SQL AUTO INCREMENT Field

Table SQL would look like this

CREATE TABLE `calendar` (
  `id` int(11) INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `startdate` varchar(48) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `enddate` varchar(48) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `allDay` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;   

Already in SQL insert is missing the table name and allday column which is also set to not null or is required, code should be like:

INSERT INTO calendar(title, startdate, enddate, allDay)  VALUES (:title, :start, :end, :allDay);

you can learn more about INSERT at this link The SQL INSERT INTO Statement

In your code it seems that you had problems with the columns NOT NULL see this link to learn more about SQL NOT NULL Constraint

It would be a good practice to use the type date in the date columns, if you have difficulties handling php data you can use library with Carbon

Answer 2

^{If you have any problems, report the problem by putting this in your question, an error screen helps a lot to solve problems like this}

That:

INSERT INTO title, startdate, enddate VALUES (:title, :start, :end, :allDay)

that should be it

INSERT INTO calendar(title, startdate, enddate, allDay) 
             VALUES (:title, :start, :end, :allDay);

that is, the table name and the fields were not specified correctly SQL is in trouble.

Remarks:

• the first SQL it was missing to pass also the allDay.
• if startdate and enddate were dates, should change the type to date in your table, never work with different types of what you need, plus give problems in conversions and filter.
• the field id from what I could understand must be auto_increment, you have to modify this field in your table, if not your SQL will give another mistake, because, the id is mandatory as the other fields.

_References:

• Mysql Documentation
• 12.7 Date and Time Functions
• 3.6.9 Using AUTO_INCREMENT
• SQL