PHP/MySQL Insert null values

PHP/MySQL Insert null values

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Why NULL Disappears in Legacy PHP Queries

If you are migrating legacy codebases or maintaining older applications, you might encounter behavior where inserting a NULL value into a database column results in an empty string instead of a true NULL. The root cause is almost always the interaction between how SQL handles type coercion and how PHP's native functions format variables before sending them to the server.

In the scenario provided, the code uses the deprecated mysql_* extension. While this library is no longer recommended for new projects due to security vulnerabilities and lack of support in modern PHP versions, understanding its behavior explains why the insertion fails silently. The specific issue lies in the variable interpolation within the SQL string:

"insert into table2 (f1, f2) values ('{$row['string_field']}', {$row['null_field']});

When $row['null_field'] contains the value NULL, PHP treats it not as a database-specific marker, but as a native PHP null type. When PHP evaluates this inside a double-quoted string without explicit casting or escaping, it often converts the null variable into an empty string ("") before concatenation occurs. Consequently, the SQL statement sent to the server becomes:

INSERT INTO table2 (f1, f2) VALUES ('value', '')

Instead of the intended:

INSERT INTO table2 (f1, f2) VALUES ('value', NULL)

This discrepancy causes the database engine to store an empty string in a column that accepts NULL, potentially corrupting data integrity if the application logic expects the absence of a value rather than a blank one.

The Correct Approach with Legacy Code

To fix this specific issue in existing code, you must explicitly convert the PHP variable into the SQL keyword NULL before interpolation. This can be achieved by casting the variable to a string and checking its value, or by using conditional logic within the query construction.

A robust pattern for handling mixed null and non-null values involves a helper function that sanitizes the input specifically for SQL insertion:

function getSqlValue($value) {
    if ($value === null || $value === '') {
        return 'NULL';
    }
    // Ensure strings are escaped to prevent injection
    return "'" . mysql_real_escape_string($value) . "'";
}

// Usage in the loop
while ($row = mysql_fetch_assoc($results)) {
    $safeString = getSqlValue($row['string_field']);
    
    // If null_field is null, we want 'NULL', otherwise we escape it
    $isNull = ($row['null_field'] === null);
    $safeNull = $isNull ? 'NULL' : "'" . mysql_real_escape_string($row['null_field']) . "'";

    mysql_query("INSERT INTO table2 (f1, f2) VALUES ($safeString, $safeNull)");
}

This approach ensures that the database receives the exact literal NULL keyword when the PHP variable is empty, preserving the intended schema behavior.

Migrating to Modern Standards

While fixing the legacy code works, it adds complexity and technical debt. The real solution is moving away from the mysql_* extension entirely. Modern PHP development uses PDO or MySQLi, which provide parameterized queries (prepared statements). Prepared statements handle data typing and escaping automatically, eliminating the risk of manual conversion errors.

With PDO, you define placeholders in your SQL string:

$stmt = $pdo->prepare("INSERT INTO table2 (f1, f2) VALUES (:string_field, :null_field)");
$stmt->execute([
    'string_field' => $row['string_field'],
    'null_field'   => $row['null_field'] // Passes null directly to the driver
]);

In this architecture, NULL is passed as a native PHP null value. The PDO driver recognizes this type and correctly translates it into the SQL NULL keyword during execution. This separation of data and logic prevents the string interpolation errors seen in the older API.

Conclusion on Data Integrity

The lesson here extends beyond just fixing a specific bug; it highlights a critical distinction between application-level types and database-level types. In legacy systems, developers must manually bridge the gap between PHP variables and SQL expectations. In modern ecosystems, the abstraction layer handles this translation safely. If you are working with https://laravelcompany.com resources or similar modern stacks, leveraging frameworks like Laravel that default to these prepared statement patterns is essential for maintaining data integrity when handling nullable fields.

Our network