[SOLVED] Inserting date into mysql?

[JP128]

How would I create a column in a table that adds the date into the field with the current time? And then how would I output it into a format like DD/MM/YYY HH:MM

[JasonLewis]

just create a table field as a 'datetime' then when inserted the date insert it as now().

ie:
[code=php:0]$query = mysql_query("INSERT INTO `table` (`datetime`) VALUES (now())");[/code]

[trq]

Then use the mysql DATE_FORMAT() function to format it however you like on the way back out.

[JP128]

ok, i thought that I had it solved... but I dont... When I pull the date from the database, and try to format it, it goes to like Dec 31, 1969 4:33:00 pm

[JP128]

does anyone know how to format something like $row['date']?

[BillyBoB]

this is wat happens to me ... :(

[JP128]

are you getting the same exact time tho? Billy?

[BillyBoB]

http://dreamshowstudios.net
thats wat im getting on home page not in shout idk why shout works ??

[JP128]

Ok, I saw your site... I wonder what is going on there.... but btw, your site is neat.

[artacus]

[quote author=JP128 link=topic=118934.msg486495#msg486495 date=1166335040]
ok, i thought that I had it solved... but I dont... When I pull the date from the database, and try to format it, it goes to like Dec 31, 1969 4:33:00 pm
[/quote]

Are you sure you've got a datetime field in the database and not just a time field? It sure looks like its only time.

[JP128]

`date` varchar(50) is what the row is.. what should I change it to?

[artacus]

datetime

[JP128]

the type instead of varchar? how would i do that? alter table... ?

[artacus]

Yeah. phpMyAdmin will let you do it easy enough. Otherwise, there are some ancient incantations you can use but it involves a gallon of bat's blood, and you only get about 2 oz from a typical bat...

[JP128]

lol
although I dont have phpmyadmin

[JP128]

Date Joined: December 31, 1969, 4:33 pm....

[JP128]

anyone know?

[trq]

Anyone know what? Use ALTER TABLE to change the field type.

[code]
ALTER TABLE tblname CHANGE `date` joined_date TIMESTAMP;
[/code]

PS; Date is a reserved word in mysql so you shouldn't really be using it as a filed name. The above query should change it to [i]joined_date[/i] as well as changing the data type.

[JP128]

[code]
$time = $row['joined_date'];
$newTime= date("m/d/Y h:i:s",$time); 

echo "Name: " . $row['firstname'] . " " . $row['lastname'] . "<br>";
echo "User: " . $row['username'] . "<br>";
echo "E-Mail: " . $row['email'] . "<br>";
echo "B-Day: " . $row['dob'] . "<br>";
echo "Status: " . $row['rank'] . "<br>";
echo "Date Joined: ". $newTime . "<br>";[/code]

it comes out like Date Joined: 12/31/1969 04:33:26

[trq]

Did you change the field type? You [i]may[/i] also need to fix your timestamp as they where broken by using the wrong filed type.

[JP128]

ALTER TABLE users CHANGE `date` joined_date TIMESTAMP;

mysql_query("insert into users values('','$firstname','$lastname','$email','$username',md5('$password'),'$dob','2',now())") or die(mysql_error());

        19 //ID
firstnametest //Firstname
lastnametest //Lastname
emailtest //email
usernametest //Username
81dc9bdb52d04dc20036dbd8313ed055 //MD5 Password
01-01-1900 //DoB
3 // Rank
2006-12-17 15:07:37 //Date Joined

[trq]

Problem solved then. Please mark the thread as such.

[JP128]

its not solved
that is what is in the database... the output is 12/31/1969 4:33:26

[trq]

Is there only one record in this database? Are you sure the results are for the record you showed the insert code for? The rank is also different.

PS; I may as well ask you now, but that dob field, is it varchar aswell?

[JP128]

there are a few in the table... I have it call those rows from the table where the username is = to $_SESSION['username']