PHP学习笔记:在php中访问MySQL数据（一些简单的例子）

说明：代码来源《PHP和MySQL Web应用开发》一书，还有就是代码有些是经过修改的，经过我的测验全部都可以用

这是数据库部分代码

//创建数据库
create database MySQLDB;

//使用数据库
use mysqldb;

//向数据库中插入表格
create table Employees(EmpName varchar(50) primary key,DepName varchar(50),Salary integer);


//修改列名
alter table employees change column DepName Title varchar(50);

//增加列
alter table employees add column DepName varchar(50);

//向表格插入数据
insert into Employees values('张三','部门经理',6000,'人事部');
//插入出错，在插入前加set names gbk;  就ok了；http://www.cnblogs.com/sunzn/archive/2013/03/14/2960248.html
//错误提示,Incorrect string value:"XX XX XX XX XX"for column 'XXX' at row 1

ALTER DATABASE database_name CHARACTER SET GBK;(修改database)
ALTER DATABASE mysqldb CHARACTER SET GBK;
//不可行

insert into Employees values('李四','职员',3000,'人事部');
insert into Employees values('王五','职员',3500,'服务部');
insert into Employees values('赵六','部门经理',6500,'开发部');
insert into Employees values('高七','职员',2500,'开发部');
insert into Employees values('马八','职员',3100,'人事部');
insert into Employees values('钱九','部门经理',5000,'财务部');
insert into Employees values('孙十','职员',2800,'财务部');

删除列：alter table 表名 drop column 列名;

CreateTable.php　　使用php在MySQL中创建表

<?PHP
    //创建数据库之后用这代码去创建表users
    $conn = mysqli_connect("localhost", "root", "123456", "MySQLDB");
    if (empty($conn)) {
        die("mysqli_connect failed: " . mysqli_connect_error());
    } 
    //执行CREATE TABLE 语句
    $sql = "CREATE TABLE IF NOT EXISTS Users (UserName VARCHAR(50) PRIMARY KEY, UserPwd VARCHAR(50), ShowName VARCHAR(50))";    
    $conn->query($sql);
    // 关闭连接
    mysqli_close($conn);
?>

FetchData.php　　在网页中显示MySQL数据库中表的内容示例

<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″> 
<?PHP

    $conn = mysqli_connect("localhost", "root", "123456", "MySQLDB");
    if (empty($conn)) {
        die("mysqli_connect failed: " . mysqli_connect_error());
    } 
    mysqli_query($conn, "SET NAMES utf8");
    // 查询Employees中的员工数据
    $sql = "SELECT EmpName, Title, Salary FROM Employees";    
    $results = $conn->query($sql);
    // 循环处理结果集中的记录
    while($row = $results->fetch_row())  {
        print($row[0] . "  " . $row[1] . "  " . $row[2] . "<BR>");
    }
    $results->free();
    // 关闭连接
    mysqli_close($conn);
    /*
    乱码问题：
    加 <meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″> 和 mysqli_query($conn, "SET NAMES utf8");
    来统一编码，from:http://jingyan.baidu.com/article/77b8dc7fcae06e6174eab6ba.html
    */
?>

multi-query.php　　使用$conn->multi_query()函数同时执行两个select语句的示例

<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″> 
<?php
    $conn = mysqli_connect("localhost", "root", "123456", "MySQLDB");
    if (empty($conn)) {
        die("mysqli_connect failed: " . mysqli_connect_error());
    }
    mysqli_query($conn, "SET NAMES utf8");
    $query = "SELECT EmpName FROM Employees;";
    $query .= " SELECT DepName FROM Departments;";
    if ($conn->multi_query($query)) {
        do {
            if ($result = $conn->store_result()) {
                while ($row = $result->fetch_row()) {
                    echo($row[0] . "<br>");
                } 
                $result->close(); 
            }
        } while ($conn->next_result()); 
    }
    $conn->close();
    /*
    使用multi_query()函数同时执行两个select语句，分别查询员工姓名和部门名称
    */
?>

viewPage.php　　php分页显示MySQL数据中表记录的代码示例

<HTML>
<HEAD><TITLE>分页显示记录</TITLE>
<meta http-equiv="Content-Type" content="text/html; charset=utf8" />
</HEAD>
<BODY>
<?PHP
    // 获取当前页码
    $page = $_GET['page'];
    if($page == 0)
        $page = 1;

    $PageSize = 3;        // 为了演示分页效果，
    // 连接到数据库
    $conn = mysqli_connect("localhost", "root", "123456", "MySQLDB");
    if (empty($conn)) {
        die("mysqli_connect failed: " . mysqli_connect_error());
    } 
    // 设置中文字符集
    mysqli_query($conn, "SET NAMES utf8");
    //执行SELECT 语句，获取表Employees的记录总数
    $sql = "SELECT COUNT(1) FROM Employees";    
    $results = $conn->query($sql);
    $row = $results->fetch_row();
    $RecordCount = $row[0];
    //////////////
    // 计算总页数//
    /////////////
    if( $RecordCount ){ 
        //如果记录总数量小于每页显示的记录数量，则只有一页
        if( $RecordCount < $PageSize ){ 
            $PageCount = 1; 
        }  
        //取记录总数量不能整除每页显示记录的数量，则页数等于总记录数量除以每页显示记录数量的结果取整再加1
        if( $RecordCount % $PageSize ){ 
            $PageCount = (int)($RecordCount / $PageSize) + 1;
        }
        else {    //如果没有余数，则页数等于总记录数量除以每页显示记录的数量
            $PageCount = $RecordCount / $PageSize; 
        }
    }
    else{  // 如果结果集中没有记录，则页数为0
        $PageCount = 0;
    }
    echo("<BR>当前页码 :" . $page . "/" . $PageCount);

?>
<table width="449" border="1">
  <tr>
    <td>员工姓名</td>
    <td>职务</td>
    <td>工资</td>
  </tr>
<?PHP
    // 循环显示当前页的记录
    $sql = "SELECT EmpName, Title, Salary FROM Employees LIMIT " . ($page-1) * $PageSize . "," . $PageSize;
    $results = $conn->query($sql);
    while($row = $results->fetch_row())  {  
        echo("<tr>");
        echo("<td>" . $row[0] . "&nbsp;</td>");
        echo("<td>" . $row[1] . "&nbsp;</td>");
        echo("<td>" . $row[2] . "&nbsp;</td>");
        echo("</tr>");
    }
    // 关闭连接
    mysqli_close($conn);
    // 显示分页链接
    if($page == 1)
        echo("第一页 ");
    else 
        echo(" <a href=viewPage.php?page=1>第一页</a> ");
    // 设置“上一页”链接
    if($page == 1)
        echo(" 上一页 ");
    else 
        echo(" <a href=viewPage.php?page=" . ($page-1) . ">上一页</a> ");
    // 设置“下一页”链接
    if($page == $PageCount)
        echo(" 下一页 ");
    else 
        echo(" <a href=viewPage.php?page=" . ($page+1) . ">下一页</a> ");
    //设置“最后一页”链接
    if($page == $PageCount)
        echo(" 最后一页 ");
    else 
        echo(" <a href=viewPage.php?page=" . $PageCount . ">最后一页</a> ")
?>
</table>
</BODY>
</HTML>

 截图：

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yzmb/p/4625091.html