...

View Full Version : Resolved trying to get data format for mysql



low tech
11-05-2010, 12:59 PM
Hi all

I want to get the data into my DB. I can't get it right.

I have this
$date = $_POST['payment_date'];

expected date format put into $date
00:31:02 Nov 03 2010 PDT

I was kindly offered this

date("Y-m-d H:i:s", strtotime('00:51:02 Nov 03, 2010 PDT'))

oould someone plaese show me how to insert this date format into mysql_query please. I'm tired and can't get it.

AND what data type should I choose im mysql DATE. DATETIME, TIMESTAMP, TIME, YEAR??? something else??

mysql_query("INSERT INTO users (date) VALUES('" ?????? "' ) ") or die(mysql_error());


LT

poyzn
11-05-2010, 01:06 PM
Hello again :)

if in DATE format:


mysql_query("INSERT INTO users date_field VALUES('" . date("Y-m-d", strtotime('00:51:02 Nov 03, 2010 PDT')) . "') ") or die(mysql_error());


if in DATETIME:


mysql_query("INSERT INTO users datetime_field VALUES('" . date("Y-m-d H:i:s", strtotime('00:51:02 Nov 03, 2010 PDT')) . "') ") or die(mysql_error());


if TIMESTAMP
in TIMESTAMP:


mysql_query("INSERT INTO users timestamp_field VALUES('" . date("YmdHis", strtotime('00:51:02 Nov 03, 2010 PDT')) . "') ") or die(mysql_error());



preferred DATETIME or TIMESTAMP

MattF
11-05-2010, 01:14 PM
Integer column type, and store it as a unix timestamp.



mysql_query("INSERT INTO users date_field (date) VALUES(".strtotime('00:51:02 Nov 03, 2010 PDT').")") or die(mysql_error());

low tech
11-05-2010, 02:29 PM
Thank you poyzn & MattF

To poyzn --

does this grab the contents of my $date variable? I'm still a littel confused -- sorry


datetime_field VALUES('" . date("Y-m-d H:i:s", strtotime('00:51:02 Nov 03, 2010 PDT')) . "') ") or die(mysql_error());

to MattF
so I choose type integer BUT store it using the code above --- is that what you mean?


just in case it helps;
this is exactly what I have so far minus the date variable which I'm trying to get.

I'm building it bit by bit to try to get it right.


mysql_query("INSERT INTO users (user_email, pwd, mc_gross, txn_id, country, approved) VALUES('". mysql_real_escape_string($user_email) ."', '".md5($pwd)."', '". mysql_real_escape_string($mc_gross) ."', '". mysql_real_escape_string($txn_id) ."', '". mysql_real_escape_string($country) ."', '". $approved ."') ") or die(mysql_error());

LT

poyzn
11-05-2010, 02:38 PM
ok. set your date field to DATETIME format, copy, paste and try to run next script



$full_name = $_POST['last_name'];
$user_name = $_POST['first_name'];
$address = $_POST['address_street'];
$mc_gross = $_POST['mc_gross']; //integer
$country = $_POST['address_country_code'];
$txn_id = $_POST['txn_id']; //not sure--> 6JR189569R234043C
$date = $_POST['payment_date']; //inot sure--> 00:31:02 Nov 03 2010 PDT ??
$user_email = $_POST['payer_email'];
$pwd = mt_rand(1000, 9999); //integer
$approved = 1; //integer

mysql_query("INSERT INTO users (full_name, user_name, user_email, pwd, mc_gross, txn_id, address, country, `date`, approved)
VALUES('". mysql_real_escape_string($full_name) ."', '". mysql_real_escape_string($user_name) ."', '". mysql_real_escape_string($user_email) ."', '". md5($pwd) ."', '". mysql_real_escape_string($mc_gross) ."', '". mysql_real_escape_string($txn_id) ."', '". mysql_real_escape_string($address) ."', '". mysql_real_escape_string($country) ."', '".date("Y-m-d H:i:s", strtotime(mysql_real_escape_string($date)))."', '". mysql_real_escape_string($approved) ."' ) ") or die(mysql_error());

low tech
11-05-2010, 02:53 PM
Hi Poyzn


Magic that is:-)

Yep that done the trick ---- gets entered as 2010-11-05 07:49:11 fantastic

now I can move on and try to get other bits done

Thanks for all the help today

really appreciated

LT
thanks to MattF too.

poyzn
11-05-2010, 03:00 PM
note that you've used mysql reserved word "date" as a column name.
next time please use date in back quotes:

`date`
or just rename the column

low tech
11-05-2010, 04:04 PM
Hi poyzn

AH good point --- missed that one ---

kick myself cos I know that is a no-no

will remane pay_date

thanks

LT
i'll tackle the integer feilds tomorrow after I've read the link you posted earlier

MattF
11-05-2010, 04:11 PM
to MattF
so I choose type integer BUT store it using the code above --- is that what you mean?

Yes. You will be storing an integer, not a formatted date, so that's all the column type needs to be. As to why, storing a preformatted date is fine if it takes your fancy. However, you have far more ease when coding without arsing around with conversions when using the unixtime format.

low tech
11-06-2010, 02:10 AM
Thanks MattF

very helpful info --- i'm new in PHP so everything is good

I am using Poyzn's solution post #5. it works perfect

I'll try to experiment and understand what you mean by this


you have far more ease when coding without arsing around with conversions when using the unixtime format.

What I don't get is the date (from paypal) will be in this format
00:31:02 Nov 03 2010 PDT

and thats not an integer as I understand it (so DB won't accept it direct)

and if I use poyznx code


'".date("Y-m-d H:i:s", strtotime(mysql_real_escape_string($date)))."',

isn't this converting it anyway??

Well this is all just for my understanding -- I'm a little puzzled but very interested

LT

poyzn
11-06-2010, 10:05 AM
I guess it means that you can store strtotime(mysql_real_escape_string($date)) value in INT field

MattF
11-06-2010, 03:29 PM
'".date("Y-m-d H:i:s", strtotime(mysql_real_escape_string($date)))."',

isn't this converting it anyway??

Well this is all just for my understanding -- I'm a little puzzled but very interested

Yup, that's converting, but to a certain format, whereas:



'".intval(strtotime($date))."'


would store it as unixtime format. For example, what if you're comparing it directly to an English formatted date which is d-m-Y, not Y-m-d? What happens if you're comparing to a partially formatted date, something like m-Y? What if you want to do a simple lesser/greater than check against the current time using time()? Formatted dates are fine, but you cannot do direct numeric comparisons on them and you never know how they're formatted, (other than the one you've personally set the format for), so you'll likely end up using something like the above on every date you do a comparison on every time you do a like to like comparison, whereas using unixtime format to store your date, most you'd end up doing is a quick strtotime($date) on the unknown format date. You're already in the correct format for a ($date == time()), ($date > time()), ($date < time()) type or comparison etc if you use the unixtime format, which for the approx. current time would appear as: 1289049745. ('06-11-2010 13:30:30' > (time()-86400)) just doesn't have the same effect.

low tech
11-07-2010, 02:33 AM
Hi

Wow thanks for that MattF ---

appreciate you taking the time to give a nicely detailed explanation.

really got me thinking. I'm so new that I hadn't thoght that I might need to comapre the date ---- but I now can see how it could be useful --- I will try to apply.

LT



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum