PHP - CSV to MySQL to frontend, and back to CSV, best encoding / string function practices?

I have a scenario where CSV files are being used to maintain data (products) in a MySQL database. Basically the steps that the client follows to maintain products is as follows:

• Adds items to the CSV file in predefined columns
• Uploads the CSV in backend
• PHP script iterates over CSV data
• Data is validated / cleaned and sent to MySQL database

Same applies in reverse when the client edits, adds or removes items, however this can also be done straight from the backend. I have had issues with certain characters not showing properly in the database or in the downloaded spreadsheet, so my question is:

Generally, what is a good set of encoding or string functions to apply to the data when adding and retrieving it in the form of a CSV file?开发者_高级运维 My MySQL connection encoding is UTF-8, however I have tried saving the CSV file with a UTF-8 character set, but it seems Excel automatically applies the default Windows-1252 encoding to it, breaking all ym UTF-8 characters.

Applications like Open Office.org prompt you for the import character set, and whe set to Unicode UTF-8 it displays fine. Obviously one cannot rely on the client always using Excel or Open Office, so what is a good solution for PC / CSV > MySQL > back to PC / CSV work?

All suggestions welcomed and appreciated.

Simon

So to answer after the comments. Have a good understanding of http://www.joelonsoftware.com/articles/Unicode.html and look at implementing a datamapper pattern, example article about it here - http://www.devshed.com/c/a/PHP/Implementing-the-Data-Mapper-Design-Pattern-in-PHP-5.

If i understand correctly, you are trying to open, edit, and save CSV files using Excel. This is generally a bad idea for a whole range of reasons, including the fact that it does not allow you to specify the details of a csv file you export to.

A fail-safe, yet more cumbersome method might be to load the data into Access instead, and work with the data there. Access allows 'export to text file' using more advanced settings, like encoding and delimiter.