The Trap of String Concatenation in MySQL UPDATE Queries
The code snippet you are looking at fails because it treats numeric columns as strings during the concatenation process. In your query, the value $points is wrapped in single quotes: ' . $points . '. This forces MySQL to interpret the current number of points not as an integer, but as a string literal.
When MySQL evaluates the expression '5' + 1, it attempts to convert the string '5' to a number. While this conversion often succeeds for simple digits, the syntax SET points = 'value' + 1 can lead to unexpected behavior depending on how the client driver handles the binding or if the column definition is not strictly defined as an integer. More critically, mixing string concatenation operators with arithmetic in a way that doesn't explicitly cast the value often results in the database storing a result that looks like 1 if the concatenation logic inadvertently creates a malformed expression or if the variable $points is being treated as part of a larger string operation before the addition.
The core issue is that you are assigning a string to a numeric column. Even if the math seems correct on the surface, MySQL's type coercion rules can be tricky when strings are involved in arithmetic expressions within an UPDATE statement. If $points is empty or null, '5' + 1 becomes null + 1, which results in NULL. However, the specific symptom of changing to 1 suggests that the database might be interpreting the operation as a string replacement followed by a partial evaluation, or the variable $points itself is being treated as an empty string in certain contexts, leading to ' ' + 1 behavior which can resolve oddly depending on SQL mode.
The robust solution is to perform arithmetic on numeric values without wrapping them in quotes. Remove the single quotes around $points. The corrected query should look like this:
mysql_query("
UPDATE member_profile
SET points = points + 1
WHERE user_id = '$userid'
");
In this approach, points is treated as a column reference. MySQL retrieves the current integer value from the column, adds 1 to it using standard SQL arithmetic, and then writes the new integer back to the row. This bypasses any string conversion issues entirely. If you need to add a specific variable rather than just 1, ensure that variable is also unquoted:
$newPoints = $currentPoints + 1;
mysql_query("UPDATE member_profile SET points = '$newPoints' WHERE user_id = '$userid'");
While the second example above works, it is generally less efficient than the first because it fetches the current value, performs the math in PHP (or your application logic), and then writes it back. The database handles the increment operation natively and atomically in the first approach, which is preferred for performance and consistency.
Security Implications of Manual Concatenation
Beyond the arithmetic error, your original code presents a significant security vulnerability. You are directly interpolating variables $points and $userid into the SQL string using single quotes. This practice opens the door to SQL injection attacks. An attacker could manipulate the $userid input to alter the query logic entirely, potentially modifying data for other users or deleting records.
Modern development practices strongly advise against using the deprecated mysql_query function alongside manual string interpolation. The mysql_* extension has been removed in PHP 7.0 and is considered insecure. You should migrate to PDO or MySQLi with prepared statements. Prepared statements separate the SQL logic from the data, ensuring that user inputs are always treated as data and never as executable code, regardless of how they are concatenated or inserted into the query structure.
Here is how you would write this safely using PDO:
$stmt = $pdo->prepare("UPDATE member_profile SET points = points + 1 WHERE user_id = :user_id");
$stmt->execute(['user_id' => $userid]);
This method ensures that even if $userid contains malicious characters, the database parses them strictly as data. It also removes the need to worry about quoting numeric variables manually, as the driver handles the type casting correctly.
Conclusion on Increment Logic
The lesson here extends beyond just this specific query. When updating a numeric column in MySQL, always reference the column name directly in the SET clause if you intend to modify its current value. Avoid wrapping existing column values or numeric variables in quotes unless you are explicitly converting them to strings for a non-arithmetic purpose. By letting the database engine handle the arithmetic on integer columns, you ensure predictable results and maintain type safety throughout your application's data layer.