example mysql table named "Profile" data as below
----------------------
user_id | max_capacity
----------------------
100 3
101 5
103 10
another mysql table named "Active" data as below
-------------------------
user_id | active_capacity
-------------------------
101 7
103 5
please note table "Active" column - active_capacity is dynamic its values are changing every second by another script.
I want to determine if my user_id #101 has hits/over the active_capacity of its allowed max_capacity which is 5 in "Profile" table. Please note the "Active" table showing that user_id #101 its active_capacity is 7 now.
I would like to to define maximum hit value like $hit=2 if my user_id #101 hits/over its max_capacity for first time then I want to store its value into another table named "Danger"
storing the user_id #101 because its active_capacity is 7 now which is over its max_capacity
mysql table "Danger" data as below
--------------
user_id | hits
--------------
101 1
after 5 minute the user_id #101 its active_capacity in mysql table "Active" decrease down to 4
after another 5 minutes the user_id #101 its active_capacity in mysql table "Active" increase up to 10 so here I want to update the "Danger" table user_id #101 column hits to "2"
please note: i want to update the "danger" table column hits value only after the active_capacity from "Active table" decrease down and again its active_capacity value increase up and hits/over its max_capacity value from "Profile" table
this script will work from cron jobs. I need advise, what is most efficient way to achieve the above goal.
Thanks.
You can use MySQL's on duplicate key update functionality to do this as one statement. This assumes user_id is a primary key on the Danger table. The SQL also assumes user_id is unique on the other two tables:
insert into Danger (
user_id, hits
) select
p.user_id, 1
from
Profile p
inner join
Active a
on a.user_id = p.user_id
where
a.active_capaciy > p.max_capacity
on duplicate key update set
hits = hits + 1;