Looking for some advice on working with PHP and MS SQL

[ScottM1164]

Hi,

I hope some of the more experienced developers in this forum might be able to give me some advice. I just found out that the client I'm working with is using an MS SQL db for her website. I had been planning on developing her website using MySQL but it appears that is now not an option. I've found some db connection script on the web that connect to MS SQL db and it looks pretty much the same as how I'd connect to a MySQL db with the exception of the mssql_connect() function replacing the mysql_connect() function. So I'm wondering if working with PHP and MS SQL is going to be pretty much the same as with MySQL?

The site I'm developing is not overly complex, the client just wants to be able to update a few specific pages and the footer links. So my db is just going to store article headers, articles, link names, URL's as well as username and password for a login page. So maybe 6 tables at the most. I'll be adding, editing and deleting entries through a form I'll create for my client.

Normally I'd create the tables using phpMyAdmin, but I know MS SQL doesn't support that so I'm planning on just writing the tables by hand since the db is not going to be overly complex. I was hoping to be able to still test the queries in phpMyAdmin by creating a duplicate MySQL db on my local server and just copy and pasting those queries to the scripts I create when I start working with my clients MS SQL db.

So I guess to try summarize what I'm asking I'll break it down to a few questions.

1) I'd like to know if the queries work the same way in MS SQL as they do in MySQL, or do I need to structure my queries differently?

2) After I've established my connection to the MS SQL db can I use the same PHP I normally use to make my pages editable when working with MySQL?

3)Are there any recommended resources for working with PHP and MS SQL?

Thanks in advance for your time :]

[r937]

... I was hoping to be able to still test the queries in phpMyAdmin by creating a duplicate MySQL db on my local server

reading your post up to this point, that's exactly what i would have suggested

1) I'd like to know if the queries work the same way in MS SQL as they do in MySQL, or do I need to structure my queries differently?

they work exactly the same

with minor differences in syntax


for 2) i'm confident the answer is yes, but i don't do php

for 3) i'm afraid i don't know of any for that particular combo, but like i said, your php is likely not going to have to change at all

you will have to convert some syntax (e.g. functions) but that's actually not difficult at all

[ScottM1164]

Thanks for your reply r937, those minor differences in syntax you mentioned, can you give me an example just so I know what to look for when inevitably run into problems. I realize that is probably too broad of a question for a simple answer. I'm expecting to be writing simple CREATE TABLE and a few INSERT, UPDATE, DELETE record queries are there syntax differences in those kinds of queries I should watch out for?

[ScottM1164]

Never mind, I just re-read your answer and I see the syntax differences where with functions, not statements. Thanks again for your time :]

[Mikl]

I was hoping to be able to still test the queries in phpMyAdmin by creating a duplicate MySQL db on my local server and just copy and pasting those queries to the scripts I create when I start working with my clients MS SQL db.

I definitely would NOT recommend that.

Although both databases support pretty much the same SQL language, there are enough minor differences in syntax and data types to cause endless headaches. You can't really write a query against one database and test it against another. Also, you won't be able to use anything other than the lowest common denominator of SQL, and will have to forego all the many useful additional T-SQL functions that are different from their MySQL equivalents.

I suggest that you purchase a copy of SQL Server Developer Edition. This is a low-cost version of the full SQL Server database, designed especially for people like you (and me). It will let you develop against SQL Server without having to install the full product and without the cost that that involves.

Mike

[r937]

mike, i completely agree

"endless headaches" is perhaps exaggerating... porting from mysql to sql server is really not that hard

but you do make some good points

[SpacePhoenix]

Your best bet for working with both MySQL and MsSQL would be to use PDO

http://www.phpro.org/tutorials/Intro...o-PHP-PDO.html

@r937 ; does MS Access use the exact same syntax as MsSQL? If it does it may be a better (cheaper) route for the OP to go down as older versions (even the new version) of MS Access can be got far cheaper.

[r937]

@r937 ; does MS Access use the exact same syntax as MsSQL?

not the exact same, no

there would still be minor syntax changes necessary

and how much cheaper than free would you want?

[Mikl]

Glad you agree, R937.

Regarding your point about it not being difficult to port from SQL Server to MySQL, that's certainly true. But my point is that you shouldn't plan on having to do the port in the first place. Otherwise, you've got all the effort of developing the code in the first place, and then the extra effort of converting it.

I still think it's better to bite the bullet and go for SQL Server from the outset.

Mike

[r937]

Otherwise, you've got all the effort of developing the code in the first place, and then the extra effort of converting it.

let's keep things in perspective, shall we?
mysql-mssql.jpg


and consider that learning a new front end platform to work with sql server express (whatever one you'd care to recommend) just might be more painful than using a tool (phpmyadmin) you're already comfortable with

[Mikl]

R937,



Nice chart, but what exactly does it mean?



Mike

[r937]

mike, you forgot the smiley after your rhetorical question

[Mikl]

Ture. I never really got the hang of this modern smiley stuff. In my young day, we made do with "<g>" and "<s>". We were made of sterner stuff then.

Mike