PHP + SQL Server - How to set charset for connection?
Mixing ISO-8859-1 and Windows-1252 might sound like a minor detail, but in the context of a database connection, it can completely break your data integrity. The core issue you are facing isn't just about which charset the PHP script uses or how mssql_connect is configured; it is about how the client driver translates bytes into characters before they even reach the T-SQL engine.
When your application sends data in ISO-8859-1 and the SQL Server expects Windows-1252, you are encountering a mismatch between the client-side encoding and the server-side collation expectations. Simply setting ini_set('mssql.charset', 'windows-1252') often fails because the older mssql extension (deprecated in later PHP versions) does not always respect this directive for character conversion during the initial handshake or specific column types. Furthermore, SET CHAR_CONVERT OFF is a T-SQL command that stops SQL Server from attempting to guess conversions on the fly, but it doesn't stop the driver from sending malformed bytes if the connection string itself implies an incorrect encoding.
The most reliable way to resolve this is to ensure the connection explicitly agrees on a character set before any data is transmitted. If you are using the legacy mssql_* functions, these functions rely heavily on environment variables and server configuration rather than PHP ini settings for charset negotiation. However, relying on the deprecated mssql extension is a technical debt that usually compounds these encoding headaches.
If you have the flexibility to switch from the legacy mssql extension to the newer PDO or SQLSRV drivers, you gain precise control over character encoding via the connection string. With PDO, you can specify the charset directly in the DSN (Data Source Name). For a Windows-1252 environment, the connection string would look something like this:
$dsn = "sqlsrv:Server=your_server;Database=your_db;charset=utf8";
// Or specifically for Windows-1252 if the driver supports it in your version:
// $dsn = "sqlsrv:Server=your_server;Database=your_db;CharacterSet=WINDOWS-1252";
Note that while SQL Server natively stores data based on its collation (e.g., Latin1_General_CI_AS), the connection channel determines how PHP interprets the input string. If your application strictly requires ISO-8859-1, you must ensure the driver understands that the incoming stream is already in that format and doesn't attempt to re-encode it based on PHP's default internal encoding (which is often UTF-8 or ASCII depending on the OS locale).
The specific scenario of iso-8859-1 vs windows-1252 is tricky because these sets are nearly identical, differing primarily in characters like à and certain accented glyphs. SQL Server's default collation often treats these as distinct. If you attempt to insert data assuming they are the same without explicit conversion flags, you might end up with garbled text or silent corruption.
If you must stick with the legacy mssql_connect function, there is no direct PHP-side switch that forces a charset conversion during the connection phase that overrides the server's default behavior effectively. The solution usually lies in the database side: ensuring the columns you are inserting into have the correct code page defined (which happens at the server level when the column is created) and verifying that the client driver isn't applying an unexpected transformation layer.
In modern development, the path of least resistance is often to normalize the data at the source. If your application logic allows it, converting all incoming strings to UTF-8 before insertion is generally safer than maintaining legacy encodings like ISO-8859-1 or Windows-1252, provided your SQL Server collation supports it (which most modern instances do).
If you are building a new stack or refactoring an existing one, consider using the Laravel framework which handles database connections via PDO by default. Laravel provides robust configuration options for defining connection parameters in config/database.php, allowing you to specify the driver and charset explicitly without relying on deprecated mssql functions. For more details on managing database configurations in modern Laravel applications, resources such as https://laravelcompany.com offer insights into best practices for maintaining clean data pipelines and handling encoding issues within their ecosystem.
Ultimately, the goal is to stop the guessing game between the client driver and the server engine. Explicitly defining the connection charset and, preferably, migrating away from legacy extensions that obscure these details will ensure your special characters remain intact.