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

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

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());

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

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());

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.

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

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

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.

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

I guess it means that you can store strtotime(mysql_real_escape_string($date)) value in INT field

'".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.

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