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 6 of 6
  1. #1
    New Coder
    Join Date
    Mar 2006
    Location
    I'm lost, livin inside myself
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Regex issue - Converting a written date to a timestamp

    I have a problem, somehow through one of my scripts, all the timestamps in my database were set to 0000-00-00 00:00:00. I can fix this because one of the fields contains the written date in this format "Wednesday, June 7, 2006", I think I need to make a regex expression that can read that and generate a new timestamp from it, I'm just not sure where to start because I've barely used regex. Some of the dates are not in that format because it contains over two years worth of data, and I want to just delete the ones that are not in that format as well. I'd appreciate any help here, I'm kind of stumped. Once I match the expression I can generate the timestamp, but I don't know how to match it.
    $guiness &= new sixpack();
    $guiness->chug();

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Just use strtotime() and convert the resulting unix timestamp.

  • #3
    Regular Coder dniwebdesign's Avatar
    Join Date
    Dec 2003
    Location
    Carrot River, Saskatchewan
    Posts
    838
    Thanks
    15
    Thanked 9 Times in 9 Posts
    Here are a few steps you can take to do this and convert it.
    1. Take the written date out of the database...
    2. Store it in a variable
    3. Convert it to a timestamp via strtotime()
    4. Convert the timestamp to your format using date()
      Example:
      PHP Code:
      date("Y-m-d h:i:s",$date); 


    Below is a full example of how I would do it:
    PHP Code:
      <?php
        $mysql 
    mysql_query("SELECT * FROM yourtable");
        while(
    $row mysql_fetch_array($mysql)) {
            
    $thedate $row["fulldaterow"];
            
    $timestamp strtotime($thedate);
            
    $new date("Y-m-d h:i:s",$date);
            
    $update mysql_query("UPDATE yourtable SET timestamp='$new' WHERE id='".$row["id"]."'");
        }
      
    ?>
    Dawson Irvine
    CEO - DNI Web Design
    http://www.dniwebdesign.com

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    2,960
    Thanks
    2
    Thanked 304 Times in 296 Posts
    You can also use the mysql STR_TO_DATE(str,format) function to convert your values in a known format into a DATETIME value.

    You could for example, create a new column to hold your DATETIME values and run a query that finds (some WHERE clause, such as greater than a starting ID number...) the existing values in your format and populates the new DATETIME column using the STR_TO_DATE function value.

    If your older values are in a known format that you can write a WHERE clause to find, you should also be able to use a STR_TO_DATE format to convert them as well.

    Once you have all the old formated data converted and populated into the new DATETIME column, you can delete the old column.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    New Coder
    Join Date
    Mar 2006
    Location
    I'm lost, livin inside myself
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dniwebdesign View Post
    Here are a few steps you can take to do this and convert it.
    1. Take the written date out of the database...
    2. Store it in a variable
    3. Convert it to a timestamp via strtotime()
    4. Convert the timestamp to your format using date()
      Example:
      PHP Code:
      date("Y-m-d h:i:s",$date); 


    Below is a full example of how I would do it:
    PHP Code:
      <?php
        $mysql 
    mysql_query("SELECT * FROM yourtable");
        while(
    $row mysql_fetch_array($mysql)) {
            
    $thedate $row["fulldaterow"];
            
    $timestamp strtotime($thedate);
            
    $new date("Y-m-d h:i:s",$date);
            
    $update mysql_query("UPDATE yourtable SET timestamp='$new' WHERE id='".$row["id"]."'");
        }
      
    ?>
    Thanks, I used a variation of that and got the desired results.
    $guiness &= new sixpack();
    $guiness->chug();

  • #6
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    I'd suggest using the STR_TO_DATE(), although you probably already coded the other.

    If you don't have to take the data out of MySQL, that is usually better.

    Although I'm curious as to why you stored the date in 2 fields (even though in this case it was good luck with bad design).

    P.S.: Your signature would create an E_NOTICE, as assigning an object by reference does not make sense (since objects are always assigned by reference). Don't spread bad code


  •  

    Posting Permissions

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