Pulling data from database in version 6 tpl files

[Si]

This used to work in version 5 to help me display the worldpay future pay agreement number for my clients on their account details page:

 

<input type="text" name="gatewayid" value="{php} $userid = $this->_tpl_vars['clientsdetails']['userid']; $result = mysql_query("SELECT gatewayid FROM tblclients WHERE id=$userid"); $data = mysql_fetch_array($result); $gatewayid = $data["gatewayid"]; echo $gatewayid; {/php}" readonly="readonly" class="inputbox readonly width_2" />

 

Does anyone know how I could replicate this in v6 templates ?

[sentq]

create new file inside " /includes/hooks/ " folder name it anything.php

copy the following hook code inside it

<?php
use Illuminate\Database\Capsule\Manager as Capsule;

function hook_worldpayAgreementNumber($vars){

   $_SESSION['uid'] = intval($_SESSION['uid']);

   $data = Capsule::table('tblclients')
                       ->where('userid', $_SESSION['uid'])
                       ->select('gatewayid')
                       ->get();

   return array("worldpaygatewayid" => $data['0']->gatewayid);

}
add_hook("ClientAreaPage", 1, "hook_worldpayAgreementNumber");

 

now update your input field to look like the following:

<input type="text" name="gatewayid" value="{$worldpaygatewayid}" readonly="readonly" class="inputbox readonly width_2" />

[Si]

Thanks for the reply. I did the above, but it returned this error.

 

Fatal error: Uncaught exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'userid' in 'where clause' (SQL: select `gatewayid` from `tblclients` where `userid` = 4226) (SQL: select `gatewayid` from `tblclients` where `userid` = 4226)'

 

?

 

The table names etc are correct and clients have agreement numbers in there. Some do, most don't.

Edited August 12, 2015 by Si

[sentq]

try it now:

<?php
use Illuminate\Database\Capsule\Manager as Capsule;

function hook_worldpayAgreementNumber($vars){

   $_SESSION['uid'] = intval($_SESSION['uid']);

   $data = Capsule::table('tblclients')
                       ->where('id', $_SESSION['uid'])
                       ->select('gatewayid')
                       ->get();

   return array("worldpaygatewayid" => $data['0']->gatewayid);

}
add_hook("ClientAreaPage", 1, "hook_worldpayAgreementNumber");

[Si]

Gotcha. Brilliant. Thanks for the help.

 

I'll never understand why v6 was moved to these hooks - I'll never understand that.

 

Thank you again.

[sentq]

as {php} is deprecated we use action hooks instead, action hooks is much safe and stable.also database query was handled with new database functions available in v6+

[Si]

Just an update on this:

I didn't receive any new orders all day. Then, on live chat I was walking a customer through the order process and the shopping cart crashed at the point of adding the domain to the hosting account. 'An Error Occurred'.

 

I removed everything one by one, that I had done in the past 2-3 days, and nothing fixed it. I deleted the hook file from this thread, and voila, the cart started working again. I don't know where or how there is a conflict, but it is happening somewhere.

 

Any ideas?

[sentq]

can you see any errors in Admin Area > Utilities > Activity Log?

[Si]

No, no errors showing. I had hooks, php and sql enabled.

[SeanP]

On what client page are you trying to display the data? In what template was the original code placed? You may need to use a more specific hook point.

[Si]

on the clientareadetails.tpl page. Any help you can give would be appreciated.

[sentq]

disable hook debug option no real need for it here, and try to place the same hook file again and test!

 

you are using WHMCS V6 right?

 

<?php
use Illuminate\Database\Capsule\Manager as Capsule;

function hook_worldpayAgreementNumber($vars){

   $_SESSION['uid'] = intval($_SESSION['uid']);

   if ($_SESSION['uid']!='0'){
       $data = Capsule::table('tblclients')
                           ->where('id', $_SESSION['uid'])
                           ->select('gatewayid')
                           ->get();

       return array("worldpaygatewayid" => $data['0']->gatewayid);
   }

   return array("worldpaygatewayid" => "N/A");

}
add_hook("ClientAreaPage", 1, "hook_worldpayAgreementNumber");

?>

 

This hook works in my installation without any issues

[Si]

the code works, and displays the future pay number in the clientareadetails.tpl page, but when I add a domain the cart shows

"An Error Occurred" message.

 

There's nothing showing in the logs (with or without hook debug enabled) to show anything.

 

using comparison order form.

Edited August 13, 2015 by Si

[Si]

you are using WHMCS V6 right?

 

 

This hook works in my installation without any issues

 

Yes, using version 6. What cart are you using out of interest?