php连接SqlServer并实现增删改查

季雨發表於 2020-5-28 18:27:00

<h2>一、配置环境</h2>
　　1、配置好SqlServer的登录名：sc。参考：https://jingyan.baidu.com/article/8cdccae9452b3c315513cd52.html
　　2、下载 php_pdo_sqlsrv_74_ts_x64.dll 和 php_sqlsrv_74_ts_x64.dll两个文件并放到 php 的ext目录下。下载地址：https://www.zhaodll.com/dll/p/201905/343232.html
　　3、在你的php.ini文件中加上
<div class="cnblogs_code">
<pre>extension=php_pdo_sqlsrv_74_ts_x64.dll
extension=php_sqlsrv_74_ts_x64.dll</pre>
</div>
　　4、重启Apache服务器。
　　5、测试：
<div class="cnblogs_code">
<pre>$serverName = "localhost"; //数据库服务器地址
$uid = "sa"; //数据库用户名
$pwd = "lxy208751"; //数据库密码
$connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database" => "Students");
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn == false) {
echo "连接失败！";
var_dump(sqlsrv_errors());
exit;
} else {
echo "链接成功";
}</pre>
</div>
<img src="https://img2020.cnblogs.com/blog/1543695/202005/1543695-20200528181914188-1303924672.png" alt="">
 
 
<h2>二、编码设置</h2>
（注：由于sql server 不支持UTF8，当使用varchar保存汉字时，会出现乱码。需要转换。）
　　1、从数据库查出来的数据编码“GBK”，一般我们有的是“UTF-8”,所以需要转换成“UTF-8”。
<div class="cnblogs_code">
<pre>function toU8($str){
return iconv('GBK', 'UTF-8', $str);
}</pre>
</div>
　　2、从程序代码中的变量插入的数据库时，需要将变量的编码改成“GBK”才能插入数据库。
<div class="cnblogs_code">
<pre>function toGBK($str){
return iconv('UTF-8', 'GBK', $str);
}</pre>
</div>
<h2>三、学生信息管理系统</h2>
　　1、index.php
<div class="cnblogs_code"><img id="code_img_closed_eb7e6582-1054-4c49-8589-c7d73b451f34" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_eb7e6582-1054-4c49-8589-c7d73b451f34" class="code_img_opened" style="display: none" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_eb7e6582-1054-4c49-8589-c7d73b451f34" class="cnblogs_code_hide">
<pre><?php

include('./conn/conn.php');
$sql="select * from student";
$result = sqlsrv_query( $conn, $sql ) or die("数据查询失败！");

if(!empty($_GET)){
$delete=$_GET['delete'];
if($delete='1'){
 echo "<script>alert('删除成功！');</script>";

}else{
 echo "<script>alert('删除失败！');</script>";
}

}
function toU8($str){
return iconv('GBK', 'UTF-8', $str);
}

?>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生管理系统</title>
<link rel="stylesheet" href="css/bootstrap.min.css" type="text/css" />
<script src="js/jquery-1.11.3.min.js" type="text/javascript"></script>
<script src="js/bootstrap.min.js" type="text/javascript"></script>
<style type="text/css">
 /*body {
 background-repeat: no-repeat;
 background-size:cover;
 background-attachment: fixed;
 background-image: url(image/c_b2.jpg);
 background-position: 0px -80px;
 }*/
 table {
 margin: auto;
 width: 80%;
 text-align: center;
 background-color: #ffffff;
 }

 h1 {
 font-family: 华文行楷;
 }
</style>
<script type="text/javascript">
 function deleteStudent(sno) {
 if (confirm("您是否要删除该项？")) {
 location.href = "delete.php?sno="+sno;
 }
 }
</script>
</head>
<body>
<div>
<h1 align="center">学生信息管理系统</h1>
<h3 align="center">
 <a href="add.php">添加学生信息</a>
</h3>
</div>
<div style="align-content: center;">
<table class="table table-bordered" style="width: 80%;">
 <thead>
 <tr>
 <th>学号</th>
 <th>姓名</th>
 <th>性别</th>
 <th>年龄</th>
 <th>学院</th>
 <th colspan="2">操作</th>

 </tr>

 </thead>
 <tbody>
 <?php
 while($row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC)){
 ?>
 <tr>
 <td><?php echo toU8($row['Sno']);?></td>
 <td><?php echo toU8($row['Sname']);?></td>
 <td><?php echo toU8($row['Ssex']);?></td>
 <td><?php echo toU8($row['Sage']);?></td>
 <td><?php echo toU8($row['Sdept']);?></td>

 <td><a
 href="update.php?sno=<?php echo toU8($row['Sno']);?>">修改</a></td>
 <td><a href="javascript:;" onclick="deleteStudent(<?php echo toU8($row['Sno']);?>)">删除</a></td>
 </tr>
 <?php
 }
 ?>
 </tbody>
</table>
</div>
<div>
<hr>
<h1 align="center" style="font-size: 20px;">石家庄铁道大学 @2018-2020</h1>
</div>
</body>
</html></pre>
</div>
View Code</div>
　　2、add.php
<div class="cnblogs_code"><img id="code_img_closed_ceb6e6c9-9986-4655-b0a8-88ba35dc799f" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_ceb6e6c9-9986-4655-b0a8-88ba35dc799f" class="code_img_opened" style="display: none" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_ceb6e6c9-9986-4655-b0a8-88ba35dc799f" class="cnblogs_code_hide">
<pre><?php
include('./conn/conn.php');
function toGBK($str){
return iconv('UTF-8', 'GBK', $str);
}
if(!empty($_POST)){
$name=$_POST['name'];
$sex=$_POST['sex'];
$sno=$_POST['sno'];
$dept=$_POST['dept'];
$age=$_POST['age'];
$sno=toGBK($sno);
$name=toGBK($name);
$sex=toGBK($sex);
$dept=toGBK($dept);
$sql = "insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('$sno','$name','$sex',$age,'$dept')";
echo $sql;
$result = sqlsrv_query($conn, $sql) or die("数据插入失败！");
header("location:index.php");
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>添加学生信息</title>
<style type="text/css">
 html {
 font-size: 15px;
 }

 fieldset {
 width: 450px;
 margin: 0 auto;
 }

 legend {
 font-weight: bold;
 font-size: 18px;
 }

 label {
 float: left;
 width: 70px;
 margin-left: 10px;
 }

 .left {
 margin-left: 80px;
 }

 .input {
 width: 200px;
 border: 1px solid #ccc;
 padding: 7px 0px;
 border-radius: 3px; /*css3属性IE不支持*/
 padding-left: 5px;
 }

 span {
 color: #666666;
 }
 p{
 margin-left: 30px;

 }
</style>
</head>

<body>
<fieldset>
<legend>添加个人信息</legend>
<form name="addForm" method="post" action="#">
 
 <label for="sno" class="label">学号:</label>
 <input id="sno" name="sno" type="text" class="input" />
 
 
 <label for="name" class="label">姓名:</label>
 <input id="name" name="name" type="text" class="input" />
 
 
 <label for="sex" class="label">性别:</label>
 <input name="sex" type="radio" value="男" >男
 <input name="sex" type="radio"value="女">女
 
 
 <label for="age" class="label">年龄:</label>
 <input id="age" name="age" type="text" class="input" />
 
 
 <label for="dept" class="label">学院:</label>
 <input id="dept" name="dept" type="text" class="input" />
 
 
 <input type="reset" value=" 重置 "/>
 <input type="submit" name="submit" value="提交" class="left" />
 
</form>
</fieldset>
</body>
</html></pre>
</div>
View Code</div>
　　3、undate.php
<div class="cnblogs_code"><img id="code_img_closed_d6821280-c4a0-4040-a661-d76bc4cfcff4" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_d6821280-c4a0-4040-a661-d76bc4cfcff4" class="code_img_opened" style="display: none" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_d6821280-c4a0-4040-a661-d76bc4cfcff4" class="cnblogs_code_hide">
<pre><?php

include('./conn/conn.php');
function toU8($str){
return iconv('GBK', 'UTF-8', $str);
}

function toGBK($str){
return iconv('UTF-8', 'GBK', $str);
}

if(!empty($_GET) ){
$sno=$_GET['sno'];
$sno=toGBK($sno);
$sql="select * from student where Sno='$sno'";
$result = sqlsrv_query($conn, $sql) or die("数据查询失败！");
$row=sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>修改学生信息</title>
<style type="text/css">
 html {
 font-size: 15px;
 }

 fieldset {
 width: 450px;
 margin: 0 auto;
 }

 legend {
 font-weight: bold;
 font-size: 18px;
 }

 label {
 float: left;
 width: 70px;
 margin-left: 10px;
 }

 .left {
 margin-left: 80px;
 }

 .input {
 width: 200px;
 border: 1px solid #ccc;
 padding: 7px 0px;
 border-radius: 3px; /*css3属性IE不支持*/
 padding-left: 5px;
 }

 span {
 color: #666666;
 }
 p{
 margin-left: 30px;

 }
</style>
</head>

<body>
<fieldset>
<legend>修改学生信息</legend>
<form name="addForm" method="post" action="change.php">

 
 <label for="sno" class="label">学号:</label>
 <input id="sno" name="sno" type="text" class="input" value="<?php echo toU8($row['Sno']); ?>"/>
 
 
 <label for="name" class="label">真实姓名:</label>
 <input id="name" name="name" type="text" class="input" value="<?php echo toU8($row['Sname']); ?>"/>
 
 
 <label for="sex" class="label">性别:</label>
 <input name="sex" type="radio" value="男"
 <?php
 if(toU8($row['Ssex'])=='男'){
 echo "checked='checked'";
 }

 ?>
 >男
 <input name="sex" type="radio" value="女"
 <?php
 if(toU8($row['Ssex'])=='女'){
 echo "checked='checked'";
 }

 ?>
 >女
 
 
 <label for="age" class="label">年龄:</label>
 <input id="age" name="age" type="text" class="input" value="<?php echo toU8($row['Sage']); ?>"/>
 
 
 <label for="dept" class="label">学院:</label>
 <input id="dept" name="dept" type="text" class="input" value="<?php echo toU8($row['Sdept']); ?>"/>
 
 
 <input type="reset" value="重置"/>
 <input type="submit" name="submit" value="确定" class="left" />
 
</form>
</fieldset>
</body>
</html></pre>
</div>
View Code</div>
　　4、change.php
<div class="cnblogs_code"><img id="code_img_closed_d72bd3d1-7c93-4400-8c01-3564b624bf09" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_d72bd3d1-7c93-4400-8c01-3564b624bf09" class="code_img_opened" style="display: none" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_d72bd3d1-7c93-4400-8c01-3564b624bf09" class="cnblogs_code_hide">
<pre><?php
include('./conn/conn.php');
function toGBK($str){
return iconv('UTF-8', 'GBK', $str);
}
if(!empty($_POST)){
$name=$_POST['name'];
$sex=$_POST['sex'];
$sno=$_POST['sno'];
$dept=$_POST['dept'];
$age=$_POST['age'];
$sno=toGBK($sno);
$name=toGBK($name);
$sex=toGBK($sex);
$dept=toGBK($dept);
$sql = "updateStudent set Sno ='$sno', Sname='$name', Ssex='$sex',Sage=$age, Sdept='$dept' where Sno ='$sno'";
echo $sql;
$result = sqlsrv_query($conn, $sql) or die("数据更新失败！");
header("location:index.php");
}</pre>
</div>
View Code</div>
　　5、delete.php
<div class="cnblogs_code"><img id="code_img_closed_0b1395a8-3553-410d-8b6e-04e406e4a5a2" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_0b1395a8-3553-410d-8b6e-04e406e4a5a2" class="code_img_opened" style="display: none" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_0b1395a8-3553-410d-8b6e-04e406e4a5a2" class="cnblogs_code_hide">
<pre><?php
include('./conn/conn.php');
function toGBK($str){
return iconv('UTF-8', 'GBK', $str);
}
if(!empty($_GET)){
$sno=$_GET['sno'];
$sno=toGBK($sno);
$sql_d="delete from student where Sno='$sno'";
$result_d = sqlsrv_query($conn, $sql_d) or die("数据查询失败！");
if($result_d){
 header("location:index.php?delete=1");
}else{
 header("location:index.php?delete=0");
}
}</pre>
</div>
View Code</div>
　　6、conn.php
<div class="cnblogs_code"><img id="code_img_closed_a58ba1a3-9949-4a0a-823f-60b5b9b04569" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_a58ba1a3-9949-4a0a-823f-60b5b9b04569" class="code_img_opened" style="display: none" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_a58ba1a3-9949-4a0a-823f-60b5b9b04569" class="cnblogs_code_hide">
<pre><?php
header("content-Type: text/html; charset=utf-8");
$serverName = "localhost"; //数据库服务器地址
$uid = "sa"; //数据库用户名
$pwd = "lxy208751"; //数据库密码
$connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database" => "Students");
$conn = sqlsrv_connect($serverName, $connectionInfo);</pre>
</div>
View Code</div>
<h2>四、视图</h2>
<img src="https://img2020.cnblogs.com/blog/1543695/202005/1543695-20200528183116750-1684257889.png" alt="">
 
 <img src="https://img2020.cnblogs.com/blog/1543695/202005/1543695-20200528183129512-409527910.png" alt="">
 
 <img src="https://img2020.cnblogs.com/blog/1543695/202005/1543695-20200528183147315-151403160.png" alt="">
 
 <img src="https://img2020.cnblogs.com/blog/1543695/202005/1543695-20200528183203124-10163561.png" alt="">
 
 <img src="https://img2020.cnblogs.com/blog/1543695/202005/1543695-20200528183224793-1656799154.png" alt="">
 
 <img src="https://img2020.cnblogs.com/blog/1543695/202005/1543695-20200528183236239-372491359.png" alt="">
  
来源：https://www.cnblogs.com/20183544-wangzhengshuai/p/12982811.html

頁: [1]

琼殿技术论坛's Archiver

php连接SqlServer并实现增删改查