Hi need help with datetime in mysql!

Chrysanthus

I wount mysql to auto register the current Year, month , date , minute in the regdate field when a value insert into mysql?

or should the php handel the registration datetime? i have no ides what i shall do...

How should the syntax look? i read the manuel and i still dont understand Please Help

CREATE TABLE table_members

(id int(11) NOT NULL auto_increment,
firstname varchar(32) default NULL,
lastname varchar(32) default NULL,
birthdate numeric(8) default NULL,
gender ENUM('M','F') default NULL,
email varchar(32) default NULL UNIQUE,
nickname varchar(32) default NULL UNIQUE,
password varchar(32) default NULL,

som thing like this?
Regdate DATETIME(14) default NULL,

PRIMARY KEY (id));

Sxooter

I think the type to do this automagically in mysql is timestamp.

Kwitcherbitchen

just use

Regdate TIMESTAMP(14) default NULL,

then when you want to call the date time stuff back later on you can use

<?php

#get the date/time info

 $datetime = $row["Regdate"];
 $year = substr($datetime,0,4);
 $mon = substr($datetime,4,2);
 $day = substr($datetime,6,2);
 $hour = substr($datetime,8,2);
 $min = substr($datetime,10,2);
 $sec = substr($datetime,12,2);
 $orgdate = date("d.m.y @ g:i a",
                mktime($hour,$min,$sec,$mon,$day,$year));

echo $orgdate;

?>

Although obviously change the $orgdate attributes to whatever you want.

Chrysanthus

I havetwo question left why or can i get a entry in the mysql that look like this

2003-05-13-0816-55

Instead of

20030513081655

And the other thing is this code i need help with getting a echo date time and on on after <h1><b>User registered!</b></h1>

</head>

<h1><b>User registered!</b></h1>

<?php

If i wont to echo the time that the new user just been registerd in the the sight.

should it look like this then!

<?php

$datetime = $row["regdate"];
$year = substr($datetime,0,4);
$mon = substr($datetime,4,2);
$day = substr($datetime,6,2);
$hour = substr($datetime,8,2);
$min = substr($datetime,10,2);
$sec = substr($datetime,12,2);
$registerdate = date("d.m.y @ g:i a",
mktime($hour,$min,$sec,$mon,$day,$year));

echo $registerdate;

Firstname: <?php echo $POST["firstname"]; ?>
<br>
Lastname: <?php echo $POST["lastname"]; ?>
<br>
Gender: <?php echo $POST["gender"]; ?>
<br>
Birth: <?php echo $POST["birthdate"]; ?>
<p>
Nickname: <?php echo $POST["nickname"]; ?>
<br>
E-mail: <?php echo $POST["email"]; ?>

<?php

$firstname=$POST["firstname"];
$lastname=$POST["lastname"];
$birthdate=$POST["birthdate"];
$gender=$POST["gender"];
$email=$POST["email"];
$nickname=$POST["nickname"];
$password=$_POST["password"];

mysql_connect (localhost, root, *******);

   mysql_select_db (flameline);

        mysql_query ("INSERT INTO table_members (firstname, lastname, birthdate, gender, email, nickname, password) VALUES ('$firstname','$lastname','$birthdate','$gender','$email', '$nickname', '$password')");

</font>

</td>
</tr>

</table>

</body>

</html>

Sxooter

There's a function in mysql for extracing date parts, using that you can basically turn that timestamp into what you want:

select datepart(YEAR,field)||'-'||datepart(MONTH,field)||'-'

and so on.

Note that || is the SQL concatenation operator. I'm not sure MySQL uses the same one or not, so you might need to convert. also, I'm sure the datepart(YEAR is not the actual syntax, just a suggestion of what it should look like. The details are in the manual.

Chrysanthus

thanks i will test it!

Chrysanthus

No it works it should look like this and!! thanks 🙂 and all others for helping me...

<?php

$get_timestamp = mysql_query("select UNIX_TIMESTAMP(regdate) as regdate from table_members ORDER BY regdate desc limit 1");
while ($row = mysql_fetch_array($get_timestamp)) {
$reg = $row['regdate'];
echo 'You Registerd At' . date('Y-m-d', $reg) . ' Time ' . date('H:i', $reg) . '<br>';
}