SQL select query in page.tpl

[Tapeix]

Where you at?
I have created a custom page by adding two files:
> {ROOT}/newpage.php
> {ROOT}/templates/six/newpage.tpl

What do you want to achieve?
I'd like to execute a SQL select query . The easiest way is to do that within the smarty tags from my .tpl page, as I can print those values directly. Since that mysql_query is depreciated, I have to use WHMCS\Database\Capsule. Unfortunately, it seems that Capsule is limited to .php files as I couldn't get it to work inside my .tpl file.  Whenever I try to call PDO from inside the smarty tags, I receive the following error: "Error: Class 'Capsule' not found". 

What is your SQL query?

$query_downloads = mysql_query("
        SELECT B.id, B.title, B.description
        FROM tblproduct_downloads A
        JOIN tbldownloads B ON A.download_id = B.id
        WHERE B.title LIKE \"%SLA%\"
        ");

What is your question?
Well, I'd like to know how I can run this query and print the output in my .tpl file. I know how to write and process SQL queries, but not how I would share the variables between newpage.php and /templates/six/newpage.tpl. The "Interacting with the Database" article does not share similar examples.

I hope someone can point me to the right direction. Thanks.

Edited January 26, 2020 by Tapeix

[Tapeix]

Update: I think I can declare a variable by using this method.

File: {ROOT}/newpage.php
use WHMCS\Database\Capsule;
$clientName = Capsule::table('tblclients')->where('id',$ca->getUserID())->value('firstname');
$ca->assign('clientname', $clientName);

File: {ROOT}/templates/six/newpage.tpl
<p>{$clientname}</p>

However, no output is shown.

Edited January 26, 2020 by Tapeix

[Kian]

First off no one forces you to use Capsule. You can freely implement your own connection for database (raw PDO, MySQLi) but that's a story for another time.

Simply put, .php is where you place all scripts including queries. The .tpl file is where you show or get data. In other words your query must be placed in php and it's result must be passed to the tpl by simply declaring a Smarty variable. In Creating page there's an example.