Inserting time into MySQL Database with PDO

Question

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;




?>

why wouldnt you just do `select now()` vs `VALUES (:pastetime)`? Also what possible purpose is the table `pasteinfo` which appears useless with no other cols — Drew, Oct 25 '16 at 04:32
IF the datatype is datetime, you can use date("Y-m-d h:i:s") for date — vishwakarma09, Oct 25 '16 at 04:32
@Drew Wasn't really aware of select now(). Does it work with the DateTime format? — index.php, Oct 25 '16 at 04:46
See [Link](http://stackoverflow.com/questions/19843203) it's an interesting and often arduous task — Drew, Oct 25 '16 at 04:50
@Drew amd I using this right? o_O $timeQuery = $con->prepare("INSERT INTO pasteinfo (pastetime) SELECT NOW(),CURDATE(),CURTIME()"); — index.php, Oct 25 '16 at 04:51
@Drew Think I'm messing this up somewhere: $timeQuery = $con->prepare("INSERT pasteinfo(pastetime) SELECT NOW()"); $con->exec($timeQuery); — index.php, Oct 25 '16 at 04:55
con prepare returns a stmt. then it would be stmt ---->execute — Drew, Oct 25 '16 at 04:57
@Drew hmm the date works now but it's inserting two seperate entries. How can I combine the two executes so it doesn't do that? — index.php, Oct 25 '16 at 05:03
Oh I see what you are doing. You are doing 2 inserts to the same table. So that means you columns are nullable. — Drew, Oct 25 '16 at 05:10
Do just one insert. An insert inserts a row. No reason to do an `update` right after it (not that you are). Collect all your data and do 1 insert per row you want — Drew, Oct 25 '16 at 05:11
@Drew I don't really see how it's possible to combine the two inserts to make it more efficient. Should I do it on a separate page in include it like I did with connection.php or something? Or would maybe closing and reopening the connection do it? — index.php, Oct 25 '16 at 05:12
@Drew updated code in OP with something I came up with. Any idea why it doesn't work? I feel like it should. — index.php, Oct 25 '16 at 05:18
There, I added an answer. I will delete all my comments above as they were too long. See if the below is what you want. — Drew, Oct 25 '16 at 05:26

Drew · Accepted Answer · 2016-10-25T05:35:01.247

Schema and end-state after running script once:

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

no problem boss. Sorry about my misunderstanding. I thought you had two tables going not one. — Drew, Oct 25 '16 at 05:29

Inserting time into MySQL Database with PDO

1 Answers1