Solved

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

Posted on 2013-12-16
5
585 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 109

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 109

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 109

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

772 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