Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    838
    Thanks
    172
    Thanked 90 Times in 90 Posts

    trying to get data format for mysql

    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
    Last edited by low tech; 11-07-2010 at 04:44 AM.

  • #2
    Regular Coder poyzn's Avatar
    Join Date
    Nov 2010
    Posts
    266
    Thanks
    2
    Thanked 61 Times in 61 Posts
    Hello again

    if in DATE format:
    PHP Code:
    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:
    PHP Code:
    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:
    PHP Code:
    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
    Last edited by poyzn; 11-05-2010 at 12:10 PM.

  • #3
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Integer column type, and store it as a unix timestamp.

    Code:
    mysql_query("INSERT INTO users date_field (date) VALUES(".strtotime('00:51:02 Nov 03, 2010 PDT').")") or die(mysql_error());
    Last edited by MattF; 11-05-2010 at 12:17 PM.

  • #4
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    838
    Thanks
    172
    Thanked 90 Times in 90 Posts
    Thank you poyzn & MattF

    To poyzn --

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

    PHP Code:
    datetime_field VALUES('" . date("Y-m-d H:i:s", strtotime('00:51:02 Nov 032010 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.

    PHP Code:
    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
    Last edited by low tech; 11-05-2010 at 01:32 PM.

  • #5
    Regular Coder poyzn's Avatar
    Join Date
    Nov 2010
    Posts
    266
    Thanks
    2
    Thanked 61 Times in 61 Posts
    ok. set your date field to DATETIME format, copy, paste and try to run next script

    PHP Code:
    $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(10009999); //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()); 

  • Users who have thanked poyzn for this post:

    low tech (11-05-2010)

  • #6
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    838
    Thanks
    172
    Thanked 90 Times in 90 Posts
    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.

  • #7
    Regular Coder poyzn's Avatar
    Join Date
    Nov 2010
    Posts
    266
    Thanks
    2
    Thanked 61 Times in 61 Posts
    note that you've used mysql reserved word "date" as a column name.
    next time please use date in back quotes:
    PHP Code:
     `date
    or just rename the column

  • #8
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    838
    Thanks
    172
    Thanked 90 Times in 90 Posts
    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

  • #9
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by low tech View Post
    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.

  • #10
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    838
    Thanks
    172
    Thanked 90 Times in 90 Posts
    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

    PHP 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

  • #11
    Regular Coder poyzn's Avatar
    Join Date
    Nov 2010
    Posts
    266
    Thanks
    2
    Thanked 61 Times in 61 Posts
    I guess it means that you can store strtotime(mysql_real_escape_string($date)) value in INT field

  • #12
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by low tech View Post
    PHP 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
    Yup, that's converting, but to a certain format, whereas:

    Code:
    '".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.
    Last edited by MattF; 11-06-2010 at 02:35 PM.

  • Users who have thanked MattF for this post:

    low tech (11-07-2010)

  • #13
    Regular Coder low tech's Avatar
    Join Date
    Dec 2009
    Posts
    838
    Thanks
    172
    Thanked 90 Times in 90 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •