How to use Capsule on old Whmcs hooks

[twister]

I have upgraded Whmcs from 6 version to 7.7 one and want to use Laravel's Capsule option instead of classic SQL syntax but i run into issues on execution.

Tried WHMCS documentations but can't seems to fetch capsule array properly.

<?php

/** old hook **/
add_hook('AdminClientServicesTabFields', 1, function($vars) {

$PID = $vars['id'];




        $result = mysql_query("SELECT name,mid FROM tblping where id = '$PID'");
               while ($data = mysql_fetch_array($result)) {
                      $var1 = $data['name'];
                      $var2 = $data['mid'];
                     }


    return [
        'var1' => $var1 ,
        'var2' => $var2,
    ];

});

<?php

/** new hook **/
add_hook('AdminClientServicesTabFields', 1, function($vars) {

use Illuminate\Database\Capsule\Manager as Capsule;
use WHMCS\Database\Capsule;

$PID = $vars['id'];


$result = Capsule::table(tblping')->select('name','mid')->where->('id', '=', '$PID')->get();

while ($data = mysql_fetch_array($result)) {
                      $var1 = $data['name'];
                      $var2 = $data['mid'];
                     }


    return [
        'var1' => $var1 ,
        'var2' => $var2,
    ];

});

[brian!]

untested, but try....

<?php

use WHMCS\Database\Capsule;

add_hook('AdminClientServicesTabFields', 1, function($vars) {

	$PID = $vars['id'];
	$result = Capsule::table('tblping')->select('name','mid')->where->('id',$PID)->get();
	$var1 = $result['name'];
	$var2 = $result['mid'];
	return array ('var1' => $var1,'var2' => $var2);
});

if result contains mutiple rows, then you could foreach loop through it, but I assumed that it didn't.

[twister]

unfortunately does not work...

[brian!]

there's an error in the $result line...

$result = Capsule::table('tblping')->select('name','mid')->where('id',$PID)->get();

my fault for copying your original line and not writing it from scratch. 🙂

alternatively, you could try...

<?php

use WHMCS\Database\Capsule;

add_hook('AdminClientServicesTabFields', 1, function($vars) {

	$PID = $vars['id'];
	$result = Capsule::table('tblping')->select('name','mid')->where('id',$PID)->get();
	$var1 = $result->name;
	$var2 = $result->mid;
	return array ('var1' => $var1,'var2' => $var2);
});

[twister]

the array is displaying now but i don't get any data. i am positive the query is correct as i can get data using old mysql syntax. 

[twister]

to get the return to display i need to use 

return [
        'var1' => $var1,
        'var2' => $var2,
    ];

 

[twister]

<?php

use WHMCS\Database\Capsule;

add_hook('AdminClientServicesTabFields', 1, function($vars) {

	$PID = $vars['id'];
	$result = Capsule::table('tblping')->select('name','mid')->where('id',$PID)->get();         
$array=	json_decode(json_encode($result), True); 

$var1 = $array[0]['name'];
	$var2 = $array[0]['mid];

return [ 'var1'=> $var1, 'var2'=> $var2]
	
});


this solution works for me. Thank you for your help 

[cosmin.bosutar]

You need to use "first()" and not "get()"

On 9/6/2019 at 5:50 PM, twister said:

Capsule::table('tblping')->select('name','mid')->where('id',$PID)->get();

This tine should be like below:

Capsule::table('tblping')->select('name','mid')->where('id',$PID)->first();

 

This is because the default response of a query is a collection and since you need only one element you need to use "first" to return only the first element of a collection.