Data Driver

Blog archive

PHP Meets SQL Server -- Converting MySQL Queries

As an addendum to last week's post about a recent SQL Server camp held for PHP developers, Microsoft's Brian Swan has published some new nuts-and-bolts details for converting MySQL queries.

Swan, who blogs on the "At the Intersection of PHP and Microsoft" page, said he learned the technique at the JumpIn! camp, where Microsoft hosted some 17 prominent PHP developers in order to share ideas and educate them about the process of supporting SQL Server and SQL Azure in their PHP applications.

He said it was "an excellent two-way learning experience" and one of the things he learned was that the SQL Server Migration Assistant for MySQL 1.0 (SSMA) can convert specific database queries in addition to its main job of converting databases.

Swan explains the relatively straightforward process by using SSMA to convert the MySQL query:

SELECT post_title, post_date FROM wp_posts ORDER BY post_date LIMIT 5 OFFSET 5;

to the SQL Server query:

SELECT TOP (5) ssma$sub1.post_title, ssma$sub1.post_date
FROM
  (
    SELECT wp_posts.post_title, wp_posts.post_date, ROW_NUMBER() OVER (
      ORDER BY wp_posts.post_date) AS ssma$rownum
    FROM dbo.wp_posts
   ) AS ssma$sub1
WHERE ssma$sub1.[ssma$rownum] > 5
ORDER BY ssma$sub1.[ssma$rownum]
GO

(Whew! If I'm a noob evaluating the two on the basis of query simplicity, it's pretty clear which direction I'd lean.)

Swan noted that all queries won't translate successfully (if they contain functions specific to MySQL, for example), but even if it doesn't work, it can provide clues to help write the translated query yourself.

Do you have experience in supporting SQL Server in PHP apps? Is MySQL really that much simpler than SQL Server? Please share your thoughts here or drop me a line.

Posted by David Ramel on 12/08/2010 at 1:15 PM


comments powered by Disqus

Reader Comments:

Thu, Jan 3, 2013 christi parks India

Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me ... and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance... would really appreciate help... and Also i would like to thank for all the information you are providing on sql.

Fri, Apr 29, 2011 Kevin Salt Lake City

I thought that abominable LIMIT/OFFSET syntax was just because of Denali, but I see that they were playing catch-up with MySQL. (sigh) I think it would have been cleaner to alter the TOP() syntax, like NEXT (5 at 5), since that is what SQL Server 2011 does anyway. It should NEVER have been part of the ORDER BY clause.

Thu, Dec 9, 2010 Brian Swan Redmond

Thanks for the spotlight on my blog post. I chose to show the conversion of a query that used the LIMIT clause because those are the clauses that cause headaches for PHP developers (and for good reason, as you point out). For most queries, however, the translation from MySQL syntax to SQL Server syntax is straightforward and simple. -Brian

Add Your Comments Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.