Solved

use php and mysql to update varchar to mysqldatetime value or NULL

Posted on 2013-12-16
5
567 Views
Last Modified: 2013-12-17
followup to
http://www.experts-exchange.com/Programming/Languages/Regular_Expressions/Q_28312994.html
I think Ray has the correct answer to my project.

to make this question easier, I created a table called
a_messages2_TEST
and the requirement is to update the dateAgo column

CREATE TABLE `a_messages2_TEST` (
  `a_messages_id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) DEFAULT NULL,
  `profile_id` varchar(20) DEFAULT NULL,
  `sender` varchar(20) DEFAULT NULL,
  `message_id` bigint(20) DEFAULT NULL,
  `message_text` varchar(1000) DEFAULT NULL,
  `dateAgo` varchar(20) DEFAULT NULL,
  `message_read` tinyint(4) DEFAULT NULL,
  `this_user` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window



the empty field and field with the word 'ago', 'now' should be deleted because I do not know the time they were collected
and dates should be converted into mysql datetime format
<?php  // RAY_temp_rgb192.php
  public function ConvertDateTime($date_time_str = 'now')
  {
    // remove unwanted characters
    $date_time_str = str_replace('(', NULL, $date_time_str);
    $date_time_str = str_replace(')', NULL, $date_time_str);

    // translate to US English date format like 'hour', 'now', etc.
    $date_time_str = str_replace('hr', 'hour', $date_time_str);
    $date_time_str = str_replace('just now', 'now', $date_time_str);

    $date_format = NULL;
    try
    {
      $datetime = date_create($date_time_str);
      $date_format = date_format($datetime, 'Y-m-d H:i:s');
    }
    catch (Exception $e)
    {
      $this->writeLog($e->getMessage(), TRUE);
    }

    return $date_format;
  }

Open in new window



just now
just now
just now
just now
(17 mins ago)
(07/25 11:26)
(07/25 11:20)
(07/25 11:19)
2 days ago
1 week ago
2 weeks ago
(12 hrs ago)
(06/26 12:54)
(07/25 09:58)
(14 hrs ago)
(07/25 11:01)
(07/25 10:59)
(07/25 10:24)
(07/25 10:23)
(07/25 09:48)
(16 hrs ago)
(20 hrs ago)
(21 hrs ago)
(07/25 22:27)
(07/25 09:43)
(07/26 12:44)
(07/26 12:17)
(07/26 11:06)
(07/26 07:07)
(07/24 17:47)
(07/24 10:45)
(07/24 08:31)
(07/23 06:47)
(07/23 06:35)
(07/23 06:34)
4 days ago
5 days ago
6 days ago
3 weeks ago
1 month ago
3 days ago
1 day ago
(11 mins ago)
(21 mins ago)
(25 mins ago)
(15 mins ago)
(19 mins ago)
(16 mins ago)
(18 mins ago)
(20 mins ago)
(08/06 19:48)
(22 mins ago)
(23 mins ago)
(24 mins ago)
(26 mins ago)
(27 mins ago)
(08/07 06:06)
(08/06 23:33)
(08/06 20:27)
(08/06 18:08)
(08/06 14:42)
(08/06 14:41)
(08/06 10:14)
(08/05 15:42)
(08/05 15:33)
(08/01 10:11)
(08/04 05:57)
(08/04 04:21)
(08/03 17:08)
(08/03 16:40)
(08/01 10:15)
(10 mins ago)
(13 mins ago)
(14 mins ago)
(42 mins ago)
(1 hr ago)
(22 hrs ago)
(08/07 07:37)
(08/07 07:32)
(08/07 07:29)
(08/07 07:20)
(11 hrs ago)
(7 hrs ago)
(08/05 16:57)
(08/05 13:59)
(08/05 12:34)
(08/05 10:49)
(08/04 15:49)
(08/04 13:02)
(08/04 12:12)
(08/04 11:55)
(08/04 04:48)
(08/04 13:08)
(08/04 12:11)
(08/04 12:08)
(08/04 02:31)
(08/04 15:59)
(08/04 15:58)
(8 hrs ago)
(9 hrs ago)
(10 hrs ago)
(09/03 11:45)
2013-10-23 14:08:00
2013-10-23 14:04:00
2013-10-23 13:54:00
2013-10-23 13:35:00
2013-10-22 12:37:00
2013-10-22 10:31:00
2013-11-30 08:31:00

Open in new window



<?php // RAY_temp_rgb192.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Programming/Languages/Regular_Expressions/Q_28312994.html


// CREATE AN ARRAY OF TEST DATA
$test_dates_arrays = array
( array( "vdate" => "2013-12-04 00:44:52" )
, array( "vdate" => "2013-12-04 00:45:53" )
, array( "vdate" => "2013-12-04 00:46:58" )
, array( "vdate" => "Yesterday" )
, array( "vdate" => "Gooseball" )
, array( "vdate" => "Tomorrow"  )
, array( "vdate" => "Now"  )
, array( "vdate" => " 2013-12-4     00:44:52"  )
, array( "vdate" => " December 17, 2:38pm"  )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, vdate VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
foreach ($test_dates_arrays as $row)
{
    $safe_dt  = $mysqli->real_escape_string($row['vdate']);
    $sql = "INSERT INTO my_table ( vdate ) VALUES ( '$safe_dt' )";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_dt</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// ALTER THE TABLE TO ADD THE DATETIME COLUMN
$sql = "ALTER TABLE my_table ADD COLUMN ddate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// VISUALIZE THE TABLE STATUS AFTER "ALTER TABLE"
$sql = "SHOW CREATE TABLE my_table";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// GET THE ROWS THAT HAVE EMPTY ddate VALUES
$sql = "SELECT * FROM my_table WHERE ddate = '0000-00-00 00:00:00' ORDER BY id";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO UPDATE THE COLUMNS
while ($row = $res->fetch_object())
{
    // IF THE TIMESTAMP IS OK
    $tst = strtotime($row->vdate);
    if ($tst)
    {
        $new = date('c', $tst);
        $uql = "UPDATE my_table SET ddate = '$new' WHERE id = $row->id LIMIT 1";
        if (!$upd = $mysqli->query($uql))
        {
            $err
            = 'QUERY FAILURE:'
            . ' ERRNO: '
            . $mysqli->errno
            . ' ERROR: '
            . $mysqli->error
            . ' QUERY: '
            . $uql
            ;
            trigger_error($err, E_USER_ERROR);
        }
    }
}
echo PHP_EOL;


// GET ALL THE ROWS TO SHOW WHAT WE HAVE DONE
$sql = "SELECT * FROM my_table ORDER BY id";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

0
Comment
Question by:rgb192
  • 3
5 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39722899
I think you would want to have a DATETIME column in that table, so you would have a place to put the output.

This design pattern seems to make sense for converting the input strings into the ISO-8601 DATETIME strings.
http://www.laprbass.com/RAY_temp_rgb192.php

<?php // RAY_temp_rgb192.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28319524.html


// TEST DATA
$arr = array
( 'just now'
, 'just now'
, 'just now'
, 'just now'
, '(17 mins ago)'
, '(07/25 11:26)'
, '(07/25 11:20)'
, '(07/25 11:19)'
, '2 days ago'
, '1 week ago'
, '2 weeks ago'
, '(12 hrs ago)'
, '(06/26 12:54)'
, '(07/25 09:58)'
, '(14 hrs ago)'
, '(07/25 11:01)'
, '(07/25 10:59)'
, '(07/25 10:24)'
, '(07/25 10:23)'
, '(07/25 09:48)'
, '(16 hrs ago)'
, '(20 hrs ago)'
, '(21 hrs ago)'
, '(07/25 22:27)'
, '(07/25 09:43)'
, '(07/26 12:44)'
, '(07/26 12:17)'
, '(07/26 11:06)'
, '(07/26 07:07)'
, '(07/24 17:47)'
, '(07/24 10:45)'
, '(07/24 08:31)'
, '(07/23 06:47)'
, '(07/23 06:35)'
, '(07/23 06:34)'
, '4 days ago'
, '5 days ago'
, '6 days ago'
, '3 weeks ago'
, '1 month ago'
, '3 days ago'
, '1 day ago'
, '(11 mins ago)'
, '(21 mins ago)'
, '(25 mins ago)'
, '(15 mins ago)'
, '(19 mins ago)'
, '(16 mins ago)'
, '(18 mins ago)'
, '(20 mins ago)'
, '(08/06 19:48)'
, '(22 mins ago)'
, '(23 mins ago)'
, '(24 mins ago)'
, '(26 mins ago)'
, '(27 mins ago)'
, '(08/07 06:06)'
, '(08/06 23:33)'
, '(08/06 20:27)'
, '(08/06 18:08)'
, '(08/06 14:42)'
, '(08/06 14:41)'
, '(08/06 10:14)'
, '(08/05 15:42)'
, '(08/05 15:33)'
, '(08/01 10:11)'
, '(08/04 05:57)'
, '(08/04 04:21)'
, '(08/03 17:08)'
, '(08/03 16:40)'
, '(08/01 10:15)'
, '(10 mins ago)'
, '(13 mins ago)'
, '(14 mins ago)'
, '(42 mins ago)'
, '(1 hr ago)'
, '(22 hrs ago)'
, '(08/07 07:37)'
, '(08/07 07:32)'
, '(08/07 07:29)'
, '(08/07 07:20)'
, '(11 hrs ago)'
, '(7 hrs ago)'
, '(08/05 16:57)'
, '(08/05 13:59)'
, '(08/05 12:34)'
, '(08/05 10:49)'
, '(08/04 15:49)'
, '(08/04 13:02)'
, '(08/04 12:12)'
, '(08/04 11:55)'
, '(08/04 04:48)'
, '(08/04 13:08)'
, '(08/04 12:11)'
, '(08/04 12:08)'
, '(08/04 02:31)'
, '(08/04 15:59)'
, '(08/04 15:58)'
, '(8 hrs ago)'
, '(9 hrs ago)'
, '(10 hrs ago)'
, '(09/03 11:45)'
, '2013-10-23 14:08:00'
, '2013-10-23 14:04:00'
, '2013-10-23 13:54:00'
, '2013-10-23 13:35:00'
, '2013-10-22 12:37:00'
, '2013-10-22 10:31:00'
, '2013-11-30 08:31:00'
)
;

// PROCESS EACH DATA ELEMENT
foreach ($arr as $date_time_str)
{
    // DEFAULT IS FAILURE
    $date_format = '0000-00-00 00:00:00';

    // REPAIR WHAT CAN BE REPAIRED
    $date_time_str = str_replace('(', NULL, $date_time_str);
    $date_time_str = str_replace(')', NULL, $date_time_str);
    $date_time_str = str_replace('hr', 'hour', $date_time_str);

    // WITH RELATIVE TIMES, WE HAVE FAILURE
    if ( (FALSE === stripos($date_time_str, 'ago')) && (FALSE === stripos($date_time_str, 'now')) )
    {
        try
        {
            $datetime    = date_create($date_time_str);
            $date_format = date_format($datetime, 'Y-m-d H:i:s');
        }
        catch (Exception $e)
        {
            echo $e->getMessage();
        }
    }
    echo PHP_EOL . "WE GET THIS ISO-STANDARD DATETIME STRING: $date_format FROM $date_time_str";
}

Open in new window

0
 
LVL 14

Expert Comment

by:ThG
ID: 39722912
I think I misunderstood the question, here is what I understood: You want to purify the varchar column and convert it to DATETIME, dropping all relative values because you lost the information of when they were relative to, correct?

In that case you need to make a second DATETIME column, and process the whole table updating row by row (you don't need to create a new table in this case), preg_match the content and if it's an absolute date store to the DATETIME column.

Is that correct?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39722918
@ThG: There is some background information you might want to look at.  Please see this link and the associated question / dialog.
http://www.experts-exchange.com/Programming/Languages/Regular_Expressions/Q_28312994.html#a39720144
0
 

Author Closing Comment

by:rgb192
ID: 39723013
This works if input is an array and output is echo.

Thanks.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39723791
Thanks for the points.  This is intended to be a teaching example, showing the essential parts of the data conversion from free-form string to ISO-8601 standard DATETIME values.  You may want to integrate this example into your own programming using something like the script shown here:
http://www.experts-exchange.com/Programming/Languages/Regular_Expressions/Q_28312994.html#a39720144
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now