The Fundamental Conflict Between Application Logic and SQL Syntax
The error you are encountering stems from a classic misunderstanding of how database queries are constructed versus how application code executes. In your attempt to handle empty strings for integer columns, you wrapped the function call inside curly braces {} within the SQL string and returned the literal text 'null'. This approach fails on two distinct fronts: syntactic structure and semantic data types.
First, let's look at the syntax error reported by PostgreSQL: ERROR: syntax error at or near ";". While the error message points to a semicolon (likely due to line wrapping in your log), the root cause of the failure is the placement of {toDB(...)} inside the query string. In standard SQL, you do not invoke application functions directly within the query definition unless those functions exist natively within the database engine itself or are provided by an extension like PL/pgSQL. Your $db->setQuery method likely handles prepared statement binding for placeholders (like ? or :name), but it does not parse and execute arbitrary PHP code blocks embedded inside the SQL string. The curly braces {} you used in your example suggest a specific template syntax, but even if that syntax were supported by your wrapper class, returning the string 'null' is incorrect for an integer column.
Returning Strings vs. Sending NULL Values
The most critical logical error in your code is returning the string 'null'. In SQL, there is a fundamental difference between the text "null" and the concept of NULL (the absence of data). When you return 'null' from your PHP function, you are sending a text value containing four characters. If you assign this to an int column in PostgreSQL, the database will attempt to cast that string to an integer, which results in a type mismatch error or stores it as NULL only if the column is explicitly defined with a default constraint or if you use specific casting syntax like ::int.
To properly represent missing data for integer columns, you must send the actual SQL keyword NULL, not a quoted string. This means your function should return a value that translates to the null token in the final query. However, since you are mixing PHP logic with SQL strings, manual string manipulation is fragile and prone to injection risks if not handled perfectly.
The Proper Solution: Application-Level Coalescing
The cleanest way to solve this problem is to handle the data conversion entirely within your application logic before constructing the query string, or by leveraging database-specific constructs like COALESCE or NULLIF if you prefer keeping the logic in SQL.
If you must stick to building the query string manually, modify your function to return an empty string that your application layer can interpret as NULL, or better yet, simply omit the value binding for empty inputs and handle it via prepared statement placeholders. However, a more robust approach involves checking the input in PHP and ensuring the final value passed to the database is either the integer (if provided) or the specific token for null.
Here is how you might refactor the logic to ensure type safety:
function normalizeInt($value) {
// Trim whitespace and check for empty strings, including 'null' as a string literal
$trimmed = trim($value);
if ($trimmed === '' || strtolower($trimmed) === 'null') {
return null; // Return PHP null to be bound as SQL NULL
}
// Ensure the input is treated as an integer
return (int)$trimmed;
}
$name = "Veo ve";
$age = '10';
$month = '';
$year = '';
// Constructing the query with proper NULL handling
$query = "INSERT INTO tr_view (name, age, month, year) VALUES (?, ?, ?, ?)";
// Bind values where empty strings become PHP nulls
$bindings = [
$name,
normalizeInt($age),
normalizeInt($month), // Returns null here
normalizeInt($year) // Returns null here
];
$db->setQuery($query, $bindings);
$result = $db->query();
Using SQL Functions for Cleaner Code
If your database abstraction layer supports it, moving the logic into the SQL query itself using COALESCE or NULLIF can be more declarative. For example:
INSERT INTO tr_view (name, age, month, year)
VALUES (
'Veo ve',
COALESCE(NULLIF('10', ''), NULL),
COALESCE(NULLIF('', ''), NULL),
COALESCE(NULLIF('', ''), NULL)
);
This approach explicitly tells the database: "If the input is empty or equals 'null' (as a string), treat it as SQL NULL." Note that NULLIF returns NULL if two expressions are equal. By comparing the empty string to itself, we can trigger the NULL result.
Why Manual String Concatenation Fails
Your original code attempted to manually concatenate strings like 'null'. This is dangerous because:
- Type Safety: You cannot cast a text string "null" reliably into an integer column without explicit casting (
::int), which might mask logical errors. - SQL Injection Risk: If you ever decide to allow user input directly into the query string without parameter binding, returning dynamic values based on string logic opens the door to injection vulnerabilities.
- Maintainability: Hardcoding the logic of converting empty strings to null inside a PHP helper function that returns a string literal couples your application code to specific SQL dialect behaviors in an opaque way.
By returning a PHP null value or utilizing built-in SQL functions, you ensure that the database receives the correct semantic meaning: "this value is missing," rather than "the user typed the word 'null'."