SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    convert text date into unix date - how!?

    Hi! I've an urgent problem, which has bought my database transfer to a halt. Maybe you can help?

    I'm transfering a user table from PHPNUKE into phpBB message board.

    The user table has name, password, prefrences etc.

    One field is the Registered date. In the old phpnuke, it's stored as a 20 VARCHAR field, like this: NOV 20,2002

    In the new phpbb message board, this fields is stored as a INT-11 field... a UNIX date thing right?

    how can I easily convert the old data into the new format, so I can insert it correctly?

    thanks!

  2. #2
    SitePoint Enthusiast Analyser's Avatar
    Join Date
    Oct 2002
    Location
    Delft, The Netherlands
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have a look at the strtotime() function. And if that doesn't accept your particular format, you could use a regular expression to mold the date into something strtotime() can handle.
    "A poor programmer is he who blames his tools."
    http://analyser.oli.tudelft.nl/

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for that... but I'm a real noob..

    so if anyone is able to suggest a real basic script that can convert this for me, then that would be great.

    I guess, it would go like this:

    SELECT data from table/field
    for each date in the table, convert format
    write data into new table

    but I have no idea how to do this! my level of skill is using phpmyadmin... that's all!!

  4. #4
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ummm, I don't believe there's any function built into Mysql that will allowyou to do that conversion on the fly. You can do this (once you add a new field to the table such as newdate:
    PHP Code:
    <?php
    $sql 
    mysql_query("SELECT datefield FROM table");
    while(
    $row mysql_fetch_array($sql))
       {
       
    $id $row['primaryid'];
       
    $unformattedate $row['datefield'];
       
    $unixtime strtotime($unformattedtime);

       
    // Insert new date
       
    mysql_query("UPDATE table SET newdate = '$unixtime' WHERE primaryid = '$id'");
       }
    ?>
    Once you convert aLL the dates successfully and you have checked them to make sure, delete the old time field and rename newdate to your old one.

    CAUTION - BACKUP YOUR DATABASE BEFORE DOING ANY OF THIS!!!!
    It's a tad complex but that's how I'd do it.
    Aaron
    Aaron Brazell
    Technosailor



  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for that!!

    im leaving work now, so I won't be able to try it out until monday... i'll let you know how it goes! :-) thanks!

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, can't get this script to work.

    here's what I have.

    PHP Code:
    <?php
    $user
    ="";
    $host="localhost";
    $password="";
    $database="allusers";
    echo 
    connecting;
    echo 
    '<br>';
    mysql_connect($host,$user,$password);
    mysql_select_db($database);
    echo 
    connected;
    echo 
    '<br>';
    echo 
    'database is:';
    echo 
    $database;
    echo 
    '<br>';

    $sql mysql_query("SELECT user_regdate FROM nuke_users");
    echo 
    $sql;
    echo 
    '<br>';
    while(
    $row mysql_fetch_array($sql))
    {
    $id $row['uid'];
    $unformattedate $row['user_regdate'];
    $unixtime strtotime($unformattedtime);
    // Insert new date
    mysql_query("UPDATE nuke_users SET newdate = '$unixtime' WHERE uid = '$id'");
    }
    echo 
    complete;
    ?>
    It connects, and the loop runs, but no data is updated.
    If I do an echo for the vars in the program, they showup as empty.

    any tips please!?

    ps. I changed the 'primaryid' to be uid as it is in my table.. is that correct?

    THANKS!

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    after some more research, here's what happens:

    so it reads each date,
    but doesn't loop properly, as the unixstamp stays the same.


    $id is:
    unformattedate is:Nov 10, 2000
    makenewdate
    unixtime1035759600


    $id is:
    unformattedate is:Feb 21, 2002
    makenewdate
    unixtime1035759600


    $id is:
    unformattedate is:Mar 03, 2002
    makenewdate
    unixtime1035759600

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, fixed the first problem

    changed this:

    $unixtime = strtotime($unformattedtime);

    to

    $unixtime = strtotime($unformattedate);

    Now it produces the correct data each time.

    But I have the problem, I am unable to add this data to the database...

    how can I add this correctly?


Bookmarks

Posting Permissions

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