php连接SqlServer并实现增删改查

2022-09-26 16:20:46

一、配置环境

　　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文件中加上

extension=php_pdo_sqlsrv_74_ts_x64.dll
extension=php_sqlsrv_74_ts_x64.dll

　　4、重启Apache服务器。

　　5、测试：

$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 "链接成功";
}

php连接SqlServer并实现增删改查

 

 

二、编码设置

（注：由于sql server 不支持UTF8，当使用varchar保存汉字时，会出现乱码。需要转换。）

　　1、从数据库查出来的数据编码“GBK”，一般我们有的是“UTF-8”,所以需要转换成“UTF-8”。

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

　　2、从程序代码中的变量插入的数据库时，需要将变量的编码改成“GBK”才能插入数据库。

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

三、学生信息管理系统

　　1、index.php

php连接SqlServer并实现增删改查 
<?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><p align="center">学号</p></th>
            <th><p align="center">姓名</p></th>
            <th><p align="center">性别</p></th>
            <th><p align="center">年龄</p></th>
            <th><p align="center">学院</p></th>
            <th colspan="2"><p align="center">操作</p></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>
View Code

　　2、add.php

php连接SqlServer并实现增删改查 
<?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="#">
        <p>
            <label for="sno" class="label">学号:</label>
            <input id="sno" name="sno" type="text" class="input" />
        <p/>
        <p>
            <label for="name" class="label">姓名:</label>
            <input id="name" name="name" type="text" class="input" />
        <p/>
        <p>
            <label for="sex" class="label">性别:</label>
            <input name="sex" type="radio" value="男" >男
            <input name="sex" type="radio"value="女">女
        <p/>
        <p>
            <label for="age" class="label">年龄:</label>
            <input id="age" name="age" type="text" class="input" />
        <p/>
        <p>
            <label for="dept" class="label">学院:</label>
            <input id="dept" name="dept" type="text" class="input" />
        <p/>
        <p>
            <input type="reset" value=" 重 置 "/>
            <input type="submit" name="submit" value="  提  交  " class="left" />
        </p>
    </form>
</fieldset>
</body>
</html>
View Code

　　3、undate.php

php连接SqlServer并实现增删改查 
<?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">

        <p>
            <label for="sno" class="label">学号:</label>
            <input id="sno" name="sno" type="text" class="input" value="<?php echo toU8($row[‘Sno‘]); ?>"  />
        <p/>
        <p>
            <label for="name" class="label">真实姓名:</label>
            <input id="name" name="name" type="text" class="input" value="<?php echo toU8($row[‘Sname‘]); ?>"/>
        <p/>
        <p>
            <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‘";
                }

                ?>
            >女
        <p/>
        <p>
            <label for="age" class="label">年龄:</label>
            <input id="age" name="age" type="text" class="input" value="<?php echo toU8($row[‘Sage‘]); ?>"/>
        <p/>
        <p>
            <label for="dept" class="label">学院:</label>
            <input id="dept" name="dept" type="text" class="input" value="<?php echo toU8($row[‘Sdept‘]); ?>"/>
        <p/>
        <p>
            <input type="reset" value="重置"/>
            <input type="submit" name="submit" value="  确 定  " class="left" />
        </p>
    </form>
</fieldset>
</body>
</html>
View Code

　　4、change.php

php连接SqlServer并实现增删改查 
<?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 = "update  Student 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");
}
View Code

　　5、delete.php

php连接SqlServer并实现增删改查 
<?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");
    }
}
View Code

　　6、conn.php

php连接SqlServer并实现增删改查 
<?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);
View Code

四、视图

php连接SqlServer并实现增删改查

 

 php连接SqlServer并实现增删改查

 

 php连接SqlServer并实现增删改查

 

 php连接SqlServer并实现增删改查

 

 php连接SqlServer并实现增删改查

 

 php连接SqlServer并实现增删改查

 

php连接SqlServer并实现增删改查

上一篇：js 时间格式化


下一篇：TensorFlow2.0（9）：TensorBoard可视化