lundi 27 juin 2016

UPDATE if last record contains same value else INSERT

I am trying to store some notifications in my database. I am receiving GPS data from some machines and I want to make a notification (record in mysql) in next scenarios: 1. IF received gps data is false -> "GPS data error" 2. IF gps coordinates match some zone -> "Machine is in zone XY" 3. IF gps coordinates don't match zone -> "Machine is out of zone XY"

Table looks like this:

ID  MachineID     note        Datetime              FirstSignal
............................................................................
1         5       in zone     2016-06-25 18:13:40   2016-06-25 18:02:40      
2         5       gps error   2016-06-25 18:19:40   2016-06-25 18:14:40                
3         5       in zone     2016-06-25 18:23:40   2016-06-25 18:20:40
etc.

So lets say I get data from machine each minute. I would like to make a notification like this: INSERT new notification if last notification for that machines isn't the same like this:

INSERT into notifications (MachineID, note, Datetime, FirstSignal) VALUES (5, 'in zone', now(), now())

If last entry has the same notification I would just like to upload it like this:

UPDATE notifications SET Datetime=now()
WHERE ID = (SELECT MAX(ID) FROM notifications WHERE MachineID=5)

So how would I combine this two queries? I know that a common answer to INSERT OR UPDATE query is to have a syntax: INSERT ... ON DUPLICATE KEY UPDATE ... with which I would have to make a unique key pair made of 'MachineID' and 'note'. But this is not acceptable for me since I want to keep entry with same note, e.g. few entries back. I guess I would have to use some IF condition on note, but I don't really know how. Any help appreciated.

EDIT So I came up with idea to use transactions. However, I have some syntax errors that I can't seem to figure out on my own. I tried this:

IF (SELECT note FROM notifications 
    WHERE MachineID='$podatki[mid]' ORDER BY Datetime DESC LIMIT 1) = '$notification'
THEN 
     UPDATE notifications SET Datetime=now() 
     WHERE ID = (SELECT MAX(ID) FROM notifications WHERE MachineID='$podatki[mid]') 
ELSE 
     INSERT into notifications (0, MachineID, note, Datetime, FirstSignal) VALUES (0, '$podatki[mid]', '$notification', now(), now()) 
END IF;

I tried also this:

IF EXISTS(SELECT * FROM notifications WHERE ID = (SELECT MAX(ID) FROM notifications WHERE MachineID='$podatki[mid]') AND note='$notification')
            THEN
            UPDATE notifications SET Datetime=now() WHERE ID = (SELECT MAX(ID) FROM notifications WHERE MachineID='$podatki[mid]')
            ELSE
            INSERT into notifications (0, MachineID, note, Datetime, FirstSignal) VALUES (0, '$podatki[mid]', '$notification', now(), now())
            END IF  

With both I receive error:

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT * FROM notifications WHERE ID = (SELECT MAX(ID) FROM notificati' at line 1     

Aucun commentaire:

Enregistrer un commentaire