This is question I have still not been able to resolve, so maybe I need to be clearer in what I need.
I have a datasource which I receive and upload to a MySQL database each month. I cannot alter the data although it may not be in the most helpful format. It looks like this:
Ref Action Date/Time User Location
00123 Create 01:02:12_09:13:13 J Jones Home
00456 Create 01:02:12_09:13:13 J Jones Home
00123 Revise 03:02:12_15:20:01 A Smith Home
00789 Create 01:02:12_09:13:13 J Jones Home
00123 Delete 05:02:12_10:51:45 B Halls Home
x 1000's
It tracks events that occur against a reference number, which is generated by the first event (the Create event). These events occur at varying intervals and are done by various people in various locations.
Using the above example I need to be able to loop through the data for a particular month, pull out a reference number and its 'Create' event, then locate all the other events for that ref number.
I then need to be able to manipulate this information, for example calculate the time difference between Create and Revise, and then Revise and Delete, and who did them, where.
I need to be able to this for all the ref numbers created in the month or a date range.
So I am hoping I can create a query that can do this - find a ref number, find the other events, string them together in some way - so that in the end I have new data -
Ref Number, Time of Create, Create By Who, Time of Revise/By Who, Time of Delete etc
It would be useful if this new data could be stored in a new table, I would think(?)
Can this be done as a query or perhaps combination of query and PHP (arrays?)
Okay, the first stage is to transform this data in a MySQL table, and you will have something like
ref integer,
action enum('create','revise','delete'), -- ordered by life cycle: create first, delete last
ts timestamp,
user varchar(32), -- or maybe user_id
loc varchar(32), -- again maybe location_id, or ENUM
So to pull out an event given its reference
SELECT * FROM mytable WHERE ref = 123 ORDER BY action;
For date ranges you can use
WHERE ts BETWEEN 'date1' AND 'date2'
To get time differences,
SELECT TIMEDIFF(b.ts, a.ts) AS delta, b.*
FROM mytable AS a
JOIN mytable AS b ON (a.action = 'CREATE' AND a.ref = b.ref)
WHERE a.ref = 123;
Finally to denormalize the table:
SELECT _create.*,
TIMEDIFF(_revise.ts - _create.ts) AS revise_delta,
TIMEDIFF(_delete.ts - _delete.ts) AS delete_delta,
FROM mytable AS _create
LEFT JOIN mytable AS _revise ON (_create.ref = _revise.ref
AND _create.action = 'CREATE' AND AND _revise.action = 'REVISE' AND ***)
LEFT JOIN mytable AS _delete ON (_create.ref = _delete.ref
AND _create.action = 'CREATE' AND AND _delete.action = 'DELETE' AND ***)
;
Here * is some way of identifying the unique relation between a CREATE event and the corresponding DELETE event.
If refs are unique, then AND *** is not needed (* equals to True).
For example if the refs are recycled every month, and it never happens that an event may span two months, you can impose that the year and month of _create should be the same as those of _revise; that and the equality of .ref establish a biunivocal match.
Otherwise it gets much more complicated, and I'd try creating a VIEW that for each CREATE event selects the COALESCE of NOW() and the datetime of the earliest CREATE event with that same ref but ts greater or equal to the current. This way you identify a "window" in which events with that ref should be attributed to that specific CREATE. But this is based on the hypothesis that it never happens that
00123 CREATE 01-NOV-2012 Jack
00123 CREATE 04-NOV-2012 Jill
00123 DELETE 05-NOV-2012 Joe <-- which event is this one?
Well, to get field values for a particular action:
$db_connection = mysqli_connect();//Have DB variables here
$ref = ;//Ref number here e.g 123
$action = '';//action to check for here e.g create
$query = "SELECT * FROM yourtable WHERE ref=".$ref." AND action=".$action."";
$q = mysqli_query($db_connection,$query);
while($row = mysqli_fetch_asocc($q)){
//The variables are in the $row array with each on an index based on a column name
}