My simple list of MSSQL / SQLSERV PDO queries that I use to compile queries when I start a new dev project in PHP

For my sqlsrv php 5.5 queries I use this as a goto cheat sheet. Just for any other dev that might be looking for these things, its handy to have this lying around when you start a new project with mssql server and want to use PDO.

So, without further ado, some copy pasta code for those who are to lazy to scour stackoverflow.

SERVERNAME = The windows domain your server runs on.
SQLEXPRESS = Default server isntance it runs on.
DATABASENAME = The Database name you wish to read write from.
TABLENAME = The Table you wish to manipulate
INDEXFIELDNAME = The index field of the row you wish to manipulate
FIELDNAME = The field name you wish to manipulate

<?php

$DB = new PDO( "sqlsrv:Server=SERVERNAME\SQLEXPRESS;Database=DATABASENAME","USERNAME","PASSWORD");

   $DB->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

   $DB->query("DATABASENAME");

  }



  catch( PDOException $e ) {

   

     die( "Error connecting to SQL Server".$e );

  }

  

/*===========================

|       UPDATE TABLE        |

===========================*/



$sql =  "UPDATE TABLENAME set FIELDNAME=:newvalue

         WHERE INDEXFIELDNAME=:id" ;

$prep = $DB->prepare($sql);

// Variables should always be defined

// NEVER put output of a function or an int or string directly in bindparam

// ALWAYS make sure you have a variable. that its not scoped out at execute.

$index = 1;

$storage = "Soemthing to save";

$prep->bindParam(":id",$index);

$prep->bindParam(":newvalue",$storage);

$prep->execute();

   

/*===========================

|       SELECT TABLE        |

===========================*/



$sql = "SELECT FIELDNAME,FIELDNAME2 FROM TABLENAME WHERE INDEXFIELDNAME=:id OR FIELDNAME2=:id2"

$prep = $DB->prepare($sql);

// Variables should always be defined

// NEVER put output of a function or an int or string directly in bindparam

// ALWAYS make sure you have a variable. that its not scoped out at execute.

$variable1 = "";

$variable2 = 0;

$prep->bindParam(":id",$variable1);

$prep->bindParam(":id2",$variable2);

$prep->execute();

$ret = array();

while ( $result = $prep->fetch( PDO::FETCH_ASSOC ) ){

 foreach($result as $key => $value) {

  echo "$key has value of [$value] <BR/>";

 }

 if($key["VELDNAAM"] == "Hello") {

  //do something funny

 }

 echo "done something unmentionalbe with ".$key['VELDNAAM2'] . "<BR/>";

}



/*=============================

| ADD NEW ROW TO TABLE        |

=============================*/



$sql = "INSERT INTO TABLENAME (FIELDNAME1,FIELDNAME2,FIELDNAME3) VALUES

   (:value1,:value2,:value3)

   WHERE INDEXFIELDNAME= :id;

   SELECT SCOPE_IDENTITY() as id;";

 $q = $DB->prepare($sql);

 // Variables should always be defined

 // NEVER put output of a function or an int or string directly in bindparam

 // ALWAYS make sure you have a variable. that its not scoped out at execute.

 $index = 1;

 $value1 = "Something";

 $value2 = "to";

 $value3 = "save";

 $q->bindParam(":id",$index);

 $q->bindParam(":value1",$value1);

 $q->bindParam(":value2",$value2);

 $q->bindParam(":value3",$value3);

 $q->execute();

 $q->nextRowSet();

 $res = $q->fetch( PDO::FETCH_ASSOC );

 $insertid = $res['id'];

 // $insertid contains row number/index number that just has been inserted into the database.



/*==========================================

| REMOVE ROW FROM TABLE(permanent)

==========================================*/

$sql = "DELETE FROM TABLENAME WHERE INDEXFIELDNAME=:id";

$prep = $DB->prepare($sql);

// Variables should always be defined

// NEVER put output of a function or an int or string directly in bindparam

// ALWAYS make sure you have a variable. that its not scoped out at execute.

$index = 1;

$prep->bindParam(":id",$index);

$prep->execute();



/*===========================

|      COPY ROW IN TABLE    |

===========================*/

// Don't touch #TempTable. This is a temporary table. if you choose to rename it, rename it consistently.

$sql = "SELECT * INTO #TempTable FROM TABLENAME WHERE INDEXFIELDNAME= :id;

   ALTER TABLE #TempTable DROP COLUMN INDEXFIELDNAME;

   INSERT INTO TABLENAME SELECT * FROM #TempTable;

   DROP TABLE #TempTable;

   SELECT SCOPE_IDENTITY() as id;";

$q = $DB->prepare($sql);

// Variables should always be defined

// NEVER put output of a function or an int or string directly in bindparam

// ALWAYS make sure you have a variable. that its not scoped out at execute.

$index = 1;

$q->bindParam(":id",$index);

$q->execute();

$q->nextRowSet();

$q->nextRowSet();

$res = $q->fetch( PDO::FETCH_ASSOC );

$insertid = $res['id'];

// $insertid contains row number/index number that just has been inserted into the database.

?>