Solved

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

Posted on 2013-12-16
5
606 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 110

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 110

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 110

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

763 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