Advanced

MySql Query

MySQL query app empowered administrator to fire various queries in MySQL on basis of plan status like on Active,Hold,Expire.
Different features embedded in it are as follows:

  1. Query on same database.
  2. Query on different databases.
  3. Admin can use tokens into query.
  4. Admin can set preferences for the app's use.
  5. Admin can restrict the execution of queries depending on the user's subscription status.
  6. Fire multiple queries at a time.

Administrators can create various sql queries using this app. For example:-

UPDATE Query:- update #__table_name set field_name = [[SELECT_TOKEN_FROM_PAYPLANS_TOKEN_LIST]]

DELETE Query:- delete from #__table_name where field_name = [[TOKEN_FROM_PAYPLANS_TOKEN_LIST]])

INSERT Query:- insert into #__table_name (field_one,field_two) values ([[TOKEN_FROM_PAYPLANS_TOKEN_LIST]] , [[TOKEN_FROM_PAYPLANS_TOKEN_LIST]])

Administrators can use this app to execute various different queries. Administrator can accomplish various scenarios using MySql query app. Some case studies are here:-

Case 1:- Suppose administrator has business of coaching center which provides various courses. Administrator decided to use two components. First component is PayPlans and second component is used for course management system. Now, each plan of PayPlans represent one course. There are three courses available at coaching center.

**Course A **=> $100
**Course B **=> $200
**Course C **=> $300

Now suppose there are four tables:-

1) Table 1:- Plan Table of PayPlans

plan_id title published visible description details params
201 Course A 1 1 BBA plan related details | plan parameters
202 Course B 1 1 BSC plan related details | plan parameters
203 Course C 1 1 MSC plan related details | plan parameters

2) Table 2:- Course details table of course management system

course_id course_name course_duration course_manager
1001 BBA 2 Years Mr. Ahmad
1002 BSC 3 Years Mr. Rmesh
1003 MSC 2 Years Mr. Suman

3) Table 3:- User(students) details table of PayPlans

user_id user_name user_email user_mobile user_address
101 ABC abc@abc.in 1234567890 abcdefghijk
102 XYZ xyz@xyz.in 1234567890 abcdefghijk
103 PQR pqr@pqr.in 1234567890 abcdefghijk
104 LMN lmn@lmn.in 1234567890 abcdefghijk
105 JGH jgh@jgh.in 1234567890 abcdefghijk
106 IJK ijk@ijk.in 1234567890 abcdefghijk

4) Table 4:- Mapping able of course and students details

course_id user_id
1001 101
1002 102

Administrator decided that when any user subscribes plan 'Course A', then user(student) will automatically assign 'BBA' course. This implies that user become student of BBA course. If user subscribes plan 'Course B' then user will become student of 'BSC' and if user subscribe plan 'Course C' then user will become student of 'MSC'. The mapping of plan and course can be represented as :-

Course A => 'BBA'
Course B => 'BSC'
Course C => 'MSC'

Now, administrator has to create three 'Mysql Query app' according to each plan.
For plan 'Course A':-

  1. Set 'Select Plan For Application' = 'Course A'
  2. Set 'Parameters' as => 'On Subscription Active' => insert into #__course_user_map (course_id, user_id) values (1001, [[SUBSCRIPTION_USER_ID]])
    Similarly for plan 'Course B':-
  3. Set 'Select Plan For Application' = 'Course B'
  4. Set 'Parameters' as => 'On Subscription Active' => insert into #__course_user_map (course_id, user_id) values (1002, [[SUBSCRIPTION_USER_ID]])

When user subscribes plan:-

Now user(user_id = 104) comes on administrator's site and subscribe plan 'Course A', then on subscription activation, 'MySql Query App' works and insert value into #__course_user_map table. After subscription of user (user_id = 104) become active then schema of #__course_user_map table will be as follows:-

course_id user_id
1001 101
1002 102
1001 104