php - 使用 PDO 将时间插入 MySQL 数据库

Question

我正在研究 Pastebin 克隆之类的东西(从头开始，不是真正克隆 Pastebin，只是做一个替代方案)，并且我遇到了将时间插入数据库的问题。

<?php
require 'connection.php';

    $paste = $_POST['paste'];
    $title = $_POST['title'];

        //$sql = "INSERT INTO pasteinfo (title, paste) VALUES (:title, :paste)";

            $stmt = $con->prepare("INSERT INTO pasteinfo (title, paste) VALUES (:title, :paste)");

                echo "hi";
            $stmt->bindParam(':paste', $paste);
            $stmt->bindParam(':title', $title);
        $stmt->execute(); 
    echo "Pasted!";


    $pastetime = new DateTime();
    $timeQuery = $con->prepare("INSERT INTO pasteinfo (pastetime) VALUES (:pastetime)");
    $time->bindParam(':pastetime', $pastetime);
    $con->exec($timeQuery);

//$con = null;




?>

这就是 insert.php。我希望当用户“粘贴”他们的粘贴时，它会记录时间，然后在我的 viewpaste.php 上它将显示标题、粘贴和粘贴的时间。

这是怎么回事？

顺便说一句，忽略那个小 echo “hi”即可；扔在那里。它帮助我解决了很多问题，并且会继续这样做。

connection.php 来源:

<?php
    try {
    $con = new PDO('mysql:host=;dbname=;charset=utf8mb4','','');
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }
    catch (PDOException $ex){
    echo $ex->getMessage();return false;
    }

    function retrieve($query,$input) {
    global $con;
    $stmt = $con->prepare($query);
    $stmt->execute($input);
    $stmt->setFetchMode(PDO::FETCH_OBJ);   
    return $stmt;
}

@德鲁:

<?php
require 'connection.php';

    $paste = $_POST['paste'];
    $title = $_POST['title'];
    $timeQuery = "SELECT NOW()";

        //$sql = "INSERT INTO pasteinfo (title, paste) VALUES (:title, :paste)";

            $stmt = $con->prepare("INSERT INTO pasteinfo (title, paste, pastetime) VALUES (:title, :paste, :pastetime)");

                echo "hi";
            $stmt->bindParam(':paste', $paste);
            $stmt->bindParam(':title', $title);
            $stmt->bindParam(':pastetime', $timeQuery);
        $stmt->execute(); 
    echo "Pasted!";


    //$timeQuery = $con->prepare("INSERT pasteinfo(pastetime) SELECT NOW()");
    //$timeQuery->execute();

//$con = null;




?>

Answer 1

运行脚本一次后的架构和最终状态:

架构:

drop table if exists pasteinfo2;
CREATE TABLE pasteinfo2
(   ai INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    paste TEXT NOT NULL,
    pastetime DATETIME NOT NULL
);

PHP 脚本:

<?php
    // turn on error reporting, or wonder why nothing is happening at times
    error_reporting(E_ALL);
    ini_set("display_errors", 1);    

    // Begin Vault
    // credentials from a secure Vault, not hard-coded (so the below is just for testing)
    $servername="localhost";
    $dbname="so_gibberish";
    $username="nate";
    $password="cannonBall##)x";
    // End Vault

    try {
        $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        $paste="Four score and seven years ago, our fore....";
        $title="Gettysburg Address";

        $stmt = $pdo->prepare("INSERT INTO pasteinfo2 (title, paste, pastetime) VALUES (:title, :paste, now())");
        $stmt->bindParam(':paste', $paste);
        $stmt->bindParam(':title', $title);
        $stmt->execute();
        echo "Yo I am here<br>";
    } catch (PDOException $e) {
        echo 'pdo problemo: ' . $e->getMessage();   // dev not production code
        exit();
    }
?>