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