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
Daniel VachonIT ProfessionalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Setup a new server. Use XAMPP.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Daniel, what you're seeing is the symptom of a character set collision.  It's a very common issue these days, as PHP changes its default encoding.  Hopefully this article can help you get it sorted out.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html
1
Daniel VachonIT ProfessionalAuthor Commented:
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()

Open in new window

in the PHP files or change the SQL collation?
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Ray PaseurCommented:
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 :-)
0
Daniel VachonIT ProfessionalAuthor Commented:
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!
0
ste5anSenior DeveloperCommented:
I would run XAMPP in a VM.. easy start, cause you can anytime revert your VM.
0
Daniel VachonIT ProfessionalAuthor Commented:
I tried XAMPP and WAMP, and could not even start my PHP program.  In both cases, I'm stuck...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why don't only go with Latin1 collation? SQL Server has it also and accept french characteres.
0
Daniel VachonIT ProfessionalAuthor Commented:
It's the same with Latin1, I tried it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.