Current Status and Previous States where to start

[ScoobyDont]

HI All, 

 

Sorry for sounding crap but searching the net has come up with nothing and that's what I have nothing...and to be honest dont know where to start looking for what I need 

 

I will try and explain as best I can

 

In the db I have  current_status, this is updated from a select list in a form on website one

 

status 1 

status 2

status 3

etc etc

 

So when the user changes the "status" from website 1 it updates the database and shows the current status in website 2

 

What I want is to show the previous states with a time  stamp on when it was changed something like image attached

 

Am I right in thinking I will not be able to do this with only working with one row in the db or is this possible with php alone

 

Can someone give me a pointer where to start, I am not after anyone to write code just a little advice

 

Thanks in advance

 

 

[Barand]

Each time the status changes you need to store the status and timestamp in a new record. You may even want to store old_status|new_status|timestamp. In addition, each record will need the id of whatever the status refers to.

[ScoobyDont]

Right Ok will go down that route and bring this thread back up when without no doubt I will get stuck again

 

Thank you...much appreciated

[ScoobyDont]

Hi, 

 

I have managed to get it partially working but now stuck again, 

 

When I change the status the time changes in the db..which is what I wanted

 

I will now try and explain what I want to do next 

 

When I change from Status 1 to Status 2 is it possible to grab the time of status change and make it static so it wont change when the status alters in the db again from 2-3

 

 

Will I need another column in the db or is there something I can do with php

 

Thanks in advance

[Barand]

A column defined as type TIMESTAMP can be configured to automatically record the current time when

 

a ) the record is inserted, or

b ) the record is updated

 

You want the former so set its default value to CURRENT_TIMESTAMP

[ScoobyDont]

Hi, 

Sorry lost you a little, this is the db

 

 

Obviously it has'insert_time' and 'update_time' which is all well and good

 

I am struggling to understand or get to grips with what I have to do to keep 'update_time' static for front end as like my image in OT

 

What I want is:-

 

Previous States

status 1 = updated @ 20.09

 

Current State

status 2 = updated @ 20.11

 

But currently if I changed the status to 2 I would get this results

 

Previous States

status 1 = updated @ 20.11

 

Current State

status 2= updated @ 20.11

 

Would I be best to do 'fwrite' to a new file or is there another way

[Barand]

CREATE TABLE `status_log` (
  `status_log_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) DEFAULT NULL COMMENT 'id of item owning the status',
  `status` tinyint(4) DEFAULT NULL,
  `changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`status_log_id`)
) ;

When the status changes, add a new record

mysql> select * from status_log;
+---------------+---------+--------+---------------------+
| status_log_id | item_id | status | changed             |
+---------------+---------+--------+---------------------+
|             1 |       1 |      1 | 2017-02-28 15:03:00 |
+---------------+---------+--------+---------------------+

-- add new record

mysql> INSERT INTO status_log (item_id, status) VALUES (1,2);

-- now you have

mysql> select * from status_log;
+---------------+---------+--------+---------------------+
| status_log_id | item_id | status | changed             |
+---------------+---------+--------+---------------------+
|             1 |       1 |      1 | 2017-02-28 15:03:00 |
|             2 |       1 |      2 | 2017-03-05 21:08:43 |
+---------------+---------+--------+---------------------+

[ScoobyDont]

Genius..Thank you