Update MySQL using HTML Form and PHP

Update MySQL using HTML Form and PHP

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

Stefan Bogdanescu

Founder & Senior Architect

Debugging Dynamic UPDATE Queries in Legacy PHP

You have successfully populated your form fields with data from the anstalld table, but the moment you hit "Update," MySQL throws a syntax error near '1'. This specific error message is a classic symptom of how PHP handles input when constructing dynamic SQL strings. The root cause isn't with your HTML structure or the initial SELECT query; it lies entirely within the logic of the UPDATE statement generation.

When you retrieve values from the database using mysql_fetch_array(), MySQL returns data as a string. However, when a user types into an input field and submits the form, the resulting $_POST variable is also treated as a string. The critical distinction here is that one of your fields likely contains a numeric value—perhaps the employee name or ID—but the database column expects an integer type for the WHERE clause condition.

In your provided code snippet:

$sql = mysql_query("UPDATE anstalld SET mandag = '$mandag', ... WHERE namn = '$namn'");

You are blindly concatenating variables directly into the query string. If a field like $mandag contains a value like 1, and you fail to quote it properly in the context of the database schema, or if one of your columns (like namn) is actually numeric but being treated as text, MySQL attempts to parse the resulting string. If your database expects an integer for the primary key or a specific column, yet your query sends a quoted string that evaluates incorrectly, or vice versa, the parser gets confused.

The error "syntax near '1' at line 1" often indicates that the SQL engine is trying to interpret a number as part of a keyword or failing to match the expected data type for a column involved in the SET or WHERE clause. For instance, if your database has a column defined as INT, but your query sends '1' (a string), MySQL might handle it loosely, but if you are trying to use that same value where a different operator expects an integer without quotes, the parser fails immediately.

However, there is a more fundamental issue at play here: SQL Injection and Data Integrity. The current implementation assumes that whatever the user types or the database holds will fit perfectly into a single string without validation. If $mandag contains special characters like a single quote ('), the query breaks entirely. For example, if a user enters O'Brien, the resulting SQL becomes:

UPDATE anstalld SET mandag = 'O''Brien', ... WHERE namn = 'John'

If your code does not explicitly escape these characters using mysql_real_escape_string() (though this extension is deprecated), the query will fail with a syntax error.

To resolve this, you must ensure that every variable inserted into the SQL string is properly escaped before concatenation. Here is how the update logic should look to handle potential special characters and data types safely:

if(isset($_POST['update'])) {
    $namn = mysql_real_escape_string($_POST['mandagid']); // Note: Variable name mismatch in original code
    $mandag = mysql_real_escape_string($_POST['mandagid']); 
    // ... escape all other variables
    
    // Ensure you are using the correct column names for the SET clause
    // and the correct column for the WHERE clause to avoid ambiguity.
    
    $sql = "UPDATE anstalld SET mandag = '$mandag', tisdag = '$tisdag', 
            onsdag = '$onsdag', torsdag = '$torsdag', fredag = '$fredag' 
            WHERE namn = '$namn'";
            
    $retval = mysql_query($sql, $conn);
}

Notice the discrepancy in your original code where you define $namn from $_POST['namnid'] but the input field is named mandagid. This mismatch would result in an empty string or undefined variable being used for the WHERE clause, potentially causing the update to affect all rows or fail entirely depending on the column definition. Always verify that the key you retrieve from $_POST matches the form input name exactly.

Furthermore, relying on the deprecated mysql_* extension is a significant technical debt. This extension was removed in PHP 7.0. While your immediate goal is to fix the syntax error, migrating to MySQLi or PDO is non-negotiable for any production environment. These modern drivers offer prepared statements, which eliminate the risk of SQL injection and handle data type coercion automatically.

Using a prepared statement would look like this:

$stmt = $conn->prepare("UPDATE anstalld SET mandag = ?, tisdag = ?, onsdag = ?, torsdag = ?, fredag = ? WHERE namn = ?");
$stmt->bind_param("sssssi", $mandag, $tisdag, $onsdag, $torsdag, $fredag, $namn);
$stmt->execute();

This approach separates the data from the structure of the query. The database driver handles the quoting and type casting internally, ensuring that a value like 1 is treated as an integer where appropriate and not as a string literal that could break syntax parsing. This separation is the only robust way to ensure your UPDATE queries behave consistently regardless of user input or underlying data types.

If you are maintaining legacy code, fix the immediate issue by ensuring every variable in your SET clause matches its database column definition (integer vs. string) and escape all inputs before concatenation. But plan a migration to prepared statements immediately to secure your application against injection attacks and syntax errors caused by unpredictable data.

Our network