<?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