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.
Create a new Custom File from Database Pane, by simply right-click on Custom Files, and choose Add File,
Enter the following info:
File name: getdatafromdatabase.php
Caption: Get Data from Database
Include common files: enabled
Path: (leave it blank)
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
?>
Re-generate ALL the script files,
Access the generated page from http://localhost/demo2026/getdatafromdatabase (login by using username admin and password master).
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.
Create a new Custom File from Database Pane, by simply right-click on Custom Files, and choose Add File,
Enter the following info:
File name: getquantityorderdetails.php
Caption: Get Quantity of Order Details
Include common files: enabled
Path: (leave it blank)
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;
?>
Re-generate ALL the script files,
Access the generated page from http://localhost/demo2026/getquantityorderdetails (login by using username admin and password master).
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:
Create a new Custom File from Database Pane, by simply right-click on Custom Files, and choose Add File,
Enter the following info:
File name: querybuilderlooprecordset.php
Caption: Query Builder Loop Recordset
Include common files: enabled
Path: (leave it blank)
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: