Skip to main content
C
Chris Molland
Known Participant
November 20, 2009
Question

PHP with Microsoft SQL

  • November 20, 2009
  • 1 reply
  • 1125 views

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

This topic has been closed for replies.

1 reply

David_Powers
David_Powers
Inspiring
November 20, 2009

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.

C
Chris MollandAuthor
Known Participant
November 22, 2009

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

C
Chris MollandAuthor
Known Participant
November 25, 2009

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.


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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices