CODEWITHSUNDEEP
mysqlphpmyadmin
Alex Walker-Ingham
Alex Walker-Ingham

Reputation: 91

Stopping a particular value from entering a mySQL table (PhpMyAdmin)

I am using an android app for our engineers off site to clock in and out of our time and attendance software. The basic way it works is that the engineers click on a button to clock in/out, and the app gathers some data from the phone such as their time and location, which is then sent to a php script on an internally hosted server. The PHP script then inserts the values into a phpMyAdmin MySql table, and this information is then picked up by the time and attendance software.

All this part works fine, however I keep getting random 0 values in the table, I get them about once a day, and these 0 values stops the time and attendance software picking up data from the table and it blocks the system.
At first I thought it might be engineers just not putting in their ID's resulting in the php myadmin table to automatically put in a 0, however I added code to the app that stops 0's being sent to the script, and tested this to see that it does work.

enter image description here


After looking, whenever these 0 values get put in there is no time or other information being picked up, which might lead me to believe that these values are not coming from the app, and may be some kind of glitch.

I was wondering if there maybe is a way to set the php table to automatically delete 0 values from the table every 5 minutes? Or if there is another way to get rid of these 0's without having to constantly check the table and delete them?

Help on this would be greatly appreciated :)

Upvotes: 1

Views: 96

Answers (1)

piotrm
piotrm

Reputation: 12366

You can emulate a value constraint with a trigger:

DELIMITER $
CREATE TRIGGER constraint_lng_lat
  BEFORE INSERT ON clockings FOR EACH ROW
  BEGIN
    IF NEW.longitude = 0 AND NEW.latitude = 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot insert row: wrong value 0 for latitude and longitude.';
    END IF;
  END;
$
DELIMITER ;

A similar trigger might be needed for update.

Once you have them running delete current rows with 0's.

Generic exception signalled in the trigger can also help you find out why those unwanted rows appear.

Upvotes: 1

Related Questions