LAMP, PHP, MSSQL, ORM, JSON, SOAP, WCF, REST for CMS - Can it be done... easily.

Adam Menkes
Adam Menkes used Ask the Experts™
on
There is a potential project which would require integrating a website being developed in LAP (LAMP without MySQL - Linux, Apache, PHP) in a CMS (Content Management System).

The database would be MS SQL Server 2008 R2. It will be accessed by both the website (being developed by another firm) and desktop windows applications (developed by me).

I have minimal knowledge of PHP, but I assume it has ways to connect to Microsoft SQL Server. Since the web host will be Linux, the SQL Server will be hosted elsewhere on a Windows box  I am assuming there won't be issues connecting to the database, via the IP, login and password. But, for my own curiosity, how is this done?

What would be the best way to go for large amounts of data retrieval? Would a web service be too slow? What would be the quickest way to get data, there are so many acronyms, I think I need to take a REST.

Are there ORM products for PHP to MS SQL? Would this help?
I saw http://www.doctrine-project.org/projects/orm, but I cannot tell if it supports MS SQL or you have to implement interfaces and classes (http://www.doctrine-project.org/projects/dbal/2.0/docs/reference/supporting-other-databases/en#supporting-other-databases), which is not what I would want. I want something that would work out of the box/download.

I cannot tell if Propel works with MS SQL (http://www.propelorm.org/). It appears that http://www.php.net/pdo-dblib has an experimental version.

An ORM may not be the way to go anyway.

Also, if the simple solution is to just have SQL Stored Procedures and have the PHP programmers execute them from their code, that would be fine too. In fact, that would probably work out better for me anyway.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

as PHP is a preprocessor language to prepare websites on a webserver it needs of course a webserver to run and on the webserver there are configuration files only accessible by the webserver (in this case by PHP), in this files the access information to get the connection to the database server are being saved. A typical scenario is phpBB, the wide spread forum system based on PHP where you can see how it works. PHP is flexible enough to handle different databases with several libraries.

But if you develop a direct access to the database for desktop use there is no need to use the same webserver (you CAN do this if you want to create a 3-tier application but it is not necessary). A desktop application usually has the possibility to directly access the database server in a intranet network so it doesn't need the webserver in between. Two possibilities are Access as frontend which can directly work with SQL Server (good choice if the project is not too complex) and a modern and more flexible solution can be Microsoft.NET which has anything you need to directly work with a database. This includes a ORM solution named LINQ. LINQ (Language Integrated Query Language) is a programming language extension directly working as other programming language commands so it can be processed by the compiler and it looks similar to SQL, works with the database but can also work with local objects or other data in the same way. It has (of course) direct support for SQL Server and Visual Studio has a professional entity editor which can map the database tables to entities so they can be used by LINQ (it generates all the classes with some clicks). Moreover some assistants in VS can create simple GUIs with some clicks which can be easily adapted. In fact you can create a working datasheet without programming a line of code.
But of course a real programming with .NET will need knowledge in object oriented programming and a language like VB or VC#. Normally it needs more code and is more complex but also more flexible. If you need a database which shows some lists or forms to change data and no complicate things or modern 3D styles and so on Access is more than enough to create good applications. Here you only need good knowledge in VBA (it is also possible to work with macros but no real programmer would use this...).

Cheers,

Christian
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Author

Commented:
Maybe my post was not clear. The web developers will be using PHP to connect to a SQL Server database. Can this be done?

The desktop app will be connecting to the same SQL Server database, not an intranet one, but one on the web, as the data needs to be shared among not only web users, but several satellite locations and several affiliates where an intranet would not be feasible.

I am fully aware of C#, ASP.NET, LINQ, and ORMs as I have developed with C#, Telerik OpenAccess and LINQ (and generics, and lambda expressions, etc). So, it is not an issue on the Windows side.

I need to know if PHP can connect to SQL Server and if there are going to be issues providing stored procedures for the data access, or if there are better alternatives to return large data results to the PHP site on a Linux server.

I know there is PHP for Windows and I would be able to install .NET side by side, but the issue is the company has already chosen the web development team who is going to use LAMP, but the M is going to be MS SQL Server, if that is possible and will not suffer for speed.


Hi,

yes, PHP can access SQL Server, look here for further details:

http://www.php.net/manual/en/book.mssql.php

Another thing is that SQL Server (2005) can directly expose a web service for direct client access, maybe interesting:

http://www.devx.com/dbzone/Article/28577

The other thing is that satellite locations could connect with a VPN connection instead of using a web application which would be more secure - a web site could be hacked as it is exposed to anyone, a VPN connection is not seen by anyone and .NET is able to work even if the connection is lost in between.

Here is a tutorial how to connect to SQL Server from PHP under Linux using TDS (tabular data stream)as protocol and several other methods:

http://www.phpbuilder.com/columns/alberto20000919.php3

Cheers,

Christian

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial