namnam0107 namnam0107 - 7 months ago 13
SQL Question

MySQL PHP Update table when there is new record

I have 3 tables. In the table that called

goal
, i have 6 fields :

id (foreign key that references to users table's id),
rid,
gid (PK),
goal,
date (format is 2016-04-18 02:32:47),
result (default value="No").


In another table, called
result
, I have 4 fields:

rid (PK),
id (foreign key that references to users table's id),
distance,
calories,
date(format is 2016-04-18 02:32:47).


Users table

username,
id (PK).


The date attribute in these two tables will be stored as NOW().

I want to check whether the user has met his goal or not from checking the result table.

Just like, if his goal is set on 2016-04-16 and distance is 123m, I'll will check the result table, and if there is a distance value that equals to 123m from the starting date (2016-04-16).

If in the future (after 2016-04-16), the result table has distance 123m, then he has met his goal, the result attribute in goal table will be changed to "Yes". But i don't know how to do this, any help please?

Answer

you could create a TRIGGER for after inserts into result table to check to see if the result matches your condition(s)...then do the UPDATE the goal.result to 'YES' based on that rid like sample below...

CREATE TRIGGER check_goal AFTER INSERT on result
FOR EACH ROW
BEGIN
  IF EXISTS(SELECT 1 FROM goal G 
            WHERE NEW.distance >= G.goal 
            AND NEW.rid = G.rid
            AND NEW.date >= G.date) THEN
    UPDATE goal SET result = 'YES' WHERE rid = NEW.rid;
  END IF;
END/

sqlfiddle of Trigger in action

Also, your goal should be a number so that you can compare (if it's in meters) just use the integer part like 123...don't store it as 123m otherwise you'll have things like 1.5km which will be really hard to compare.

P.S. for this sqlfiddle i set / as delimiter, you might have to change that to whatever delimiter you're using.

Comments