CODEWITHSUNDEEP
phpmysql
Atimis
Atimis

Reputation: 64

How to update a column in mysql database when value is a date variable

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;
?>

Upvotes: 0

Views: 161

Answers (3)

mhall
mhall

Reputation: 3701

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.

Upvotes: 0

Mokky Miah
Mokky Miah

Reputation: 1353

I would prepare the statement, instead of appending to it.

Try this: 

<?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 = ?";

    $conn->prepare($sql1, array($parseDateText));

    $conn->exec($sql1);
    echo "New record created successfully";
    sleep(1);

} catch (PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

Prepare the statement then execute:

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

$conn->prepare($sql1, array($parseDateText));

$conn->exec($sql1);

Upvotes: 1

Nishanth Matha
Nishanth Matha

Reputation: 6081

remove these lines:

$parseDateText = str_replace('"', "", $parseDateText);
$parseDateText = '`'.$parseDateText.'`' ;

and try:

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

Upvotes: 1

Related Questions