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

2015-04-02T02:42:00.000-07:00

<?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.
?>