www.webdeveloper.com
Results 1 to 5 of 5

Thread: converting a php script for mySql to microsoft sql

  1. #1
    Join Date
    Apr 2014
    Posts
    2

    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.

    Code:
    [syntax=php][syntax=php]//DATABASE DETAILS//
    $DB_ADDRESS="";
    $DB_USER="";
    $DB_PASS="";
    $DB_NAME="";
    
    //SETTINGS//
    //This code is something you set in the APP so random people cant use it.
    $SQLKEY="randomkey";
    
    /************************************CONFIG****************************************/
    
    //these are just in case setting headers forcing it to always expire
    header('Cache-Control: no-cache, must-revalidate');
    
    error_log(print_r($_POST,TRUE));
    
    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
        $query=urldecode($_POST['query']);
        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
          $query=stripslashes($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
        if($link){
          $result=mysql_query($query);                            //runs the posted query (NO PROTECTION FROM INJECTION HERE)
          if($result){
            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, ',', '"');
              }
              rewind($outstream);
              fpassthru($outstream);
              fclose($outstream);
              // 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";
    }[/syntax]
    ?>[/syntax]
    Last edited by jedaisoul; 05-01-2014 at 05:01 AM. Reason: code tags added

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

    mSQL
    MySQL (obsolete api)
    MySQLi

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,614
    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=127.0.0.1';
    // $dsn = 'sqlsrv:Database=testdb;Server=127.0.0.1';

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

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Apr 2014
    Posts
    2
    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
    Location
    Ankh-Morpork
    Posts
    19,614
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

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



Recent Articles