CODEWITHSUNDEEP
phpmysqldatetime
Kohl
Kohl

Reputation: 87

using mysql NOW() function to update row in database not working in PHP query

I am trying to use the NOW() mysql function to update the datetime column for an inserted row. The datetime column is called 'transaction'. I tried finding a similar PHP function to mirror/mimic the datetime format but couldn't find one suitable.

 $purchase = query("INSERT INTO `Portfolio`(`id`, `symbol`, `shares`, `transaction`, `transType`) VALUES ((?),(?),(?),(?),(?)) ON DUPLICATE KEY UPDATE shares = shares + VALUES(shares)",$user,$symbol,$shs,NOW(),"BUY");

Upvotes: 0

Views: 2390

Answers (3)

Mr. Bland
Mr. Bland

Reputation: 21

$database = new database;
$now = $database->now();

class database {
...
public function now($query = "SELECT NOW();"){
        $sth = $this->connect->prepare($query);
        // execute
        if ($sth->execute()) {
            $result = $sth->fetch(PDO::FETCH_ASSOC);
            return $result["NOW()"];
        } else {
            echo "\nPDO::errorInfo():\n";
            print_r($sth->errorInfo());
            echo "\nQuery:\n";
            echo $query;
            echo "\nParam:\n";
        }
    }
}

Upvotes: 0

Rick James
Rick James

Reputation: 142356

The NOW() goes in place of one of the "?", not in the bind list.

If you also wanted to update that field in case the statement turns into an UPDATE, then you need it in the SET also.

Upvotes: 0

Marcin Nabiałek
Marcin Nabiałek

Reputation: 111859

You can use PHP date function:

date("Y-m-d H:i:s")

to put current time

or you can not bind the parameter:

$purchase = query("INSERT INTO `Portfolio`(`id`, `symbol`, `shares`, transaction`, `transType`) VALUES (?,?,?,NOW(),?) ON DUPLICATE KEY UPDATE shares = shares + VALUES(shares)",$user,$symbol,$shs,"BUY");

Upvotes: 2

Related Questions