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]);
}
?>
