Search the Community
Showing results for tags 'DATABASE'.
-
Hi guys, I have a module that won't load it's settings due to a url that it looks at being changed. Can I manually change all the old url strings in the database to reflect the new url? If so what query can I run to do this? Ie, search for all "oldurl" instances and replace with "newurl"
-
Hello, is there a module or a way to create a MySQL user & database automatically for the newly created cPanel account? Thanks, Davor
-
I noticed in the database that the table tbllog_register has over 89,000 records in it, and tblactivitylog has over 12,000. I have Settings -> General Settings -> Limit Activity Log set to 1000, which apparently doesn't appear to be working for tblactivitylog, although the documentation does say, "Non client related entries will remain." I took a look at Utilities -> System -> System Cleanup, and none of the tools there appear to apply to the table tbllog_register. On that page, it shows only 2094 log entries in the Activity Log, so does that mean there are 10,000 non-client-related entries in the table? The Activity Log takes a long time to load even on my dedicated server, so it seems like a good idea to get control of those tables. What's the best way to keep the tables tblactivitylog and tbllog_register from getting huge? -
I have a serious problem with a recently purchased template. Right now I don't have time to wait for your painful support, or to turn back. For this reason I need to change the id number of a product group. I have two groups of Hosting products. An old one with no products currently in use (id 1) Another current one that is the one that I want to convert to id=1 I have seen the following after doing an analysis of the tables. tblproductgroup id tblproducts gid is associated to tblproductgroup.id tblproducts_slug group_id is associated to tblproductgroup.id tblproductslinks gid is associated to tblproductgroup.id tblproductconfigoptions gid is associated to tblproductgroup.id I only want to exchange the id, since due to the bad programming of the template, I have to do it to avoid a tremendous UX problem, and the fastest way is for the current Hosting group to come out first in the drop-down list. Is something left in the pipeline before executing the sql that will change the corresponding id? Doubts tblproducts_slugs_tracking slug_id ??? tblproduct.id ? Best regrads.
-
Hello, I want to get the orderId from the database based on the invoiceId directly from database using Capsule. I don't get anything, also with try catch I don't get any errors. Here is the code that I'm using: use WHMCS\Database\Capsule; $orderId = Capsule::table('tblorders')->select('id') ->where('invoiceid', $invoiceId) ->first(); The $invoiceId is for example 74 and there is a record in the database table tblorders that contains this invoiceId. Can anyone see what I'm doing wrong? Thanks, Davor
-
provisioning Provisioning - Posting customfields to external DB
Jeronk posted a topic in Developer Corner
Good day, I require assistance with custom fields, I need to pass 4 custom fields inputs to an external database, array( '$params[‘customfields’][‘Username’]', '$params[‘customfields’][‘Email’]', '$params[‘customfields’][‘Cellphone Number’]', '$params[‘customfields’][‘Password’]', ); how do I use the above custom fields in a SQL query? Any assistance would be greatly appreciated, thanks. -
When I try to add anything new to my WHMCS like a client, a product or even a server the row is created on the database but not add the ID and then I can't edit. The id stay as 0. Any idea of how to solve this?
-
I've migrated my WHMCS from a cPanel panel to Plesk. The problem now is that I can't see the orders but they are on the database. Any idea of how to solve it?
-
how to get user request module info into modules hook folder structure: mymedia | | +---- hooks.php +---- mymedia.php +---- index.php created products: product-01: product-02: ------------------- ------------------- | Name: media 01 | | Name: media 02 | | Module: mymedia | | Module: mymedia | | | | | |_________________| |_________________| here, TableName: tblproducts ----------------------------------------------------- | id | name | servertype | ...... | ------------------------------------------------------- | 1 | media 01 | mymedia | ...... | | 2 | media 02 | mymedia | ...... | | .. | ........... | ....... | ...... | ----------------------------------------------------- hooks.php: <?php add_hook("AcceptOrder", 0, "mymedia_acceptOrder"); function mymedia_acceptOrder() { // how to get request product id or name here $mediaIDorName = ?? } ?> NB: how to get user request module info into modules hook.
-
Hi, We've just moved over to using WHMCS as our billing system and we have a number of existing customers who we charge through our Stripe account. I can get their cus_ and pm_ ID's out of Stripe and I would like to be able to add them directly to client records. I'm guessing that this has to be done through the database. Looking at our MySQL db, I think that I have to add JSON into the form {"customer":"cus_XXXXXXXXXXX","method":"pm_XXXXXXXXXXXXX"} into tblcreditcards.card_data The JSON appears to have been encrypted as a BLOB . I'm happy writting MySQL queries to inject the data but how do i correctly encrypt the JSON package so that WHMCS will read it correctly. Thanks Kieran
-
Where can I edit the queries that dictate which orders are displayed in the orders.php page in Admin area? orders.php?status=Active shows active order4s but also shows that are suspended which I don't want. So how can I edit the query that creates this page display? Thanks in advance for any help.
-
Hi, by mistake I have deleted the Email Verification Template from the database and now the "Send/Resend Verification Email" doesn't work. I have looked in the 'emailtemplates.sql' in the installation folder, but there isn't such template/table called 'Email Verification Template'... How can I get it working/restore again? P.S. The send/resend buttons are there, in the admin as in the client area as well, but they do nothing and the 'Activity Log' is empty nor any error warnings/messages on the site or logs. Thanks
-
Hello, I have a question on how to connect to hosted website whmcs database on my local pc, I am using MySQL Workbench, MySQL Query Browser to connect to cPanel database. Usually for a normal website database I am able to connect peacefully, but for whmcs it's on the other way round. For WHMCS, I filled in the hostname based on ip, username, password, and database name correctly but still doesn't let me. Is there a specific encryption or accessibility to grant user to connect whmcs database? Thank you!
-
Hi, I want to perform some tasks that require the service id on a hook(specifically invoice creation) the paramters there are limited all I get is the invoice id. Is there a way to fetch service id using that invoice id any indication/help/solution would be highly appreciated. The hook must trigger either before or after invoice generation. Thanks. 👍
-
First off thanks to the original thread for the inspiration for the first script. I was unable to reply there with my bash version of this. I also had another script so I figured id share all it here. #!/bin/bash ## Author: Michael Ramsey ## Objective clean 2 weeks prior to tbllog_register ## Please ensure that the database user used for this has both Select and Delete permissions for the whmcs database ## save as whmcs-register-clean.sh chmod it to 700 ## example cronjob ## 0 0 * * 0 /bin/bash /home/username/scripts/whmcs-register-clean.sh >/dev/null 2>&1 date2w=$(date --date='2 week ago' +"%Y-%m-%d") /usr/bin/mysql -h "localhost" -u "database_username" "-pPasswordhere" -e "DELETE FROM tbllog_register WHERE created_at < $date2w" database_name The second one is more for privacy based companies that want to prevent any client IP's from being unnecessarily saved and prevent DB growth for really large companies with active clients logins. We run privacy based logless VPN service and wanted to ensure there is no IP's ever saved of any of our clients. This included the client and support portal in addition to the VPN nodes and freeradius auth server. More information about this setup is in our blog post here > https://whattheserver.com/whmcs-client-last-login-ip-log-clearing-script/ #!/bin/bash ## Author: Michael Ramsey ## Objective clear last login IP and host from tblclients ## Please ensure that the database user used for this has both Select and Delete permissions for the whmcs database ## save as whmcs-client-clear-ip.sh chmod it to 700 ## example cronjob ## * * * * * /bin/bash /home/username/scripts/whmcs-client-clear-ip.sh >/dev/null 2>&1 #Clear clients last login IP address in table tblclients > ip,host /usr/bin/mysql -h "localhost" -u "database_username" "-pPasswordhere" -e "UPDATE tblclients SET ip = '', host = ''" database_name #Clear Order Ipaddress in table tblorders > ipaddress /usr/bin/mysql -h "localhost" -u "database_username" "-pPasswordhere" -e "UPDATE tblorders SET ipaddress = ''" database_name whmcs-register-clean.sh whmcs-client-clear-ip.sh -
Does anyone use MariaDB 10.3 on version 7.7.1 without any problems?
-
Hello, our cronjob made a dataabse backup. The cronjob works fine, but databse backup needs round about 45minutes. 11.04.2018 08:44 Cron Job: Backup Complete 11.04.2018 08:44 Cron Job: FTP Backup - Completed Successfully 11.04.2018 08:44 Cron Job: Backup Generation Completed 11.04.2018 08:43 Cron Job: Starting Backup Zip Creation 11.04.2018 08:43 Cron Job: Backup Database Dump Complete 11.04.2018 08:00 Cron Job: Starting Backup Database Dump 11.04.2018 08:00 Cron Job: Starting Backup Generation 11.04.2018 08:00 Cron Job: Completed Daily Automation Tasks 11.04.2018 08:00 Domain Sync Cron: Completed 11.04.2018 08:00 Automated Task: Starting WHMCS Updates 11.04.2018 08:00 Automated Task: Starting Domain Expiry 11.04.2018 08:00 Automated Task: Starting Client Status Update 11.04.2018 08:00 Automated Task: Starting Server Usage Stats 11.04.2018 08:00 Automated Task: Starting Email Marketer Rules 11.04.2018 08:00 Automated Task: Starting Delayed Affiliate Commissions 11.04.2018 08:00 Automated Task: Starting Inactive Tickets 11.04.2018 08:00 Automated Task: Starting Fixed Term Terminations 11.04.2018 08:00 Automated Task: Starting Overdue Terminations 11.04.2018 08:00 Automated Task: Starting Overdue Suspensions 11.04.2018 08:00 Automated Task: Starting Cancellation Requests 11.04.2018 08:00 Automated Task: Starting Domain Renewal Notices 11.04.2018 08:00 Automated Task: Starting Invoice & Overdue Reminders 11.04.2018 08:00 Automated Task: Starting Credit Card Charges 11.04.2018 08:00 Cron Job: Late Invoice Fees added to 0 Invoices 11.04.2018 08:00 Automated Task: Starting Late Fees 11.04.2018 08:00 Automated Task: Starting Invoices 11.04.2018 08:00 Automated Task: Starting Product Pricing Updates 11.04.2018 08:00 Automated Task: Starting Currency Exchange Rates 11.04.2018 08:00 Cron Job: Starting Daily Automation Tasks 11.04.2018 08:00 Domain Sync Cron: Starting Is it normal? Is it possible to seperate the cronjob databse to another cronjob that will make a backup maybe at 3 o'clock?
-
Hi, i think that "Classes" should get it´s own menu item under developers.whmcs.com. it is somewhat hidden in the footer which is an underrepresentation of something that makes life really easier. Michael
-
Hello WHMCS Community! I'm building a custom page on WHMCS that needs to retrieve all the products on a specific product group. In this step so far so good. But, these products have their names translated into several languages using the dynamic translation and different currency prices, looking into WHMCS Classes Model, I found on Product Model the methods that can retrieve this information in the current language and currency set. I build a simple loop that catches all the products ID: $command = 'GetProducts'; $productgroupid = 12; $postData = array( 'gid' => $productgroupid, ); $results = localAPI($command, $postData); $productsId = array(); foreach ($results['products']['product'] as $product) { $productsId[] = $product['pid']; } After this, I passed this products ID's to Product Model to get the information that I need: foreach ($productsId as $key => $value) { $products[$key] = [ 'name' => Product::find($value)->getNameAttribute(), 'description' => Product::find($value)->getDescriptionAttribute(), 'price' => Product::find($value)->pricing(), ]; } This works perfectly well, but the princing() method return only the default currency. I make a test outside the loop to get only one product and tested with different currencies, and the result is that what I expected: $userid = (isset($_SESSION['uid']) ? $_SESSION['uid'] : ""); $currencyid = (isset($_SESSION['currency']) ? $_SESSION['currency'] : ""); // $currencyid = 5; $currency = getCurrency($userid, $currencyid); var_dump($currency); var_dump(Product::find(1))->pricing(); (I have try without the currency variable, and after this passing the ID of currency, and the both cases works good) Only on the loop the princig() method do not retrieve the correct currency. I'm making a mistake in the loop that is not allowing the currency to recover properly? -
Hello, in order to investigate an internittent issue, our SQL admin asked me the table name where we could find every product price - the product price we are looking for falls in the 'other category' any help is appreciated
-
When my server does backup, mysql service is stopped. So, for few minutes my WHMCS gives a bad Critical Error Could not connect to the database. It lasts just a couple of minutes, and it's deep inthe night... but it's no nice. A custom page with a message like "We're doing system maintenance, for your security... come back soon" (or something like that) would be much better. Any idea on how to do it?
-
Hello Everyone, Hope you guys doing good, WHMCS going so far, but some more tweaks need to be done! How to show logged in Hello and client name to wordpress or html website hompage, as per old thread, some guys did this in older version of whmcs ex - 4.0 whmcs. but many things has been depreciated in ver - 7. You can check the same below code and thread As per older thread - https://forum.whmcs.com/showthread.php?48351-Possible-to-show-quot-Welcome-back-name-quot-otherwise-show-login-form <?php include("dbconnect.php"); if ($_SESSION['uid']) { $result = mysql_query("SELECT firstname FROM tblclients WHERE id='".$_SESSION['uid']."' LIMIT 1;"); while($row = mysql_fetch_array($result)) echo "<p id='login'><span class='loginwelcome'>".$_LANG['welcomeback'].', '.$row['firstname'].': | <a href="/YOURWHMCS/clientarea.php">'.$_LANG['clientareatitle'].'</a> | <a href="/YOURWHMCS/logout.php">'.$_LANG['logouttitle'].'</a></span></p>'; } else{ echo '<form action="https://www.YOURSITE.com/YOURWHMCS/dologin.php" method="post" name="loginform" class="header">Client Login: <input name="username" type="text" class="login" id="username" value="username" size="15""> <input name="password" type="password" id="password" value="xxxxx" size="15""> <input type="hidden" name="token" value="add-your-token-here" /> <input type="image" src="/images/go.gif" width="25" height="19" align="absmiddle"> </form>'; } ?> i tried this code by making a new php file in whmcs directory. Used - from include("dbconnect.php"); to include("init.php"); But there is something more need to done! please suggest and share your idea. Thanks Madan Malhotra
-
Hello WHMCS Community! I come here to try to clarify a doubt. I developing a custom provisioning module and I want to use the Eloquent Model from Laravel to manipulate the database over the Query Builder which is mentioned in the documentation. Are there any restrictions on use? Regards!
-
Hello, I want to display a user's recent support tickets in some client area pages. I have created a hook following the instructions here and changes the variables. The hook works fine and it displays the results I need. However, it displays the recent tickets of all users. here is my code <?php function hook_displaysupporttickets($vars){ $output = ""; $query = "SELECT * FROM tbltickets ORDER BY date DESC LIMIT 0,6"; $result = mysql_query($query); while ($data = mysql_fetch_array($result)) { $tid = $data["tid"]; $date = $data["date"]; $replyid = $data["replyid"]; $title = $data["title"]; $department = $data["department"]; $date = fromMySQLDate($date); $output .= '<p class="mytitleannoun"><i class="fa fa-file-text-o" aria-hidden="true" style="margin-right:15px;"></i><a style="margin-right:15px;" href="/viewticket.php?tid='.$tid.'">'.$title.'</a><span>'.$department.'</span><span>'.$tid.'</span><font color="#b3b3b3">'.$date.'</font></p>'; } return array("displaysupporttickets" => $output); } add_hook("ClientAreaPage", 1, "hook_displaysupporttickets"); I need to change the code so that it displays only the tickets of the logged in user (each user will see only his own tickets). Any ideas? Thank you.
-
Looks like * in latest version (v6.1.1) wasn't updated since 2013, only the following Database query i was wondering about: update_query("tblaffiliates",array("visitors"=>"+1"),array("id"=>$aff)); It needs to be updated to use Capsule instead of the old query function
