Results 1 to 5 of 5

Thread: converting a php script for mySql to microsoft sql

  1. #1
    Join Date
    Apr 2014

    converting a php script for mySql to microsoft sql

    I have a script that is designed to work with mysql and i need it to work with microsoft sql. I have already built a web app using mvc and have it connecting to the database. i have also built a mobile app using a software called App inventor 2 which just uses code blocks. I need the mobile app to connect to the same database but in order to do so i have to use a php script but the problem is this script is wrote for mysql and i am using microsoft sql. I have never used php before and am wondering is there much involved to change the script so it will work with microsoft sql.

    [syntax=php][syntax=php]//DATABASE DETAILS//
    //This code is something you set in the APP so random people cant use it.
    //these are just in case setting headers forcing it to always expire
    header('Cache-Control: no-cache, must-revalidate');
    if( isset($_POST['query']) && isset($_POST['key']) ){         //checks if the tag post is there and if its been a proper form post
      //2014/02/18: set content type to CSV (to be set here to be able to access this page also with a browser)
      header('Content-type: text/csv');
      if($_POST['key']==$SQLKEY){                                 //validate the SQL key
        if(get_magic_quotes_gpc()){     //check if the worthless pile of crap magic quotes is enabled and if it is strip the slashes from the query
        $link = mysql_connect($DB_ADDRESS,$DB_USER,$DB_PASS);     //connect ot the MYSQL database
        mysql_select_db($DB_NAME,$link);                          //connect to the right DB
          $result=mysql_query($query);                            //runs the posted query (NO PROTECTION FROM INJECTION HERE)
            if (strlen(stristr($query,"SELECT"))>0) {          //tests if its a select statemnet
              $num_fields = mysql_num_fields($result);            //collects the rows and writes out a header row
              $headers = array();
              for ($i = 0; $i < $num_fields; $i++) {
                $headers[] = mysql_field_name($result , $i);
              $outstream = fopen("php://temp", 'r+');             //opens up a temporary stream to hold the data
              fputcsv($outstream, $headers, ',', '"');
              while ($row = mysql_fetch_row($result)){
                fputcsv($outstream, $row, ',', '"');
              // echo $csv; //writes out csv data back to the client
            } else {
              header("HTTP/1.0 201 Rows");
              echo "AFFECTED ROWS: ".mysql_affected_rows($link); //if the query is anything but a SELECT it will return the number of affected rows
          } else {
            header("HTTP/1.0 400 Bad Request");                  //send back a bad request error
            echo mysql_errno($link).": ".mysql_error($link);     // errors if the query is bad and spits the error back to the client
          mysql_close($link);                                    //close the DB
        } else {
          header("HTTP/1.0 400 Bad Request");
          echo "ERROR Database Connection Failed";               //reports a DB connection failure
      } else {
         header("HTTP/1.0 400 Bad Request");
         echo "Bad Request";                                     //reports if the code is bad/
    } else {
            header("HTTP/1.0 400 Bad Request");
            echo "Bad Request";
    Last edited by jedaisoul; 05-01-2014 at 04:01 AM. Reason: code tags added

  2. #2
    Join Date
    Dec 2002
    Seattle, WA
    the built-in api functions for MySQL and mSQL are mostly similar. the actual SQL syntax used by each is vastly different.

    MySQL (obsolete api)

  3. #3
    Join Date
    Aug 2004
    If possible, I would recommend switching from using the PHP MySQL extension to using its PDO extension. This both makes it easier to switch between different DBMS's, while it also would get you away from using the now-deprecated MySQL extension (replaced by the MySQLi extension). If you do this, then all you have to do (assuming reasonably standards-compliant SQL throughout) is to change the "dsn" string (hopefully just one line in a config file) used by the PDO connection function.

    A simple example (with no defensive coding):
    PHP Code:
    // un-comment the DB type you want to use:
    $dsn 'mysql:dbname=testdb;host=';
    // $dsn = 'sqlsrv:Database=testdb;Server=';

    $dbUser 'foo';
    $dbPass 'bar';
    $pdo = new PDO($dsn$dbUser$dbPass);
    $stmt $pdo->prepare("SELECT * FROM some_table WHERE something = :value");
    $row $stmt->fetch(PDO::FETCH_ASSOC)) {
    // do stuff with elements in $row

    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  4. #4
    Join Date
    Apr 2014
    thanks that seems like a good answer. expecially the part where you say "all you would have to do" .The problem is im not familiar with php and wondering if i understand correctly. Are all you are saying is that i just change the extension and dsn string. what about all the mysql key words in my script.
    Thanks a mill.

  5. #5
    Join Date
    Aug 2004
    Yes, whichever way you go (use PDO or MSSQL functions), you would have to essentially change any line of code using mysql_*() functions to use the different set of functions. The advantage (IMHO) of replacing them with PDO functions/methods is that if you ever need to change it back or to something else, you just have to change that dsn string. (That may be a best case only, as there are some differences between DBMS's, especially if you use special syntax/features that are not generally supported by all.) On the the other hand, it might be slightly simpler to change to MSSQL functions, as they would likely replace your MySQL functions on a roughly one-for-one basis; which might save you a little time now at the risk of more time in the future if you ever need to change again.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



X vBulletin 4.2.2 Debug Information

  • Page Generation 0.09178 seconds
  • Memory Usage 2,905KB
  • Queries Executed 15 (?)
More Information
Template Usage (34):
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (5)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (5)postbit
  • (5)postbit_onlinestatus
  • (5)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (71):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates