SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Looking for some advice on working with PHP and MS SQL

    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 :]

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ScottM1164 View Post
    ... 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

    Quote Originally Posted by ScottM1164 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind, I just re-read your answer and I see the syntax differences where with functions, not statements. Thanks again for your time :]

  5. #5
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,608
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    @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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,608
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mikl View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,608
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    R937,



    Nice chart, but what exactly does it mean?



    Mike

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    mike, you forgot the smiley after your rhetorical question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,608
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •