timestamp problem

Question

Hello, i have some scripts and in the database tabel 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.