PDO Insert Record

Question

PDO Insert Record

Navigation

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

0

I'm having issues with the insert tag in the database with PDO , below it follows tabela and script :

table calendar

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;

connection.php

<?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();
}

php mysql pdo

asked by anonymous 08.10.2017 / 15:48

2 answers

0

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

This:

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

should be this

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

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

Remarks:

The first SQL was also missing the allDay .
If startdate and enddate are dates , you should change the type to # / date / and-time-functions.html "> date in your table, never work with different types of what you need, as well as giving conversion and filter problems.
the id field as I understand it should be auto_increment , you have to modify this field in your table, otherwise your SQL will give another error, because id is mandatory like the other fields.

08.10.2017 / 16:03

Bring images with ajax Calculation in SELECT oracle

score 0 · Accepted Answer

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

SQL table 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;

In SQL insertion is missing the table name and column allday which is also set to non-null or is required, code should look like this:

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

You can learn more about INSERT in this link The SQL INSERT INTO Statement

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

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