I usually work with PHP MySQL and resultantly, my knowledge with MsSQL is somewhat limited. I am about to write a class that makes executing MsSQL queries much easier for myself and my associates. However, in my research, I have found that I have two options of how to connect to an MsSQL database; I can either use the PHP PDO Library or the SQLSRV API. The latter seems the better option for me as I am unfamiliar with the PDO library and how it works, although, I would be willing to learn more about the PDO library if it is the much better choice.
So effectively, I would simply like to know which option is better for communicating with an MsSQL database using PHP?
In addition, is anybody familiar with a good site with examples and documentation of the API? The Microsoft website is good, but somewhat biased and limited.
Any help much appreciated, thanks.
I would recommend you to learn PDO.
Where MySQL(i) is only for MySQL, and SQLSRV API is only for MsSQL, PDO is for around 20 different database engines. Plus, it's object oriented and supports named parameters in prepared statements.
See this great article (it's about PDO vs MySQLi, but the arguments there are valid)
If you are going to write a wrapper class to SQL may i suggest something like Doctrine instead. Its an Object Relational Mapper (ORM) it uses Objects to represent records in a database and a Database Abstraction Layer(DBAL) that uses PDO :
Object relational mapper (ORM) for PHP that sits on top of a powerful
database abstraction layer (DBAL). One of its key features is the
option to write database queries in a proprietary object oriented SQL
dialect called Doctrine Query Language (DQL), inspired by Hibernates
HQL. This provides developers with a powerful alternative to SQL that
maintains flexibility without requiring unnecessary code duplication.
I agree with @Truth that PDO is a more portable API than SQLSRV and therefore generally a better choice.
However there is one bad thing about PDO: insofar as I can tell, it always returns data as type "string", regardless of the datatype in the originating database. Various other SO posts on why PDO is like this boil down to "because that's the way it is on PHP". Maybe so, but it's still gross: returning datetimes or floating point numbers as strings wastes bandwidth, reduces precision, and introduces problems with internationalization due to differences in decimal points and date formats. Yuck.
I'd been planning to use the PDO driver for the obvious reasons, but now I'm taking a look at the SQLSRV driver since it preserves data types.
