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\n", mysqli_connect_error());
exit();
} else {
//输出Host信息
printf("Host infomation: %s\n", 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\n", 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\n", 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\n", 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\n", 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\n", 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\n", 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\n", 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\n", 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

上一篇:Masonry的一些使用。


下一篇:Python安装与环境变量配置 入门详解 - 精简归纳