timestamp issue

Question

I have some scripts and in the database table I have set the dato to int(25) and timestamp

I have a create topic script and then I create a topic it insert the date and time in the timestamp tabel (dato).

But somehow my comment script will not insert the time :S and its the same stuff I use :S.

Here is my script

    if(isset($_POST['opret_kommentar']))
{
 $indhold = $_POST['indhold'];
 $godkendt = "ja";

 $mysql = connect();
 $stmt = $mysql->prepare("INSERT INTO forum_kommentare (fk_forum_traad, brugernavn, indhold, dato, godkendt) VALUES (?,?,?,?,?)") or die($mysql->error);
 $stmt->bind_param('issis', $traadID, $_SESSION['username'], $indhold, $dato, $godkendt) or die($mysql->error);
 $stmt->execute();
 $stmt->close();

 $svar = mysqli_insert_id($mysql); 

 header("location: forum.traad.php?traadID=$traadID&kategoriID=$kategoriID&#$svar");

}

Here is my create topic script so you can see its the same stuff I use :S

    if(isset($_POST['send'])) {

 $kategoriID = $_GET['kategoriID'];
    $overskrift = $_POST['overskrift'];
    $indhold    = $_POST['indhold'];
 $godkendt   = "ja";

    $mysql = connect();
    $stmt = $mysql->prepare("INSERT INTO forum_traad (overskrift, indhold, fk_forum_kategori, brugernavn, dato, godkendt) VALUES (?,?,?,?,?,?)") or die($mysql->error);
 $stmt->bind_param('ssisis', $overskrift, $indhold, $kategoriID, $_SESSION['username'], $dato, $godkendt) or die($mysql->error);
 $stmt->execute();
 $stmt->close();

 $traadID = mysqli_insert_id($mysql);

  header("location: forum.traad.php?traadID=$traadID&kategoriID=$kategoriID");

}#Lukker isset send

Here is my SQL

CREATE TABLE IF NOT EXISTS forum_kommentare ( id int(11) NOT NULL AUTO_INCREMENT, fk_forum_traad int(11) NOT NULL,
brugernavn text NOT NULL, indhold mediumtext NOT NULL, dato timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, godkendt varchar(4) NOT NULL DEFAULT 'ja',
PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--

-- Data dump for tabellen forum_kommentare

---

--

-- Struktur-dump for tabellen forum_traad

CREATE TABLE IF NOT EXISTS forum_traad ( id int(11) NOT NULL AUTO_INCREMENT, overskrift text NOT NULL, indhold mediumtext NOT NULL, fk_forum_kategori int(11) NOT NULL, brugernavn text NOT NULL, dato timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, godkendt varchar(4) NOT NULL DEFAULT 'ja', PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- -- Data dump for tabellen forum_traad

Hope someone can help me :/

Answer 1

Apart from your code that need's to be proper formated so one can read it I strongly suggest you go with the MySQL date field and not a timestamp.

So change this query to this:

// notice that the date is inserted by MySQL with the NOW() operator
$stmt = $mysql->prepare("INSERT INTO forum_traad (overskrift, indhold, fk_forum_kategori, brugernavn, dato, godkendt) VALUES (?,?,?,?,NOW(),?)") or die($mysql->error);

Why should you use the MySQL date field instead of a timestamp?

DATETIME's range... '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

TIMESTAMP's range... '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'

Not only that but you also have a lot of build-in MYSQL functions to work with DATETIME's like INTERVAL which will allow you to do great things.

// easy pull all comments values from within a range
SELECT * FROM your_table
WHERE your_date_field = DATE_SUB('2001-01-01 12:12:12' + INTERVAL 15 DAYS;

If you do need to work with timestamps on PHP you can pull them real time on SQL like this:

// converts a DATETIME field real time to timestamps
SELECT UNIX_TIMESTAMP(your_date_field)
FROM your_table (...)
And when you pull them out just format them anyway you like using strftime. Example bellow.

// pull a date from MySQL and format them to a custom format
// %A -> full weekday | %B -> full month name | %d -> month day | %Y - year 1999
$sql = "SELECT title, name, UNIX_TIMESTAMP(date) as timestamp
        FROM your_table
        WHERE your_limits";
$result = mysql_fetch_assoc(mysql_query($sql));
echo strftime("%A, %B %d, %Y", $result['timestamp']);

I had already covered a question in the same topic so you can look on that thread for aditional responses.

Hope it helps!

Answer 2

A more cheap and cheerful solution would be...

$stmt = $mysql->prepare("INSERT INTO forum_traad (overskrift, indhold, fk_forum_kategori, brugernavn, godkendt) VALUES (?,?,?,?,?)") or die($mysql->error);

Just don't set the dato column. The first (leftmost) TIMESTAMP column of any table will automatically be set to the current date time when a row is inserted or updated.