PHP with Microsoft SQL

[Chris Molland]

Hello, and thank you for reading this question.

I have successfully built a static website and now my client has asked me to move the data from my current XML files into their corporate Microsoft SQL database. I had a feeling this may happen which is why I placed the data in XML files in the first place and used Javascript methods to pull in the data. Additionally I want to use PHP which appears to be a more contemporary language than ASP with a longer life.

Information and guidance on achieving this is sketchy to say the least! Microsoft seem to be embracing PHP with IIS and SQL but currently Dreamweaver/PHP is almost exclusively used with MySQL.

I would be very grateful if anyone has any real experience or guidance on how achievable this is. In (very) simple terms can I view this as a PHP/MySQL project only with me manually changing the code a little to incorparate the appropriate Microsoft SQL calls? Microsoft do have a listing of the PHP commands to call MS SQL available.

I was hoping there may be a book available for those who wish to use PHP to pull down data from a corporate database, but I can't see one yet.

Thank you very much.

Regards

Chris

[David_Powers]

For historical reasons, PHP started out with database-specific functions, rather than using a standard interface, such as ODBC. That's why Dreamweaver's PHP server behaviors work only with MySQL. Adapting Dreamweaver's code to work with MS SQL Server would involve rewriting almost every line of code.

Since the release of PHP 5, PHP Data Objects (PDO) provide a database-neutral way of interacting with a database. You would need to hand-code everything yourself, but I think PDO is probably the best solution for you. See http://docs.php.net/manual/en/book.pdo.php.

[Chris Molland]

Thank you David.

I will look into PHP Data Objects to learn more.

Out of interest, and to be sure I am heading in the right direction, may I briefly explain my scenario to see how you would suggest I proceed:

I am developing a training website. The building blocks of this website are Lessons. Lessons are web pages located in Topic folders. A Lesson can only reside within a single Topic folder. The content is made available via Courses which are a collection of Lessons, sorted by Topic. A Lesson can belong to any number of Courses because a Course is merely pointing to the Lessons associated with it.

Currently we have an XML file for each Course which defines the Topics and Lessons within the Course. This holds the structure of the Course.

Each Lesson has an XML file which contains the text. Each Lesson comprises a single web page with stacking Divs for each individual screen of learning. These are hiddden and shown as the user clicks throught the Lesson. Spry is used to retrieve the text from the Lesson XML file and display it within the Div. Navigation is developed using Flash which, using Javascript, also reads the XML and 'drives' the showing and hiding of the Divs.

So I would like to create Lessons which are multi-page units of content. Each page needs to use CSS to standardise the text formatting though we wish to have flexibility to position the text and media on each page independantly to keep the content 'alive'.

The Lessons will be displayed within a Master layout which will contain the navigation and an open space for the Lesson to be displayed within.

All of the structure will be held with a series of related SQL tables. Topics will contain Lessons. Lessons will contain Sub-sections. Sub-sections will contain Pages. Pages will contain Text and Media. A Junction Table will relate Courses with Lessons.

So when selecting a Course I would like the Navigation on the Master Page to dynamically adapt to provide Topic, Lesson, Sub-section and page controls. The First page of the First Sub-section of the first Lesson of the first Topic to display. The page to get its text from the SQL database where references to the Media (photos, illustrations, Flash movies etc.) are also held so that the HTML can bring these elements in to the layout.

Summary

So I currently have an XML per Course defining it's structure and an XML file per Lesson defining the text and media for each of its pages. Why move to a dynamic model? Well we soon want to search for content and itterating through the XML is too slow. We also need to save user data and track user performance.

I haven't the experience or knowledge to know if I should try and continue with Dreamweaver replacing the Spry lookups with PHP calls to SQL and replacing all of the Flash navigation Javascript calls to the XML with new SQL lookups. Or should I take this opportunity to re-develop the site using, for example, ASP.NET?

Should I move away from Dreamweaver as it's integration with Microsoft SQL appears to have its limitations or am I missing something here?

Many thanks for reading this. I hope it wasn't too rambling.

Regards

Chris

[David_Powers]

Chris Molland wrote:

I haven't the experience or knowledge to know if I should try and continue with Dreamweaver replacing the Spry lookups with PHP calls to SQL and replacing all of the Flash navigation Javascript calls to the XML with new SQL lookups. Or should I take this opportunity to re-develop the site using, for example, ASP.NET?

Should I move away from Dreamweaver as it's integration with Microsoft SQL appears to have its limitations or am I missing something here?

It's a difficult decision. I have never worked with Microsoft SQL Server or ASP.NET, so I can't say whether learning how to use them will be the best use of your time. They're important technologies, and people who have mastered them are in demand. However, is this for just one project, or do you intend making wider use of them? ASP.NET is reputed to have a steep learning curve, so going in that direction isn't a step to be taken lightly.

Other considerations are how competent you are with PHP, and whether the server you're working on supports it (presumably, if the database is MS SQL Server, ASP.NET is supported). Although I have never worked with MS SQL Server, I'm comfortable enough with my knowledge of PHP to write an application that I'm confident would work with it. It would certainly take me less effort than trying to learn ASP.NET as well. On the other hand, if your knowledge of PHP is limited, you might be better taking the plunge with ASP.NET.

As for using Dreamweaver, it's not the best program for working with ASP.NET. But many .NET developers prefer it for building the front end of their applications. Integration with MS SQL Server is not important. Those who rely on Dreamweaver to do all the back-end coding for them in PHP find themselves extremely limited. Dreamweaver server behaviors are great for quick prototyping, but if you want anything more than very basic functionality, there's no real alternative to coding it yourself. A small, but nevertheless significant number of people relied entirely on the Adobe Dreamweaver Developer Toolbox (ADDT) to create database-driven websites. Adobe has now dropped ADDT, leaving those people tied into an application technology that has a severely limited future.

Making the choice between PHP, ASP.NET, or ColdFusion is a major step in itself, but it's important to acquire the skills to use your chosen technology independently of the IDE you use to build applications.

[jon8]

Here's a blog entry:

http://www.cmiwebstudio.com/blog/how-do-you-decide-between-c-java-php-and-python

In particular, a debate between the C#/.NET/IIS stack and the Java/J2EE/Apache/Solaris stack and the PHP/Apache/Linux stack could go on and on for years and years and you’d never find the right answer. That’s because there are so many pros and cons of all these platforms that advocates of each side can debate and debate and never get any closer to the truth, but it sure as heck is a fun debate.

There’s so much evidence that when it comes right down to it, zillions of people are building huge business-critical things in C#, Java, PHP, or Python, and while they may have problems, they’re not mission threatening problems. So how do you decide between C#, Java, PHP, and Python? The only real difference is which one YOU know better. If you have a serious PHP guru on your team who has built several large systems successfully with PHP, you’re going to be a heck of a lot more successful with PHP, not because PHP is a better language than C#, Java, or Python, but because he knows it better.

ASP.NET is both powerful and clunky at the same time.  sql server is powerful and easy to work with (in my opinion anyway), but there is little that it can do that mysql (which is free) can also do.

For ASP.NET (and everything else other than php and cf), dreamweaver will only be in your workflow creating front ends.  You won't be using it at all for actual C# or .NET framework related coding.  For that, you will need visual studio.

Here's a blog entry about the history of web development with a chart:

http://www.cmiwebstudio.com/blog/the-history-of-web-development

no matter what path you take, you will never know everything you can, you will never stop the need to learn new technologies, and the technologies will never stop changing.  so when it comes to paths, it's not about which one to take, but rather your capacity to follow trends and learn them quickly.  learning one programming language really well, will help you understand how to learn additional ones easier.  if supply and demand affects your decision, the need for really good c#.net developers is probably double that of php developers (because of the sheer volume of php developers), and the pay reflects that.

when you provide clients the facts and information about the technology options, they rarely want to go with anything other than the majority (which is php and the LAMP stack).  However, IT people want to secure their futures, and if it's not stamped with MS, they hate it.  So anything corporate will gravitate toward MS technologies like a moth to the flame.  What type of work you want to do?  Do you picture yourself developing intranets and working on a corporate team developing large scale projects and software... then c#.net is the right direction.  If you want to be a freelancer and work directly with clients building online apps, php is the right direction.  the third option is java, and has the same types of employment you'll find with .net.  everything else is a flavor that comes and goes with popularity, and are riskier paths.

[Chris Molland]

David

You're a Gentleman, thank you.

This is for the one project, but it's a big one!

Interestingly reading your post has me consider ASP.NET more seriously. I have quite a reasonable grasp of developing an N-Tier application (Model, View, Presenter pattern) with a SQL data source (traditional ADO) using Visual Basic in a Visual Studio Team System environment. Also I am currently developing a WPF application which also has a SQL data source (LINQ to SQL this time) and I am embracing the Model, View, ViewModel pattern successfully.

Perhaps taking on ASP.NET won't be too scary after all.

Currently we have most of our navigation developed using Adobe Flash which reads our XML files to determine the Course structure and keep a track of where the user is. Now I have no real idea but I presume that an ASP.NET site would pull down the data it required from the SQL source, disconnect and providing the data doesn't change use this cached data source. Do you know if my Flash files could access any data source held by the ASP.NET application? I wondered if my HTML could get property values from my LINQ to SQL and make them available to my Flash elements?

I will have to enquire if my client has Silverlight on their 'standard corporate PC configuration'. This may be an alternative.

Well it seems that I have quite a journey ahead of me. I am very grateful for your words of wisdom.

If anybody reading this has any good advice or suggestions for a good suitable read then I would be most interested.

Thank you.

[jon8]

I would check out the asp.net forums.  http://forums.asp.net/  you wont receive much help with asp.net here.

[David_Powers]

Chris Molland wrote:

Do you know if my Flash files could access any data source held by the ASP.NET application?

Yes, very much so. The Flex framework is designed to do precisely that sort of thing. Major changes are being made to the Flex framework in the next version, Flex 4, but you can already get access to a public beta of Flash Builder 4 (http://labs.adobe.com/technologies/flashbuilder4/). There are also forums dedicated to Flex and the Flash Builder public beta.