Error in date field

Discussion in 'MySQL' started by piropeator, May 21, 2016.

0
  1. #1
    In table1, fec_cta is date (all database using UTF-8).
    $fecha="2016-04-30";
$sql = "INSERT INTO table1 (cod, fec_cta, cant)
        SELECT codigo, ".$fecha.",cantidad
        FROM tabla2";
$sth = $BD->prepare($sql);
$sth->execute();
    PHP:
    What is wrong here?
    The fec_cta is empty after to run the query.
     
    Solved! View solution.
    piropeator, May 21, 2016 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    Oh, wow... There are so many things wrong with that statement... I'm on my phone, and really don't wanna try rectifying that on there, but... Wow. First of, you can't insert values like that. And, turn on php errors, that way you'll get a bit more info.
     
    PoPSiCLe, May 21, 2016 IP
  3. piropeator

    piropeator Well-Known Member

    Messages:
    194
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #3
    Let's start again: I have this simple form:
    <!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <title>Formulario</title>
</head>
<body>
    <form id="formulario" action="grabar.php" method="POST">
        <input type="text" name="codigo"/>
        <input type="text" name="nombre"/>
        <input type="date" name="fecha"/>
        <input type="submit" value="Enviar" />
    </form>
</body>
</html>
    HTML:
    And php file:
    <?php
class ConexionDB extends PDO {
  public function __construct () {
  try {  parent:: __construct('mysql:host=localhost;dbname=table;charset=utf8', 'root','key');
  parent:: setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  } catch (Exception $ex) { die ('Database is not exist');  }  }
  function __destruct(){
  }
}
  $BD = new ConexionDB();

  $sth = $BD->prepare("INSERT INTO tipos (codigo, nombre, fecha) VALUES (:codigo, :nombre, :fecha)");

  $codigo = $_POST['codigo'];
  $nombre = $_POST['nombre'];
  $fecha  = $_POST['fecha'];

  $sth->bindParam(':codigo', $codigo, PDO::PARAM_INT);
  $sth->bindParam(':nombre', $nombre, PDO::PARAM_STR);
  $sth->bindParam(':fecha', $fecha);
  $sth->execute();
?>
    PHP:
    I have some questions:
    1) The input type="date" show in the form this: mm/dd/yyyy. Can I change this for to show dd/mm/yyyy?
    2) The sentence where the date field is assigned, it needs a PDO :: PARAM for dates??
    3) The field in table is ok (yyyy/mm/dd), only in the view I need to show dd/mm/yyyy.
    My table is utf8_general_ci.
     
    piropeator, May 23, 2016 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #4
    Just looking at your first code snippet - here's what I'd do
    
$fecha="2016-04-30";
$sql = "INSERT INTO `table1` (`cod`, `fec_cta`, `cant`)
SELECT `codigo`, `".$fecha."`,`cantidad`
FROM `tabla2`";
var_dump($sql);
    Code (markup):
    and I'd expect to see dumped out 
    
INSERT INTO `table1` (`cod`, `fec_cta`, `cant`)
SELECT `codigo`, `2016-04-30`,`cantidad`
FROM `tabla2`
    Code (markup):
    but I can't see why you have the date as a column name

    If the date in your form is setup as d/m/y then you just need to fiddle with it before you save. I typically use code that looks something like this
    $bits = explode('/', $_POST['fecha']);
$fecha = "{$bits[2]}-{$bits[1]}-{$bits[0]}";
    Code (markup):
    There may be a more elegant way but this is readable later on when you come to edit your code.
     
    sarahk, May 23, 2016 IP
  5. piropeator

    piropeator Well-Known Member

    Messages:
    194
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #5
    And this expression why does not work : 
     echo date_format($_POST['fecha'],'%d/%m/%Y');
    PHP:
    the mistake error is 
    Warning: date_format() expects parameter 1 to be DateTime, string given in
    HTML:
     
    piropeator, May 24, 2016 IP
  6. #6
    And? It says right there in the error what the problem is.
    Do this instead:
    
$date = new DateTime($_POST['fecha']);
$date->format('d/m/Y');
    Code (markup):
     
    PoPSiCLe, May 24, 2016 IP
  7. piropeator

    piropeator Well-Known Member

    Messages:
    194
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    121
    #7
    Thanks. Now in my application I can to work with the date time of system and the date from the form.
     
    piropeator, May 24, 2016 IP