2016年9月1日 星期四

Windows + Apache + MSSQL + PHP 測試

平時較常用 Linux + Apache + MySQL + PHP。今日嘗試把當中兩項改為 Microsoft 的產品,變成 Windows + Apache + MSSQL + PHP,看看需要有多大的改動。

安裝好 Windows Server 後,裝上 Apache 及 PHP 後,編寫了一個簡單的程式,分別以 PDO 及 sqlsrv 方式對 MSSQL 內的表格進行存取。
<?php
//----------------------------------------------------------------------------------------
//  MSSQL Connection and Query Test for Microsoft Windows 2012
//----------------------------------------------------------------------------------------
//  Platform: Windows Server 2012 + PHP7 + Apache24 + MSSQL
//  Written by Pacess HO
//  Copyright 2016 Pacess Studio.  All rights reserved.
//----------------------------------------------------------------------------------------

//  Variables
$hostname = "127.0.0.1";
$username = "pacess";
$password = "Sita0310";
$database = "SitaChan";

$date = date("YmdHis");

//----------------------------------------------------------------------------------------
//  Connect to database
$connectionInfo = array("Database"=>$database, "UID"=>$username, "PWD"=>$password);
$sqlConnection = sqlsrv_connect($hostname, $connectionInfo);

//----------------------------------------------------------------------------------------
//  SELECT statement
$statement = "SELECT * FROM dbo.employees";
$query = sqlsrv_query($sqlConnection, $statement);
echo("<br>statement=$statement");

while ($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_NUMERIC))  {
    echo("<br>".$row[1]." / ".$row[2]);
}
echo("<hr>");

//----------------------------------------------------------------------------------------
//  PDO: Connect to database
//----------------------------------------------------------------------------------------
$dsn = "sqlsrv:Server=".$hostname.";Database=".$database;
$pdo = new PDO($dsn, $username, $password);
$pdo->exec("SET NAMES 'utf8'");
$pdo->exec("SET CHARACTER SET UTF8");
$pdo->exec("SET CHARACTER_SET_RESULTS=UTF8");

$pdo->exec("USE TestingDB;");

//----------------------------------------------------------------------------------------
//  SELECT statement
$statement = "SELECT * FROM dbo.employees";
$resultArray = $pdo->query($statement);
echo("<br>statement=$statement");

$nextID = 1;
foreach ($resultArray as $row)  {
    $nextID++;
    echo("<br>".$row["employee_name"]." / ".$row["employee_password"]);
}
echo("<hr>");

//----------------------------------------------------------------------------------------
//  INSERT statement
$newPassword = md5("Pass_".$date);

$statement = "INSERT INTO dbo.employees (employee_id, employee_name, employee_password) VALUES (".$nextID.", 'User_".$date."', '".$newPassword."');";
$affectedRows = $pdo->exec($statement);
echo("<br>statement=$statement");
echo("<br>affectedRows=$affectedRows");
echo("<hr>");

//----------------------------------------------------------------------------------------
//  UPDATE statement
$userPassword = md5($date);

$statement = "UPDATE dbo.employees SET employee_password='".$userPassword."' WHERE employee_name='Pacess'";
$affectedRows = $pdo->exec($statement);
echo("<br>statement=$statement");
echo("<br>affectedRows=$affectedRows");
echo("<hr>");

?>

沒有留言: