Page 1 of 1

MySQL concatenation with curdate curtime?

Posted: Mon Jul 14, 2008 12:03 am
by JAB Creations
I'm not sure how to use concatenation in MySQL. I've tried the period and the plus symbol and neither seem to work. Below does not generate any error messages however it does not insert anything useful either...?

Code: Select all

$query = "INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', CURDATE() + CURTIME())";

Re: MySQL concatenation with curdate curtime?

Posted: Mon Jul 14, 2008 2:03 am
by jaoudestudios
$query = "INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', CURDATE() + CURTIME())";
Why not store the date with a php unix timestamp, then from that you can get any time and/or date format you want.

Instead of CURDATE() + CURTIME() just use time()

Resulting in...

Code: Select all

$query = "INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', ".time().")";
Then when you want to display the result in your php page just use...

Code: Select all

date("d m Y" , *UNIX TIMESTAMP FROM DB*)
You can change d m Y to any format you want, including time. The codes for all the options can be found on php.net

Re: MySQL concatenation with curdate curtime?

Posted: Mon Jul 14, 2008 2:15 am
by VladSun
I vote for using timestamps too, but formating it at DB layer.
If you don't want to use timestamps - http://dev.mysql.com/doc/refman/5.0/en/ ... nction_now

Re: MySQL concatenation with curdate curtime?

Posted: Mon Jul 14, 2008 2:17 am
by JAB Creations
I decided this would be a better format since essentially the MySQL documentation severely lacks any useful examples, tutorial sites are doing mostly useless/static crap, and I can later use PHP's explode function if I need to "clean" the format up.

Code: Select all

$date_time = date("Y:m:d:H:i:s");
$query = "INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', '$date_time')";

Re: MySQL concatenation with curdate curtime?

Posted: Mon Jul 14, 2008 2:18 am
by JAB Creations
Hi Vlad, I tried that stuff out and was never able to merge the date and time together in to a single row/column.

Re: MySQL concatenation with curdate curtime?

Posted: Mon Jul 14, 2008 2:19 am
by VladSun
JAB Creations wrote:... since essentially the MySQL documentation severely lacks any useful examples, tutorial sites are doing mostly useless/static crap ...


???

There are tons of them in the official manual
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

Re: MySQL concatenation with curdate curtime?

Posted: Mon Jul 14, 2008 2:19 am
by VladSun
JAB Creations wrote:Hi Vlad, I tried that stuff out and was never able to merge the date and time together in to a single row/column.
I don't believe it - give an example.
mysql> SELECT NOW();
-> '2007-12-15 23:50:26'

Re: MySQL concatenation with curdate curtime?

Posted: Mon Jul 14, 2008 2:29 am
by JAB Creations
Maybe it was the initial column width? I'm not sure...now works now though. :mrgreen: Thanks!

Re: MySQL concatenation with curdate curtime?

Posted: Tue Jul 15, 2008 9:18 pm
by Benjamin
Here are two ways to do it.

Code: Select all

 
INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', CONCAT(CURDATE(), ' ', CURTIME()))
INSERT INTO accounts (username, password, email, date_0) VALUES ('$username', '$password', '$email', NOW())
 

Re: MySQL concatenation with curdate curtime?

Posted: Wed Jul 16, 2008 2:17 am
by jaoudestudios
Instead of using the php unix timestamp
time()
sql has its own one...

Code: Select all

UNIX_TIMESTAMP()
Converting from unix timestamp in php is easy and well documented, but there is a similar way in sql

Code: Select all

FROM_UNIXTIME(unix timestamp, format)
Example...

Code: Select all

FROM_UNIXTIME(unix timestamp, '%d %m %Y')