Daniel Vachon
asked on
SQL collation with php
Hi
I want to start of by saying that I'm very new to SQL and PHP, I've done a bit of research for this but I usually manage the network so some concepts are very new for me!
Now here's my situation:
We've purchased a program (from a schoolboard, not an actual company so no support is available) that works off a web page. It uses php with a sql DB.
We were not told the requirements so I made the setup with Windows server 2012, IIS and sql 2012(free edition), but the programmer built this on server 2008R2, apache and mysql and this is creating a few issues.
His PHP pages are encoded with ANSI but once installed it, I needed to switch the PHP pages to UTF-8 since we use french characters ( é, è, à, ç, etc...), this solved the web page issue but for the DB, since he uses MySQL, is collation is Latin1 and mine is French_CI_AS so all french characters ( é, è, à, ç, etc...) in the DB comes out wrong (é,Ã, ç, é,lè) and same thing when we export the data to word/excel.
Now I've managed to create a query to change existing errors but want to fix this permanently.
Do I simply need to change the collation on the SQL DB or should I think about creating a new server with the recommended settings ( which seems more complicated as I know nothing about apache/php/MySQL)
Thanks!!
Dan
I want to start of by saying that I'm very new to SQL and PHP, I've done a bit of research for this but I usually manage the network so some concepts are very new for me!
Now here's my situation:
We've purchased a program (from a schoolboard, not an actual company so no support is available) that works off a web page. It uses php with a sql DB.
We were not told the requirements so I made the setup with Windows server 2012, IIS and sql 2012(free edition), but the programmer built this on server 2008R2, apache and mysql and this is creating a few issues.
His PHP pages are encoded with ANSI but once installed it, I needed to switch the PHP pages to UTF-8 since we use french characters ( é, è, à, ç, etc...), this solved the web page issue but for the DB, since he uses MySQL, is collation is Latin1 and mine is French_CI_AS so all french characters ( é, è, à, ç, etc...) in the DB comes out wrong (é,Ã, ç, é,lè) and same thing when we export the data to word/excel.
Now I've managed to create a query to change existing errors but want to fix this permanently.
Do I simply need to change the collation on the SQL DB or should I think about creating a new server with the recommended settings ( which seems more complicated as I know nothing about apache/php/MySQL)
Thanks!!
Dan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Ray, it does help in understanding character encoding but still unsure what I should do to fix the issue. I solved the black diamond with interrogation mark in the web pages by converting to UTF-8 but in SQL, I get é,Ã, ç, é,lè, so should I add the
utf8_decode()
It has to be an end-to-end solution that is consistent across HTML, PHP and MySQL. Please see Character Sets in MySQL in the article.
If you have existing data, you might want to back it up before you start working on the character sets :-)
If you have existing data, you might want to back it up before you start working on the character sets :-)
ASKER
So I've been trying to solve this and still does not work. I'm trying to avoid reinstalling to Mysql so is there a way to change the settings in MS sql 2012? Do I need to make changes to the PHP files? As stated originally, I very new to DB management and PHP so I would appreciate more info on how/where to make the changes.
Thanks!
Thanks!
I would run XAMPP in a VM.. easy start, cause you can anytime revert your VM.
ASKER
I tried XAMPP and WAMP, and could not even start my PHP program. In both cases, I'm stuck...
Why don't only go with Latin1 collation? SQL Server has it also and accept french characteres.
ASKER
It's the same with Latin1, I tried it.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html