0

Because of the limitation of a wordpress plugin I am using I cannot update tables from the website when they are created from JOIN statement. So I created a bigger table with data of all tables. But I want that when a website user edits a field in this table then the update should be reflected to the "joined" tables.

For example I have these tables (in reality the tables have ~20 fields and the big one has ~50 fields):

Students:

+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | John |  34 |
|  2 | Mary |  21 |
+----+------+-----+

Courses:

+----+--------+----------+
| id | course | semester |
+----+--------+----------+
|  1 | math   | 1st      |
|  2 | geo    | 2n       |
+----+--------+----------+

Registrations:

+----+------------+------+-----+-----------+--------+----------+
| id | student_id | name | age | course_id | course | semester |
+----+------------+------+-----+-----------+--------+----------+
|  1 |          1 | John |  34 |         1 | math   | 1st      |
|  2 |          2 | Mary |  21 |         2 | geo    | 2n       |
+----+------------+------+-----+-----------+--------+----------+

So when user for example a user edits the name of a student then the edit has to be made both to the Registrations table and to the Students. But it has to be done via a trigger or a stored procedure in phpmyadmin because I dont want to change the functionality of the wordpress plugin. How I can create this kind of trigger or stored procedure in Students or Courses?

  • 1
    For such large tables you should use views not separate tables. view support all CRUD operations to their respective tables. view would exactly behave like a table, you don't need to change any code, just create the view with same name of your large table and remove the table. You should be good. To test whether it will work for you or not, do it in local or beta environment first. – Jitendra Yadav Sep 6 at 15:25
  • I tried with a view but I couldn't update it. Probably is plugins limitation... wpdatatables.com/documentation/front-end-editing/… MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID. – netdev Sep 6 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.