index.php index.php - 1 month ago 14
MySQL Question

Inserting time into MySQL Database with PDO

I'm working on a Pastebin clone kind of a thing (from scratch, not literally cloning pastebin, just making an alternative) and I've ran into an issue inserting time into the database.

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




?>


So that's insert.php. I'm hoping that when a user 'pastes' their paste it will record time, and then on my viewpaste.php it will display the title, paste, and time the paste was made.

What's wrong with it?

By the way, just ignore the little echo "hi"; thrown in there. It's helped me troubleshoot a lot and continues to do so.

connection.php source:

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


@Drew:

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

Schema and end-state after running script once:

enter image description here

Schema:

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 script:

<?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="For 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();
    }
?>
Comments