How to Load Data from Database into Custom Files (v2026)

PHPMaker User Submitted Tips (PHPMaker)
1

Since v2026, we will NOT use the approach that will disable the Include common files option of the Custom Files anymore, if we want to get/load data from database. For more info, you may read the reason here.

I created this topic in order to expose so many advantages of Symfony framework that we can optimize in order to work with Database and Custom Files.

Let’s say we use demo2026 project to implement it.

  1. Create a new Custom File from Database Pane, by simply right-click on Custom Files, and choose Add File,

  2. Enter the following info:

    • File name: getdatafromdatabase.php
    • Caption: Get Data from Database
    • Include common files: enabled
    • Path: (leave it blank)

  3. Click on Code (Server Events, Client Scripts and Custom Templates) tab, and then click on Content under Custom Templates → Table-Specific → Custom Files, and put the following code::

    <?php

echo "Custom File with Include common files option";

$sql = "SELECT Model FROM models"; // define your SQL
$stmt = ExecuteQuery($sql); // execute the query
$value = ""; // initial value
if ($stmt->rowCount() > 0) { // check condition: if record count is greater than 0
  while ($row = $stmt->fetchAssociative()) { // loop
    $value .= $row["Model"] . ""; // in case the result returns more than one record, display it and separated by line break
  } // end loop
  echo "Here is the Models list: " . $value; // display the result
} else { // if there are no result
  echo "No record found."; // display the message
} // end of check condition

?>

  4. Re-generate ALL the script files,

  5. Access the generated page from http://localhost/demo2026/getdatafromdatabase (login by using username admin and password master).

2

The next step, we will try to optimize the QueryBuilder in order to get data based on TWO criteria in WHERE clause.

Again, we will use demo2026 project to implement it.

  1. Create a new Custom File from Database Pane, by simply right-click on Custom Files, and choose Add File,

  2. Enter the following info:

    • File name: getquantityorderdetails.php
    • Caption: Get Quantity of Order Details
    • Include common files: enabled
    • Path: (leave it blank)

  3. Click on Code (Server Events, Client Scripts and Custom Templates) tab, and then click on Content under Custom Templates → Table-Specific → Custom Files, and put the following code::

    <?php

use Doctrine\DBAL\Types\Types;

$orderID = 11077;
$productID = 41;
$qb = QueryBuilder();
$qb->select('Quantity')
  ->from('orderdetails')
  ->where('OrderID = :order_id')
  ->andWhere('ProductID = :product_id')
  ->setParameter('order_id', $orderID, Types::INTEGER)
  ->setParameter('product_id', $productID, Types::INTEGER)
  ->setMaxResults(1);
$quantity = $qb->executeQuery()->fetchOne(); // Safe, returns first column of first row
echo "Quantity of OrderID " . $orderID . " and ProductID " . $productID . " is: " . $quantity;

?>

  4. Re-generate ALL the script files,

  5. Access the generated page from http://localhost/demo2026/getquantityorderdetails (login by using username admin and password master).

ExecuteRow() vs. QueryBuilder()
3

Another QueryBuilder implementation in Custom Files is to load Recordset and loop through it in order to display the result (similar to my first post above).

Using demo2026 project, you may follow these steps:

  1. Create a new Custom File from Database Pane, by simply right-click on Custom Files, and choose Add File,

  2. Enter the following info:

    • File name: querybuilderlooprecordset.php
    • Caption: Query Builder Loop Recordset
    • Include common files: enabled
    • Path: (leave it blank)

  3. Click on Code (Server Events, Client Scripts and Custom Templates) tab, then click on Content under Custom Templates → Table-Specific → Custom Files, and put the following code:

    <?php

use Doctrine\DBAL\Types\Types;

$id = 1;
$users = QueryBuilder()
  ->select('FirstName, LastName, Username')
  ->from('employees')
  ->where('EmployeeID > :id')
  ->setParameter('id', $id, Types::INTEGER)
  ->executeQuery()
  ->fetchAllAssociative();

// Var_Dump($users);
$users_list = '';
$i = 1;
foreach ($users as $row) {
  $users_list .= $i . " -> " . $row["LastName"] . ", " . $row["FirstName"] . " (" . $row["Username"] . ")<br>";
  $i++;
}
echo "Users List without Nancy: <br>" . $users_list;

?>

  4. Re-generate ALL the script files,

  5. Access the generated page from http://localhost/demo2026/querybuilderlooprecordset (login by using username admin and password master).

4

Besides QueryBuilder, we can also optimize the Connection object that belongs to DBAL in order to get recordset and loop through it, in order to display the result.

In this example, we will use two parameters in SQL Query; each of param will be bound to its data type. Let's say we want to display the Orders records which OrderID is greater than 11070 (Integer) and CustomerID is not equal to 'ERNSH' (String).

Again, we will use demo2026 project, you may follow these steps:

  1. Create a new Custom File from Database Pane, by simply right-click on Custom Files, and choose Add File,

  2. Enter the following info:

    • File name: GetDataUsingDBALConnection.php

    • Caption: Get Data Using DBAL Connection

    • Include common files: enabled

    • Path: (leave it blank)

  3. Click on Code (Server Events, Client Scripts and Custom Templates) tab, then click on Content under Custom Templates → Table-Specific → Custom Files, and put the following code:

    <?php 	
    use Doctrine\DBAL\Types\Types;
    $id = 11070;
    $cust_id = 'ERNSH';
    $conn = Conn();
    $sql = 'SELECT * FROM orders WHERE OrderID > :id AND CustomerID <> :cust_id';
    $result = $conn->executeQuery(                    
                    $sql,                     
                     ['id' => $id, 'cust_id' => $cust_id],
                     [Types::INTEGER, Types::STRING]
             );    
    $rows = $result->fetchAllAssociative(); 	
    $rows_list = ''; 	
    $i = 1; 	
    if (count($rows) > 0) { 		
       foreach ($rows as $row) { 		 
           $rows_list .= $i . ". ID: " . $row["OrderID"] . ", Date: " . $row["OrderDate"] . ", Customer: " . $row["CustomerID"] . "<br>"; 		 
            $i++; 		
        } 		
        echo "Orders List: <br>" . $rows_list; 	
    } else { 		
        echo "Data not found";
    }
?>

    Re-generate ALL the script files,

  4. Access the generated page from http://localhost/demo2026/getdatausingdbalconnection (login by using username admin and password master).

5

Assume you want to use QueryBuilder with condition in WHERE Clause as follows:

SELECT Model FROM models WHERE Model IN ('Z4 Sdrive35i', 'M5', 'X6', 'X3');

How did you do that by using QueryBuilder?

To answer such question, then let's use demo2026 project to do that.

  1. Create a new Custom File from Database Pane, by simply right-click on Custom Files, and choose Add File,

  2. Enter the following info:

    • File name: GetArrayDataFromWhereInClause.php

    • Caption: Get Array Data From Where In Clause

    • Include common files: enabled

    • Path: (leave it blank)

  3. Click on Code (Server Events, Client Scripts and Custom Templates) tab, then click on Content under Custom Templates → Table-Specific → Custom Files, and put the following code:

    <?php

    use Doctrine\DBAL\Types\Types;
    use Doctrine\DBAL\ArrayParameterType;

    $trademark_id = 2; // <-- please adjust this value to see it in action, available values: 1, 2, or 3

    $trademark_name = GetTrademarkName($trademark_id);

    echo "Record Count of Models from Trademark <strong>" . $trademark_name . "</strong> is <strong>" . GetRecordCountFromModels($trademark_id) . "</strong><br>";

    $models = QueryBuilder()
      ->select('Model')
      ->from('models')
      ->where('Model IN (:model)')
      ->setParameter('model', GetArrayFromModels($trademark_id), ArrayParameterType::STRING)
      ->executeQuery()
      ->fetchAllAssociative();

    // Var_Dump($models);
    // Var_Dump(GetArrayFromModels($trademark_id));

    if (count($models)) {
        $models_list = '';
        $i = 1;
        foreach ($models as $row) {
          $models_list .= "#" . $i . ": " . $trademark_name . " -> " . $row["Model"] . "<br>";
          $i++;
        }
        echo "<br>Models List for <strong>" . $trademark_name . "</strong>: <br>" . $models_list;
    } else {
        echo "<br>Sorry, data not found.";
    }
?>

  4. Still on Code (Server Events, Client Scripts and Custom Templates) tab, then click on Global Code under Server Events → Global → All Pages, and put the following code:

  5. use Doctrine\DBAL\ArrayParameterType;

function GetTrademarkName($trademark_id) {
  $qb = QueryBuilder();
  $qb->select('Trademark')
    ->from('trademarks')
    ->where('ID = :id')
    ->setParameter('id', $trademark_id, Types::INTEGER);
  $val = $qb->executeQuery()->fetchOne(); // returns single value
  return ($val != "") ? $val : "-";
}

function GetArrayFromModels($trademark_id) {
  $qb = QueryBuilder();
  $qb->select('Model')
    ->from('models')
    ->where('Trademark = :trademark')
    ->setParameter('trademark', $trademark_id, Types::INTEGER);
  $rows = $qb->executeQuery()->fetchFirstColumn(); // returns array of string
  return $rows;
}

function GetRecordCountFromModels($trademark_id) {
    $qb = QueryBuilder();
    $qb->select("COUNT(Model)")
        ->from("models")
        ->where("Model IN ( :model )")
        ->setParameter("model", GetArrayFromModels($trademark_id), ArrayParameterType::STRING)
        ->setMaxResults(1);
    $val = $qb->executeQuery()->fetchOne(); // Safe, returns first column of first row
	return $val;
}

  6. Re-generate ALL the script files,

  7. Access the generated page from http://localhost/demo2026/getarraydatafromwhereinclause (login by using username admin and password master).

The result is something like this:

Record Count of Models from Trademark BMW is 4

Models List for BMW:
#1: BMW -> Z4 Sdrive35i
#2: BMW -> M5
#3: BMW -> X6
#4: BMW -> X3
6

Hi

Add this to the global code and use it.

function KayraExecuteQuery(string|QueryBuilder $sql, array $params = [], Connection|string|null $c = null, int $pageSize = 0, int $offset = -1): array|false
{
    if ($c === null) {
        $c = Conn("DB");
    }
    $conn = ($c instanceof Connection) ? $c : Conn($c);

    try {
        if (is_string($sql)) {
            $trimmedSql = trim($sql);
            if (stripos($trimmedSql, "SELECT") === 0) {

				$sqlWithPagination = $sql;
				if ($pageSize > 0) {
					$sqlWithPagination .= " LIMIT " . (int)$pageSize;
					if ($offset > -1) {
						$sqlWithPagination .= " OFFSET " . (int)$offset;
					}
				}
				$stmt = $conn->prepare($sqlWithPagination);

            } else {
                $queryBuilder = $conn->createQueryBuilder();
                $queryBuilder->select('*')->from('(' . $sql . ')', 'temp_table');

                if ($offset > -1) {
                    $queryBuilder->setFirstResult($offset);
                }
                if ($pageSize > 0) {
                    $queryBuilder->setMaxResults($pageSize);
                }
                $stmt = $conn->prepare($queryBuilder->getSQL());
            }
        } else {
            $queryBuilder = $sql;

            if ($offset > -1) {
                $queryBuilder->setFirstResult($offset);
            }
            if ($pageSize > 0) {
                $queryBuilder->setMaxResults($pageSize);
            }
            $stmt = $conn->prepare($queryBuilder->getSQL());
        }

        // bind پارامترها
        foreach ($params as $key => $param) {
            $paramType = null;
            if ($param instanceof \DateTime) {
                $paramType = \Doctrine\DBAL\Types\Types::DATETIME_MUTABLE;
            } elseif (is_int($param)) {
                $paramType = \Doctrine\DBAL\ParameterType::INTEGER;
            } elseif (is_string($param)) {
                $paramType = \Doctrine\DBAL\ParameterType::STRING;
            }
            if (is_int($key)) {
                $stmt->bindValue($key + 1, $param, $paramType);
            } else {
                $stmt->bindValue($key, $param, $paramType);
            }
        }

        $result = $stmt->executeQuery();
        $data = $result->fetchAllAssociative();
        return [
            'status' => true,
            'error' => null,
            'result' => $data
        ];


    } catch (Exception $e) {
		$errorMsg = $e->getMessage();
		LogError("KayraExecuteQuery error: " . $errorMsg, ['file' => __FILE__, 'line' => __LINE__]);
        return [
            'status' => false,
            'error' => $errorMsg,
            'result' => null
        ];
	}
}


function isDbResultValidFormat($dbResult) {
    return (
        is_array($dbResult) &&
        isset($dbResult['status'], $dbResult['result']) &&
        $dbResult['status'] === true &&
        is_array($dbResult['result'])
    );
}

Brief Explanation:

This code provides a wrapper function KayraExecuteQuery for executing database queries safely with automatic pagination support and parameter binding, using Doctrine DBAL.

Key features:

  1. Flexible input: Accepts SQL as a string or a QueryBuilder object.
  2. Connection handling: Automatically resolves the database connection; accepts a connection object or a connection name.
  3. Pagination logic:
    • If the SQL is a SELECT statement, it appends LIMIT/OFFSET directly.
    • If it's a non-SELECT query (e.g., complex subqueries), it wraps the SQL in a subquery and applies setFirstResult()/setMaxResults() via QueryBuilder.
  4. Safe parameter binding: Automatically detects parameter types (DateTime, int, string) and binds them with the correct type.
  5. Error handling: Catches exceptions, logs them with LogError(), and returns a structured array with status, error, and result keys.
  6. Validation helper: isDbResultValidFormat() checks if the returned array is valid (status true, result exists and is an array).

Return format:

[
    'status'  => true/false,
    'error'   => null or error message,
    'result'  => array of rows or null
]

In short: It's a unified, safe, and pagination-aware query executor that standardizes DB responses and simplifies error handling across your project.
And...

$sql = "SELECT Model FROM models";
$params = [];
$dbResult = KayraExecuteQuery ($sql, $params, "DB");


$models = isDbResultValidFormat($dbResult) 
	? $dbResult["result"] 
	: [];


foreach ($models as $row) {
	// ...
}

Enjoy it. :wink: