Logging Activity in Browse

[marc]

Hello,

As described on the wiki, I set up a logging activity for my table. The goal is to have this log information displayed on a browse screen.
I've already added three additional columns (added, vieved, edited) to the browse.

Now I'm looking for a way to extract the values from the JSON string for each field.

Code: Select all

{
 "added": {
   "user": "globeadmin",
   "time": 1513706208
 },
 "viewed": {
   "user": "globeadmin",
   "time": 1513706382
 },
 "edited": {
   "user": "globeadmin",
   "time": 1513706382
 }
}

[admin]

marc,

If you have created fields like this...

Code: Select all

ALTER TABLE `aaa` 
ADD `aaa_added` DATETIME NOT NULL AFTER `aaa_nulog`, 
ADD `aaa_viewed` DATETIME NOT NULL AFTER `aaa_added`, 
ADD `aaa_edited` DATETIME NOT NULL AFTER `aaa_viewed`;

Then try this in a Procedure...

Code: Select all

$t  = nuRunQuery('SELECT * FROM aaa');

while($r = db_fetch_object($t)){
    
    $i  = $r->aaa_id;
    $j  = json_decode($r->aaa_nulog);
    $a  =  date('Y-m-d H:i:s', $j->added->time);
    $v  =  date('Y-m-d H:i:s', $j->viewed->time);
    $e  =  date('Y-m-d H:i:s', $j->edited->time);
    
    
    $s  = "
            UPDATE aaa SET 
                aaa_added = ?, 
                aaa_viewed = ?, 
                aaa_edited = ?
                
            WHERE aaa_id = ?
    ";
    
    nuRunQuery($s, [$a, $v, $e, $i]);

}

Steven

[toms]

Hi,

In addtion to what has been suggested you could use JSON_EXTRACT() in your query.

https://dev.mysql.com/doc/refman/en/jso ... tions.html
(you need MySQL 5.7.8+)

https://mariadb.com/kb/en/library/json_extract
(JSON functions were added in MariaDB 10.2.3.)

This might slow down your query a little, especially if your table contains lots of rows.

[admin]

.