How to update products tblcustomfieldsvalues

[whattheserver]

So figured I'd share this here so if anyone else runs into this issue with their module and updating their custom fields values for products they have a reference.

I was trying to update the freeradius server module(WHMCS) one to auto add assigned forwarded port and random MAC_addy placeholder. I realized it was a pita to update this despite having the variable I wanted to set easily accessible.

I had bought this as it looked promising but didn't end up working for me as I had hoped. I might have been doing it wrong though. I might still end up using it. It also looks more flexible if you have lots of different products with customfields and values you need to update. I only had one product that needs this. So why I left a link here as you may prefer its approach.
https://whmcstricks.com/whmcs-api-update-customfields/

Anyhow for reference.

 

Custom Fields Name:
Port
MacAddress

Custom Fields "fieldid":
Port: 2
MacAddress: 1

Example of the database structure to give you an idea.

SELECT * FROM `tblcustomfields`

id    type        relid    fieldname    fieldtype    description    fieldoptions    regexpr    adminonly    required    showorder    
1     product     3     MacAddress     text                                 1     0000-00-00 00:00:00     0000-00-00 00:00:00
2     product     3     Port         text       

 

SELECT * FROM `tblcustomfieldsvalues`
 

id    fieldid    relid    value    created_at    updated_at
1     2     2         2019-12-04 14:46:23     2019-12-04 14:46:23
2     1     2         2019-12-04 14:46:23     2019-12-04 14:46:23

SELECT * FROM `tblhosting`
 

id     userid     orderid     packageid     server     regdate
2     1     2     3     2     2019-12-04

create:

        Capsule::table('tblcustomfieldsvalues')
            ->where('relid', '=', $params["serviceid"])
        ->where('fieldid', '=', 2)
            ->update(array(
                'value' => $Port,
            ));

        Capsule::table('tblcustomfieldsvalues')
            ->where('relid', '=', $params["serviceid"])
        ->where('fieldid', '=', 1)
            ->update(array(
                'value' => $MacAddress,
            ));

Terminate:
 

        Capsule::table('tblcustomfieldsvalues')
            ->where('relid', '=', $params["serviceid"])
        ->where('fieldid', '=', 2)
            ->update(array(
                'value' => '',
            ));

        Capsule::table('tblcustomfieldsvalues')
            ->where('relid', '=', $params["serviceid"])
        ->where('fieldid', '=', 1)
            ->update(array(
                'value' => '',
            ));

This works amazing and now I can update these values dynamically and on create/unsuspend terminate/suspend as needed.

 

Edited December 5, 2019 by whattheserver