php操作ms sql,首先打开php.ini中,extension=php_mssql.dll前;去掉,重启apache

1.添加记录

add.php

<?php
 header("content-type:text/html;charset=utf-8");

 function convert2utf8($string)
 {
   return iconv("gbk","utf-8",$string);
 }
 function convert2gbk($string)
 {
   return iconv("utf-8","gbk",$string);
 }

 $dbhost="YANSIYU";
 $dbuser="sa";
 $dbpwd="admin";
 $my_conn=mssql_connect($dbhost,$dbuser,$dbpwd);
 mssql_select_db('test',$my_conn);

 $username=$_REQUEST["username"];
 $password=$_REQUEST["password"];
 $age=$_REQUEST["age"];

 if($username=="" or $password=="" or $age==""){
 echo "请添加完整";
 }
 else{
 $sql="insert into userinfo(username,password,age) values('".$username."','".$password."',".$age.")";
 $result =mssql_query($sql,$my_conn);
 echo "添加成功";
 }
?>

2.查询记录

query.php

<?php
 header("content-type:text/html;charset=utf-8");

 function convert2utf8($string)
 {
   return iconv("gbk","utf-8",$string);
 }
 function convert2gbk($string)
 {
   return iconv("utf-8","gbk",$string);
 }

 $dbhost="YANSIYU";
 $dbuser="sa";
 $dbpwd="admin";
 $my_conn=mssql_connect($dbhost,$dbuser,$dbpwd);
 mssql_select_db('test',$my_conn);

 if($_REQUEST["id"]==""){
 $sql="select * from userinfo with(nolock)";
 }
 else{
 $sql="select * from userinfo with(nolock) where id=".$_REQUEST["id"]." ";
 }
 
 //echo $sql;

 $result =mssql_query($sql,$my_conn);
 echo "<table border=1 width=40% align=center><tr><td>ID</td><td>姓名</td><td>日期</td></tr>";
 while($row=mssql_fetch_array($result)){
 echo "<tr>";
 echo "<td>".convert2utf8($row["id"])."</td>";
 echo "<td><A href=query.php?id=".$row["id"].">".convert2utf8($row["username"])."</a></td>";
 echo "<td>".convert2utf8($row["datetime"])."</td>";
 echo "</tr>";
 }
 echo "</table>";
?>

3.分页显示记录

page.php

<style type="text/css">
table{
font-size:12px;
font-family:Arial, Helvetica, sans-serif;
}
</style>
<?php
header("content-type:text/html;charset=utf-8");
function convert2utf8($string)
{
 return iconv("gbk","utf-8",$string);
}
function convert2gbk($string)
{
 return iconv("utf-8","gbk",$string);
}
$local="YANSIYU";
$user="sa";
$pass="admin";
$name="test";
$page=$_REQUEST["page"];
$userperpage=20;
if (!isset($page)) { $page=1; }
if ($page<=0) { $page=1; }
$db=mssql_connect($local,$user,$pass);
mssql_select_db($name,$db);
$query=mssql_query("select count(*) from userinfo",$db);
$query=mssql_fetch_array($query);
$pages=ceil($query[0]/$userperpage);
if ($page>$pages) { $page=$pages;}
if ($query[0]<$userperpage) { $userperpage=$query[0]; }
$dangqian=($page-1)*$userperpage;
echo "<table border=0 width=100% cellspacing=0 cellpadding=0 height=19>";
echo "<tr>";
echo "<td width=52% height=19 valign=bottom bgcolor=#B5C6B0><p align=center>";
echo "<font color=#408080>新闻标题</font></td>";
echo "<td width=14% height=19 valign=bottom align=center bgcolor=#B5C6B0>";
echo "<font color=#408080>发表日期</font></td>";
echo "</tr>";
echo "</table>";
echo "<table border=0 width=100% cellspacing=0 cellpadding=0 height=19>";
      $db=mssql_connect($local,$user,$pass);
      mssql_select_db($name,$db);
      $query=mssql_query("select * from userinfo order by datetime desc",$db);
      $roro=mssql_num_rows($query);
       if ($roro==0) {
echo "<br><font color="#FF0000" size="2"><marquee behavior="alternate" border="0" width="300" height="16" scrolldelay="70">目前数据库中无任何记录！</marquee></font>";
        exit;
        }
      mssql_data_seek($query,$dangqian);
      $i=0;
      while ($arr=mssql_fetch_array($query)) {
       if ($i>=$userperpage) { break;}
echo "<tr>";
echo "<td width=52% bgcolor=#F3F3F3 height=19 valign=bottom><font color=#3D5449>".convert2utf8($arr["username"])."</font></td>";
echo "<td width=14% bgcolor=#F3F3F3 height=19 valign=bottom align=center><font color=#3D5449>".convert2utf8($arr["datetime"])."</font></td>";
echo "</td>";
echo "</tr>";
     $i++;
 }   //while
echo "<tr>";
echo "<td width=100% height=20 colspan=5></td>";
echo "</tr>";
echo "<tr><td width=100% colspan=5><center>";
    if ($page<>1) {
echo "<a href=$PHP_SELF?page=1><font color=#408080>首页</font></a>　";
                  }
   if ($page>1) { 
   $upage=$page-1; 
echo "<a href=$PHP_SELF?page=$upage><font color=red>上一页</font></a>　";
                }

   if ($page<$pages) {
   $npage=$page+1;
echo "<a href=$PHP_SELF?page=$npage><font color=red>下一页</font></a>　";
                     }
   if ($page<>$pages)   {
echo "<a href=$PHP_SELF?page=$pages><font color=#408080>尾页</font></a>　";
                       }
        echo "共有".$pages."页，当前".$page."页";
echo "</center></td>";
echo "</tr>";
echo "</table>";
?>

4.php执行ms sql存储过程带输入参数

phpmssqlspin.php

sp:CREATE   procedure sp2 @username varchar(10)
as select username,datetime from userinfo where username =@username
<?php

header("content-type:text/html;charset=utf-8");

 function convert2utf8($string)
 {
   return iconv("gbk","utf-8",$string);
 }
 function convert2gbk($string)
 {
   return iconv("utf-8","gbk",$string);
 }
$hostname = "localhost";
$dbuser = "sa";
$dbpasswd = "admin";
$conn = mssql_connect($hostname,$dbuser,$dbpasswd) or die("无法连接数据库服务器！");
$db = mssql_select_db("test",$conn) or die("无法连接test数据库！");
$query="declare @username varchar(10)";
$username=$_REQUEST["username"];
$query.="set @username='".$username."'";
$query.="exec sp2 @username";
$rresult = mssql_query($query) or die("无法执行SQL：$query");
while($arr = mssql_fetch_array($rresult)){
echo convert2utf8($arr["username"])."　";
echo convert2utf8($arr["datetime"])."<br/>";
}
?>

5.php执行ms sql存储过程带输出参数

phpmssqlspout.php

sp:CREATE procedure sp4 @password varchar(10) output
as select @password=password from userinfo where username='victor'

<?php

 header("content-type:text/html;charset=utf-8");
 function convert2utf8($string)
 {
   return iconv("gbk","utf-8",$string);
 }
 function convert2gbk($string)
 {
   return iconv("utf-8","gbk",$string);
 }
$hostname = "localhost";
$dbuser = "sa";
$dbpasswd = "admin";
$conn = mssql_connect($hostname,$dbuser,$dbpasswd) or die("无法连接数据库服务器！");
$db = mssql_select_db("test",$conn) or die("无法连接test数据库！");
$rresult = mssql_query("declare @password varchar(10) exec sp4 @password output select @password") or die("无法执行SQL：$query");
while($arr = mssql_fetch_array($rresult)){
echo "密码:".convert2utf8($arr[0]);
}
?>