当value是日期变量时，如何更新mysql数据库中的列

Question

I got a problem when I updated the column parseDate.

I am using PHP to update the column value, but always fail.

I tried directly use query in MySQL, it's work.

The value is $parseDateText.

I tried

$parseDateText = '`'.$parseDateText.'`' ;

$parseDateText = '"'.$parseDateText.'"' ;

$parseDateText = "\"".$parseDateText."\"";

$parseDateText = "\'".$parseDateText."\'";

But all of $parseDateText showed error message: SQLSTATE[42000]: Syntax error or access violation: 1064.

The query is $sql1, please help me.

By the way, the column "parseDate" is VARCHAR(30), it should be OK no matter what I update the text.

<?php
$parseDateText = date ("Y-m-d");
$parseDateText = mysql_real_escape_string ($parseDateText);
$parseDateText = str_replace('"', "", $parseDateText);
$parseDateText = '`'.$parseDateText.'`' ;

// $test = printf "%s, $parseDateText";

$servername = "localhost";
$username   = "username";
$password   = "password";
$dbname     = "dbname";

try {
    $conn = new PDO(
        "mysql:host=$servername;dbname=$dbname", $username, $password
    );

    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql1 = "UPDATE TABLE 2015_02_20 SET parseDate = $parseDateText";

    $conn->exec($sql1);
    echo "New record created successfully";
    sleep (1);
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

Answer 1

This SQL seems strange:

UPDATE TABLE 2015_02_20 SET parseDate = $parseDateText

The SQL syntax for UPDATE in MySQL is UPDATE <table name> SET ...

So if your table name is, say MyTable, you should use:

UPDATE MyTable SET parseDate = '$parseDateText'

No need for quotes in the $parseDateText variable itself, i.e. it should be like 2015-02-02 rather than "2015-02-20" etc.