Insert Records With Database Capsule

[Eric Fahnle]

Hi everyone! Hope you're all doing great.

We're trying to insert some entries in the following table: "tblproductconfigoptionssub" from a custom page (that we developed) as stated here: https://developers.whmcs.com/advanced/creating-pages/ following all of the mentioned methods here: https://developers.whmcs.com/advanced/db-interaction/

The problem we face is: The custom page inserts the records successfully (as we can tell from the SQL DB). However, when we create a product with this config option in client area, this new entry doesn't appear. If I create it manually from the admin page, the entry appears succesfully. Also, if I create it from the custom page, and later on go to open the config option in admin area, it then appears at the cart.

This makes me believe there is some ORM / Cache keeping up the objects. When we insert the entry manually, the ORM / Cache can't tell there has been a new record added, so it doesn't show it. When something is edited from the admin page, the ORM / Cache reloads all of its records, and it later appears.

An ideal solution for us, would be an instant refresh for the client area, or a way to tell the ORM/Cache to check for newly added entries. We haven't, however, found a way to do it at least until now.

Any help with this would be much appreciated.

Thanks in advance,

Eric

[pRieStaKos]

As you say, you are inserting entries to `tblproductconfigoptionssub`. Have you seen the table relations ?

You have to create or match the entries with all the below:

• tblproductconfiggroups
• tblproductconfiglinks
• tblproductconfigoptions
• tblproductconfigoptionssub
• tblproductgroups
• tblproducts

[Eric Fahnle]

Hi John, thank you for your response!!

I've taken a look at these tables and I still don't understand what's going on...

1. tblproductconfigoptionssub has a reference to tblproductconfigoptions.

For example, a configoption with name "Disk Size" has "40 GB" and "80 GB" suboptions.

MariaDB [DB]> SELECT * from tblproductconfigoptions;
+----+-----+-------------------------+--------------+----------------+-----------------+-------+--------+
| id    | gid   | optionname                  | optiontype | qtyminimum | qtymaximum | order | hidden
+----+-----+-------------------------+--------------+----------------+-----------------+-------+--------+
| 1     |   1     | Disk Size                         | 1                      |          0                 |          0                   |     0       |      0      
+----+-----+-------------------------+--------------+----------------+-----------------+-------+--------+

MariaDB [DB]> select * from tblproductconfigoptionssub;
+----+----------+----------------+------------+--------+
| id    | configid | optionname | sortorder | hidden 
+----+----------+----------------+------------+--------+
| 1     |        1         |         40 GB       |         0           |      0 
| 2     |        1         |         80 GB       |         1           |      0 
+----+----------+------------+-----------+-------------+

"configid" column is the Foreign Key to tblproductconfigoptions

 

2. tblproductconfigoptions has a reference to tblproductconfiggroups.

Continuing the example, let's assume that the configoption "Disk Size" belongs to "VM Options" group.

MariaDB [DB]> SELECT * from tblproductconfiggroups;
+----+------------------------+-------------+
| id    | name                               | description 
+----+------------------------+-------------+
|  1    | VM Options                 |             
+----+------------------------+-------------+

"gid" column in tblproductconfigoptions is the Foreign Key to tblproductconfiggroups

 

3. Finally, the product has a many-to-many relationship with tblproductconfiggroups through tblproductconfiglinks

MariaDB [DB]> SELECT * from tblproductconfiglinks;
+-----+------+-----+
| id      | gid    | pid 
+-----+------+-----+
|  1      |     1     |   1 
+-----+-----+-----+

MariaDB [DB]> SELECT id, name from tblproducts;
+----+--------------+
| id    | name         
+----+--------------+
|  1     | vm1          
+----+--------------+

As a result, "vm1" product has the "Disk Size" configoption with two sub options: "40 GB" and "80 GB" suboptions.

At this point, I insert directly to db a new "Disk Size" suboption:  "100 GB".

insert into tblproductconfigoptionssub(configid, optionname, sortorder, hidden)
values(1, "100 GB",  2, 0);

configid=1 refers to "Disk Size" configoption

So, if I add a suboption record (tblproductconfigoptionssub) with a configoption (tblproductconfigoptions) that is in a group (tblproductconfiggroups) which is associated with a product (tblproductconfiglinks), I think that there is no need to add / update some record in any other table. Also, entering the config options page in admin area, open "VM Options" and clicking "Disk Size" is enough to make suboption "100 GB" appear in client area; No need to add / edit any attribute from the admin area.

Hope I made myself clear,

Thank you very much for your time,

Eric