PHP与MySQL交互

<?php
$con = mysql_connect("localhost","root","12345");

$dbcharset = "utf8";
mysql_query("SET character_set_connection=$dbcharset, character_set_results=$dbcharset, character_set_client=binary");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// some code

mysql_select_db("my_db", $con);
?>

 1.php连接数据库

$mysqli = mysqli_connect("hostname", "username", "password", "database");

<?php
    $mysqli = mysqli_connect("localhost", "root", "12345", "test");
    //错误信息
    if(mysqli_connect_errno()) {
        //错误信息
        printf("Connect failed: %s
", mysqli_connect_error());
        exit();
    } else {
        //输出Host信息
        printf("Host infomation: %s
", mysqli_get_host_info($mysqli));
        //显示关闭连接
        mysqli_close($mysqli);
    }
?>

旧版使用mysql函数集

2.查询

<?php
    $mysqli = mysqli_connect("localhost", "root", "12345", "test");
    //错误信息
    if(mysqli_connect_errno()) {
        //错误信息
        printf("Connect failed: %s
", mysqli_connect_error());
        exit();
    } else {
        $sql = "CREATE TABLE testTable (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, testField VARCHAR(75))";
        $res = mysqli_query($mysqli, $sql);
        if($res === TRUE) {
            echo "Table testTable successfully created.";
        } else {
            //获取MYSQL错误信息
            printf("Could not create table: %s
", mysqli_error($mysqli));
        }
        //显示关闭连接
        mysqli_close($mysqli);
    }
?>
mysqli_error($mysqli)可以获取错误信息

3.mysqli_query使用Mysql数据

3.1避免SQL注入

在一个登陆过程中，查询过程大概如下

SELECT * FROM users WHERE name = '".$_POST['username']."';

假设username的值为 ' or  '1' = '1

这会产生一个完整的查询如

SELECT * FROM users Where name = '  '  or  '1' = 1;

这个查询结果总是为真。 这就是sql注入。

要避免注入就要限制用户的输入，比如特殊字符（and, or）等都禁止提交。

3.2插入数据

插入表单中的数据

表单提交页面

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>
<form method="post" action="insert.php" >
    <input type="text" name='testfield' />
    <input type="submit" />
</form>
<body>
</body>
</html>

处理页面insert.php

<?php
    $mysqli = mysqli_connect("localhost", "root", "12345", "test");
    //错误信息
    if(mysqli_connect_errno()) {
        //错误信息
        printf("Connect failed: %s
", mysqli_connect_error());
        exit();
    } else {
        // 超全局变量$_POST访问表单
        $clean_text = mysqli_real_escape_string($mysqli, $_POST['testfield']);
        $sql = "INSERT INTO testTable (testField) 
                VALUES ('".$clean_text."')";
        $res = mysqli_query($mysqli, $sql);
        if($res === TRUE) {
            echo "插入数据成功";
        } else {
            //获取MYSQL错误信息
            printf("插入数据失败: %s
", mysqli_error($mysqli));
        }
        //显示关闭连接
        mysqli_close($mysqli);
    }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>
<body>
</body>
</html>

3.3php获取数据

3.31查询结果个数

<?php
    $mysqli = mysqli_connect("localhost", "root", "12345", "test");
    //错误信息
    if(mysqli_connect_errno()) {
        //错误信息
        printf("Connect failed: %s
", mysqli_connect_error());
        exit();
    } else {
        $sql = "SELECT * FROM testTable";
        $res = mysqli_query($mysqli, $sql);
        if($res) {
            //mysqli_num_rows获取结果个数
            $number_of_rows = mysqli_num_rows($res);
            printf("拥有%s组结果:", $number_of_rows);
        } else {
            //获取MYSQL错误信息
            printf("查询失败: %s
", mysqli_error($mysqli));
        }
        //mysqli_free_result释放结果内存供其它脚本使用
        mysqli_free_result($res);
        //显示关闭连接
        mysqli_close($mysqli);
    }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>
<body>
</body>
</html>
mysqli_free_result释放结果内存供其它脚本使用，mysqli_num_rows获取结果个数

3.32显示查询结果

<?php
    $mysqli = mysqli_connect("localhost", "root", "12345", "test");
    //错误信息
    if(mysqli_connect_errno()) {
        //错误信息
        printf("Connect failed: %s
", mysqli_connect_error());
        exit();
    } else {
        $sql = "SELECT * FROM testTable";
        $res = mysqli_query($mysqli, $sql);
        if($res) {
            //mysqli_fetch_array获取每一行构成的数组
            while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
                $id = $newArray['id'];
                $testField = $newArray['testField'];
                echo "第".$id."条的内容为".$testField."。";
            }
        } else {
            //获取MYSQL错误信息
            printf("查询失败: %s
", mysqli_error($mysqli));
        }
        //mysqli_free_result释放结果内存供其它脚本使用
        mysqli_free_result($res);
        //显示关闭连接
        mysqli_close($mysqli);
    }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>
<body>
</body>
</html>

更多的MySQLi部分http://www.php.net/mysqli

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