use php and mysql to update varchar to mysqldatetime value or NULL
followup to
https://www.experts-exchange.com/questions/28312994/regular-expression-to-see-if-mysql-is-a-date.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
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
https://www.experts-exchange.com/questions/28312994/regular-expression-to-see-if-mysql-is-a-date.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;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;
}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<?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;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@ThG: There is some background information you might want to look at. Please see this link and the associated question / dialog.
https://www.experts-exchange.com/questions/28312994/regular-expression-to-see-if-mysql-is-a-date.html?anchorAnswerId=39720144#a39720144
https://www.experts-exchange.com/questions/28312994/regular-expression-to-see-if-mysql-is-a-date.html?anchorAnswerId=39720144#a39720144
ASKER
This works if input is an array and output is echo.
Thanks.
Thanks.
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:
https://www.experts-exchange.com/questions/28312994/regular-expression-to-see-if-mysql-is-a-date.html?anchorAnswerId=39720144#a39720144
https://www.experts-exchange.com/questions/28312994/regular-expression-to-see-if-mysql-is-a-date.html?anchorAnswerId=39720144#a39720144
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?