Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

log onto mysql and update column

Posted on 2013-12-16
17
1 solution
Medium Priority
?
273 Views
Last Modified: 2014-02-13
Please see earlier question:

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

That works if input is an array and output is echo.



But input table a_messages2_TEST.dateAgo (varchar) is an array and output same column using update.

And there needs to be a mysqli or pdo login to mysql
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
Join The Community
  • 9
  • 6
17 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39723751
"There needs to be" is not a question that we can answer.  This is something that should be a part of your basic library of examples.  You can copy the connection examples from this article.  
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

You can use the connection information over and over.  Usually a programmer would have a common script called at the top of all PHP scripts.  A data base connection might be included in the common script.
output same column using update
I very gently but strongly urge you to NOT do that.  Here is why:
http://en.wikipedia.org/wiki/Mung_%28computer_term%29

There are two good choices:  You can use ALTER TABLE to add a new column and you can update the new column as you convert the datetime strings into the ISO-8601 standard values.  Or you can copy the table and make the updates in the copied table.  I would prefer the first option, because you will be able to use a tool such as phpMyAdmin to examine the rows and make manual corrections if needed.  With the original and standardized datetime values in the same row you will have only one place to look for things that require manual intervention.

If you UPDATE the original rows by overwriting the original columns, you will have lost the original data.  In other words, the process is not reversible and once it is started it must be 100% successful, or you must accept an arbitrarily large failure rate.  Those are not circumstances you want to live with!  See Antipractice #2 and #3.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html
0
 

Author Comment

by:rgb192
ID: 39725149 
// 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);
}
// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
var_dump($mysqli);

Open in new window




 You can use ALTER TABLE to add a new column dateAgo_EDIT
so I can compare but I do not know the where statement.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39726784
do not know the where statement
Please see line 139 in the fourth code snippet of this question:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28319524.html
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Go "Always On" Today
 

Author Comment

by:rgb192
ID: 39737790 
<?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


// old TEST DATA (I now want table 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'
)
;













// 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;
}








//I added this line because I want to use my table data as test data
$arr=$sql;





// 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


I am attempting to switch $arr hard coded test data to select my table
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39738069
Line 340 destroys the $arr array, so this script will probably be pretty useless.  It was meant as a teaching example to show the design of how you might get some of the data/time values into a usable format.  Obviously it will not work for all of them, but it can reduce the amount of manual work that must be done to correct the contents of the data base.  

Do you have a data base table that has these "strange" date/time values?  If so, you can use a SELECT query to get a results set, then you can use while() to iterate over the results set.  Inside the while loop you can generate the ISO-8601 standard date/time value and you can insert this value into the new column that you've added to the table. This is what is going on in line 267-308.
0
 

Author Comment

by:rgb192
ID: 39750565
Do you have a data base table that has these "strange" date/time values?

no these are legacy values


If so, you can use a SELECT query to get a results set, then you can use while() to iterate over the results set.  Inside the while loop you can generate the ISO-8601 standard date/time value and you can insert this value into the new column that you've added to the table. This is what is going on in line 267-308.

I do not understand
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39751227
Are the "legacy values" in a data base table?  If so, please post the CREATE TABLE statement and tell us what column contains the legacy values.  If not, please explain where the legacy values are recorded and where you want to put the standardized values.
0
 

Author Comment

by:rgb192
ID: 39755511
a_messages2: dateAgo is varchar (incorrect) and has many legacy values (or bad values: not sure of proper syntax)
here are the values:
'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

a_messages2 
CREATE TABLE `a_messages2` (
  `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 AUTO_INCREMENT=9969 DEFAULT CHARSET=utf8;

Open in new window


the only column of that is different is dateAgo
a_messages: dateAgo is datetime (correct)
CREATE TABLE `a_messages` ( `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` datetime 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 AUTO_INCREMENT=254 DEFAULT CHARSET=utf8;

Open in new window

0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39756115
Please see http://www.laprbass.com/RAY_temp_rgb192.php

Note the effect of relying on a date/time string where the year is not specified.  This may or may not produce what you want.  After this has been run, you would want to use phpMyAdmin or a table maintenance script to manually correct the values that could not be converted from the "freeform" to the ISO-8601 standard date.  Once you're satisfied with that process, you can ALTER TABLE to remove the dateAgo column, and rename the validDateAgo column to dateAgo.

<?php // RAY_temp_rgb192.php
error_reporting(E_ALL);

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28319753.html#a39755511

// 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);
}

// CREATE THE TABLE WITH THE UNFORMATTED dateAgo COLUMN
$sql = <<<EOD
CREATE TEMPORARY TABLE `a_messages`
( `a_messages_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, `dateAgo` varchar(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
EOD;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// THE TEST DATA SET POSTED AT EE
$dat = 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'
)
;
// LOAD THE TEST DATA INTO THE TABLE
foreach ($dat as $str)
{
    $sql = "INSERT INTO a_messages (dateAgo) VALUES ('$str')";
    $res = $mysqli->query($sql);
    if (!$res)
	{
	    $err
	    = 'QUERY FAILURE:'
	    . ' ERRNO: '
	    . $mysqli->errno
	    . ' ERROR: '
	    . $mysqli->error
	    . ' QUERY: '
	    . $sql
	    ;
	    trigger_error($err, E_USER_ERROR);
	}
}

// ALTER THE TABLE TO ADD THE CORRECTLY FORMATTED DATETIME COLUMN
$sql = "ALTER TABLE `a_messages` ADD validDateAgo DATETIME 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);
}

// UPDATE THE TABLE TO MOVE THE VALID DATES INTO THE NEW DATETIME COLUMN
$sql = "SELECT a_messages_id, dateAgo FROM a_messages";
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())
{
    // FILTER AND CLEANUP THE OLD DATE COLUMN
    $dtm = $row->dateAgo;
    if (stripos($dtm, 'ago')) continue;
    $dtm = str_replace('(', NULL, $dtm);
    $dtm = str_replace(')', NULL, $dtm);
    if (!$dtm = strtotime($dtm)) continue;
    $dtm = date('c', $dtm);

    // UPDATE THIS ROW
    $upd = "UPDATE a_messages SET validDateAgo = '$dtm' WHERE a_messages_id = $row->a_messages_id LIMIT 1";
    $mysqli->query($upd);
}

// SHOW THE WORK PRODUCT
$sql = "SELECT a_messages_id, dateAgo, validDateAgo FROM a_messages";
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 '<br>' . PHP_EOL;
}

Open in new window

HTH, ~Ray
0
 

Author Comment

by:rgb192
ID: 39756366
I checked the code before I decided to run

a_messages is the production table, the column dateAgo is correct datetime and every field has a datetime value

the table a_messages2 (which is not in your code) are the older values which I which to insert into a_messages
dateAgo was varchar and there were many different types of false values
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39756561
Yes, I understand about dateAgo values.  "Just now" and many of the others are meaningless because there is no date/time context.  This is the sort of thing that has to be handled manually.

Anyway, I hope the concept is clear in the example.  I tested the code and it did what was expected.  Best regards, ~Ray
0
 

Author Comment

by:rgb192
ID: 39758371 
stdClass Object ( [a_messages_id] => 1 [dateAgo] => just now [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 2 [dateAgo] => just now [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 3 [dateAgo] => just now [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 4 [dateAgo] => just now [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 5 [dateAgo] => (17 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 6 [dateAgo] => (07/25 11:26) [validDateAgo] => 2014-07-25 11:26:00 ) 
stdClass Object ( [a_messages_id] => 7 [dateAgo] => (07/25 11:20) [validDateAgo] => 2014-07-25 11:20:00 ) 
stdClass Object ( [a_messages_id] => 8 [dateAgo] => (07/25 11:19) [validDateAgo] => 2014-07-25 11:19:00 ) 
stdClass Object ( [a_messages_id] => 9 [dateAgo] => 2 days ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 10 [dateAgo] => 1 week ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 11 [dateAgo] => 2 weeks ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 12 [dateAgo] => (12 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 13 [dateAgo] => (06/26 12:54) [validDateAgo] => 2014-06-26 12:54:00 ) 
stdClass Object ( [a_messages_id] => 14 [dateAgo] => (07/25 09:58) [validDateAgo] => 2014-07-25 09:58:00 ) 
stdClass Object ( [a_messages_id] => 15 [dateAgo] => (14 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 16 [dateAgo] => (07/25 11:01) [validDateAgo] => 2014-07-25 11:01:00 ) 
stdClass Object ( [a_messages_id] => 17 [dateAgo] => (07/25 10:59) [validDateAgo] => 2014-07-25 10:59:00 ) 
stdClass Object ( [a_messages_id] => 18 [dateAgo] => (07/25 10:24) [validDateAgo] => 2014-07-25 10:24:00 ) 
stdClass Object ( [a_messages_id] => 19 [dateAgo] => (07/25 10:23) [validDateAgo] => 2014-07-25 10:23:00 ) 
stdClass Object ( [a_messages_id] => 20 [dateAgo] => (07/25 09:48) [validDateAgo] => 2014-07-25 09:48:00 ) 
stdClass Object ( [a_messages_id] => 21 [dateAgo] => (16 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 22 [dateAgo] => (20 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 23 [dateAgo] => (21 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 24 [dateAgo] => (07/25 22:27) [validDateAgo] => 2014-07-25 22:27:00 ) 
stdClass Object ( [a_messages_id] => 25 [dateAgo] => (07/25 09:43) [validDateAgo] => 2014-07-25 09:43:00 ) 
stdClass Object ( [a_messages_id] => 26 [dateAgo] => (07/26 12:44) [validDateAgo] => 2014-07-26 12:44:00 ) 
stdClass Object ( [a_messages_id] => 27 [dateAgo] => (07/26 12:17) [validDateAgo] => 2014-07-26 12:17:00 ) 
stdClass Object ( [a_messages_id] => 28 [dateAgo] => (07/26 11:06) [validDateAgo] => 2014-07-26 11:06:00 ) 
stdClass Object ( [a_messages_id] => 29 [dateAgo] => (07/26 07:07) [validDateAgo] => 2014-07-26 07:07:00 ) 
stdClass Object ( [a_messages_id] => 30 [dateAgo] => (07/24 17:47) [validDateAgo] => 2014-07-24 17:47:00 ) 
stdClass Object ( [a_messages_id] => 31 [dateAgo] => (07/24 10:45) [validDateAgo] => 2014-07-24 10:45:00 ) 
stdClass Object ( [a_messages_id] => 32 [dateAgo] => (07/24 08:31) [validDateAgo] => 2014-07-24 08:31:00 ) 
stdClass Object ( [a_messages_id] => 33 [dateAgo] => (07/23 06:47) [validDateAgo] => 2014-07-23 06:47:00 ) 
stdClass Object ( [a_messages_id] => 34 [dateAgo] => (07/23 06:35) [validDateAgo] => 2014-07-23 06:35:00 ) 
stdClass Object ( [a_messages_id] => 35 [dateAgo] => (07/23 06:34) [validDateAgo] => 2014-07-23 06:34:00 ) 
stdClass Object ( [a_messages_id] => 36 [dateAgo] => 4 days ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 37 [dateAgo] => 5 days ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 38 [dateAgo] => 6 days ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 39 [dateAgo] => 3 weeks ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 40 [dateAgo] => 1 month ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 41 [dateAgo] => 3 days ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 42 [dateAgo] => 1 day ago [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 43 [dateAgo] => (11 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 44 [dateAgo] => (21 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 45 [dateAgo] => (25 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 46 [dateAgo] => (15 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 47 [dateAgo] => (19 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 48 [dateAgo] => (16 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 49 [dateAgo] => (18 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 50 [dateAgo] => (20 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 51 [dateAgo] => (08/06 19:48) [validDateAgo] => 2014-08-06 19:48:00 ) 
stdClass Object ( [a_messages_id] => 52 [dateAgo] => (22 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 53 [dateAgo] => (23 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 54 [dateAgo] => (24 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 55 [dateAgo] => (26 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 56 [dateAgo] => (27 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 57 [dateAgo] => (08/07 06:06) [validDateAgo] => 2014-08-07 06:06:00 ) 
stdClass Object ( [a_messages_id] => 58 [dateAgo] => (08/06 23:33) [validDateAgo] => 2014-08-06 23:33:00 ) 
stdClass Object ( [a_messages_id] => 59 [dateAgo] => (08/06 20:27) [validDateAgo] => 2014-08-06 20:27:00 ) 
stdClass Object ( [a_messages_id] => 60 [dateAgo] => (08/06 18:08) [validDateAgo] => 2014-08-06 18:08:00 ) 
stdClass Object ( [a_messages_id] => 61 [dateAgo] => (08/06 14:42) [validDateAgo] => 2014-08-06 14:42:00 ) 
stdClass Object ( [a_messages_id] => 62 [dateAgo] => (08/06 14:41) [validDateAgo] => 2014-08-06 14:41:00 ) 
stdClass Object ( [a_messages_id] => 63 [dateAgo] => (08/06 10:14) [validDateAgo] => 2014-08-06 10:14:00 ) 
stdClass Object ( [a_messages_id] => 64 [dateAgo] => (08/05 15:42) [validDateAgo] => 2014-08-05 15:42:00 ) 
stdClass Object ( [a_messages_id] => 65 [dateAgo] => (08/05 15:33) [validDateAgo] => 2014-08-05 15:33:00 ) 
stdClass Object ( [a_messages_id] => 66 [dateAgo] => (08/01 10:11) [validDateAgo] => 2014-08-01 10:11:00 ) 
stdClass Object ( [a_messages_id] => 67 [dateAgo] => (08/04 05:57) [validDateAgo] => 2014-08-04 05:57:00 ) 
stdClass Object ( [a_messages_id] => 68 [dateAgo] => (08/04 04:21) [validDateAgo] => 2014-08-04 04:21:00 ) 
stdClass Object ( [a_messages_id] => 69 [dateAgo] => (08/03 17:08) [validDateAgo] => 2014-08-03 17:08:00 ) 
stdClass Object ( [a_messages_id] => 70 [dateAgo] => (08/03 16:40) [validDateAgo] => 2014-08-03 16:40:00 ) 
stdClass Object ( [a_messages_id] => 71 [dateAgo] => (08/01 10:15) [validDateAgo] => 2014-08-01 10:15:00 ) 
stdClass Object ( [a_messages_id] => 72 [dateAgo] => (10 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 73 [dateAgo] => (13 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 74 [dateAgo] => (14 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 75 [dateAgo] => (42 mins ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 76 [dateAgo] => (1 hr ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 77 [dateAgo] => (22 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 78 [dateAgo] => (08/07 07:37) [validDateAgo] => 2014-08-07 07:37:00 ) 
stdClass Object ( [a_messages_id] => 79 [dateAgo] => (08/07 07:32) [validDateAgo] => 2014-08-07 07:32:00 ) 
stdClass Object ( [a_messages_id] => 80 [dateAgo] => (08/07 07:29) [validDateAgo] => 2014-08-07 07:29:00 ) 
stdClass Object ( [a_messages_id] => 81 [dateAgo] => (08/07 07:20) [validDateAgo] => 2014-08-07 07:20:00 ) 
stdClass Object ( [a_messages_id] => 82 [dateAgo] => (11 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 83 [dateAgo] => (7 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 84 [dateAgo] => (08/05 16:57) [validDateAgo] => 2014-08-05 16:57:00 ) 
stdClass Object ( [a_messages_id] => 85 [dateAgo] => (08/05 13:59) [validDateAgo] => 2014-08-05 13:59:00 ) 
stdClass Object ( [a_messages_id] => 86 [dateAgo] => (08/05 12:34) [validDateAgo] => 2014-08-05 12:34:00 ) 
stdClass Object ( [a_messages_id] => 87 [dateAgo] => (08/05 10:49) [validDateAgo] => 2014-08-05 10:49:00 ) 
stdClass Object ( [a_messages_id] => 88 [dateAgo] => (08/04 15:49) [validDateAgo] => 2014-08-04 15:49:00 ) 
stdClass Object ( [a_messages_id] => 89 [dateAgo] => (08/04 13:02) [validDateAgo] => 2014-08-04 13:02:00 ) 
stdClass Object ( [a_messages_id] => 90 [dateAgo] => (08/04 12:12) [validDateAgo] => 2014-08-04 12:12:00 ) 
stdClass Object ( [a_messages_id] => 91 [dateAgo] => (08/04 11:55) [validDateAgo] => 2014-08-04 11:55:00 ) 
stdClass Object ( [a_messages_id] => 92 [dateAgo] => (08/04 04:48) [validDateAgo] => 2014-08-04 04:48:00 ) 
stdClass Object ( [a_messages_id] => 93 [dateAgo] => (08/04 13:08) [validDateAgo] => 2014-08-04 13:08:00 ) 
stdClass Object ( [a_messages_id] => 94 [dateAgo] => (08/04 12:11) [validDateAgo] => 2014-08-04 12:11:00 ) 
stdClass Object ( [a_messages_id] => 95 [dateAgo] => (08/04 12:08) [validDateAgo] => 2014-08-04 12:08:00 ) 
stdClass Object ( [a_messages_id] => 96 [dateAgo] => (08/04 02:31) [validDateAgo] => 2014-08-04 02:31:00 ) 
stdClass Object ( [a_messages_id] => 97 [dateAgo] => (08/04 15:59) [validDateAgo] => 2014-08-04 15:59:00 ) 
stdClass Object ( [a_messages_id] => 98 [dateAgo] => (08/04 15:58) [validDateAgo] => 2014-08-04 15:58:00 ) 
stdClass Object ( [a_messages_id] => 99 [dateAgo] => (8 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 100 [dateAgo] => (9 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 101 [dateAgo] => (10 hrs ago) [validDateAgo] => 0000-00-00 00:00:00 ) 
stdClass Object ( [a_messages_id] => 102 [dateAgo] => (09/03 11:45) [validDateAgo] => 2014-09-03 11:45:00 ) 
stdClass Object ( [a_messages_id] => 103 [dateAgo] => 2013-10-23 14:08:00 [validDateAgo] => 2013-10-23 14:08:00 ) 
stdClass Object ( [a_messages_id] => 104 [dateAgo] => 2013-10-23 14:04:00 [validDateAgo] => 2013-10-23 14:04:00 ) 
stdClass Object ( [a_messages_id] => 105 [dateAgo] => 2013-10-23 13:54:00 [validDateAgo] => 2013-10-23 13:54:00 ) 
stdClass Object ( [a_messages_id] => 106 [dateAgo] => 2013-10-23 13:35:00 [validDateAgo] => 2013-10-23 13:35:00 ) 
stdClass Object ( [a_messages_id] => 107 [dateAgo] => 2013-10-22 12:37:00 [validDateAgo] => 2013-10-22 12:37:00 ) 
stdClass Object ( [a_messages_id] => 108 [dateAgo] => 2013-10-22 10:31:00 [validDateAgo] => 2013-10-22 10:31:00 ) 
stdClass Object ( [a_messages_id] => 109 [dateAgo] => 2013-11-30 08:31:00 [validDateAgo] => 2013-11-30 08:31:00 )

Open in new window



This is the output

I think a_messages2 is the table that needs to be edited because it has the varchar dateAgo

I do not notice any changes in the a_messages table (but I think this table is correct anyway)
0
 

Author Comment

by:rgb192
ID: 39773430
should I show other data for scee?
0
 

Author Comment

by:rgb192
ID: 39792432
Can this question be broken down into smaller questions.
0
 

Author Closing Comment

by:rgb192
ID: 39857922
works for me if the test data ('1 week ago, date incorrect format) is coming the file and not the mysql table
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Watch The Video
Question has a verified solution.

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

Display your dates as Today, Yesterday etc in PHP
Article by: Gary
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
AJAX form submission with customized server field validation
Article by: Julian
This article discusses how to implement server side field validation and display customized error messages to the client.
Matching Tagged Data with PHP Regular Expressions
Video by: Terry
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Lists and Links
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Advertise Here

660 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

Advertise Here