update from 3 tables

+1 qoley vision · November 3, 2015
hello, i have a problem, i not professional about database, i have develop a website from php then i stuck on database. already 3 days stuck from it. now i need a help.

i have 3 table.
status 1=active
status 0= deactive

table name: users

idnamestatuslicense
1john1
2crown1mui_K56U-PLBU-2R39-D62I

table users(license) only link with table service_license(license_key)


table name: service_license
idclient_idlicense_key
51mui_K56U-PLBU-2R39-D62I
61mui_Y4J5-51D6-8XCD-UFY9


table client_order (table client_order(id) on link with table_service(id))
idclient_idproduct_idservice_typestatusexpires_at
517licenseactive2014-11-11T00:49:04+08:00
617licenseactive2016-11-11T00:49:04+08:00


my request is: if any license has been expired, the status from table users will change to zero.

Post a Reply

Replies

Oldest  Newest  Rating
0 Monty Mason · November 11, 2015
You could create a trigger for that.

https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

Programming logic should be fairly straightforward, but really depends on the exact requirements. Based off of your original post, you could try and launch the trigger daily (or a couple of times a day) to take in the system date and time and match that with the stored value of "expires_at" in the "tableclient_order" table.

Or you could also (not sure about this one) try and do this on the PHP front creating a simple function that would interface with the database and compare dates and times. Should the date and or time exceed, the status value within the respective table(s) can be updated.
  • 1

SQL & Databases

107,015 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator