配置文件
config.php
<?php
//首先创建连接数据库配置文件
//使用常量方法
header('content-type:text/html;charset=utf-8');
define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PSD','root');
define('DB_NAME','demo1');
define('DB_CHAR','utf8');connect.php文件
<?php
//引入配置文件
require 'config.php';
header('content-type:text/html;charset=utf-8');
$mysqli = new mysqli(DB_HOST,DB_USER,DB_PSD,DB_NAME);
if ($mysqli->connect_errno)
{
echo '<p>';
echo '连接数据库情况：';
echo '连接失败'.$mysqli->connect_error;
echo '</p>';
}else
{
echo '<p>';
echo '连接数据库情况：';
echo '连接成功';
echo '</p>';
}
$mysqli->set_charset(DB_CHAR);2.预处理插入语句
<?php
require 'public/connect.php';
//先查询再插入
//1.查询，如果表中有则不插入，没有则插入数据
//2.插入语句
//3.关闭数据库连接
//第一步
//准备插入的语句
$data = ['name'=>'郭芙','email'=>'gf@php.cn', 'psd'=>'123'];
//查询语句
$sql = "SELECT * FROM `userLog` WHERE `name`=?";
$Name=$data['name'];
//生成预处理对象
$mysqli_stmt= $mysqli->prepare($sql);
//绑定参数
$mysqli_stmt->bind_param('s',$Name);
//执行预处理语句
if ($mysqli_stmt->execute())
{
//将查询到的结果传递到结果集
$mysqli_stmt->store_result();
if ($mysqli_stmt->num_rows == 0)
{
//插入数据
//创建插入的预处理SQL语句
$sql1="INSERT `userLog` SET `name`=?, `email`=?, `psd`=sha1(?)";
$stmt = $mysqli->prepare($sql1);
//绑定参数
$stmt->bind_param('sss',$data['name'],$data['email'],$data['psd']);
//执行预处理语句
if ($stmt->execute())
{
echo '成功插入'.$stmt->affected_rows;
}else
{
echo '失败'.$stmt->error;
}
//关闭该预处理语句
$stmt->close();
}else
{
echo '禁止重复插入';
}
$mysqli_stmt->free_result(); //释放预处理结果集
$mysqli_stmt->close(); //关闭当前的预处理语句
} else
{
echo '<p>';
echo '插入失败';
echo '<br>错误信息'.$mysqli_stmt->error;
echo '</p>';
}
//最后关闭总的数据库连接
$mysqli->close();3.查询操作及遍历出结果集
<?php
require 'public/connect.php';
//先进行查询工作
$sql = "SELECT `name`,`email` FROM `userLog` WHERE `id`>? ";
//准备查询id
$id = 5;
//生成stmt对象
$mysqli_stmt = $mysqli->prepare($sql);
//参数绑定
$mysqli_stmt->bind_param('i',$id);
//执行预处理语句
if ($mysqli_stmt->execute())
{
$mysqli_stmt->store_result();
if ($mysqli_stmt->num_rows>0)
{
//将结果集中的字段与变量进行绑定,当前返回的有2个字段
$mysqli_stmt->bind_result($name,$email);
//开始遍历结果集,将获取到的数据与绑定的变量结合
echo '<h3 align="center">用户信息表</h3>';
echo '<table border="1" cellspacing="0" cellpadding="3" width="40%" align="center">';
echo '<tr bgcolor="lightblue"><th>姓名</th><th>邮箱</th></tr>';
while ( $mysqli_stmt->fetch())
{
echo '<tr align="center">';
echo '<td>'.$name.'</td><td>'.$email.'</td>';
echo '</tr>';
}
$mysqli_stmt->free_result();
$mysqli_stmt->close();
}else
{
echo '<p>';
echo '没有找到信息';
echo '</p>';
}
}else
{
echo '<p>';
echo '查询失败';
echo '<br>错误信息'.$mysqli_stmt->error;
echo '</p>';
}
//关闭连接
$mysqli->close();
4. 更新操作
<?php
require 'public/connect.php';
//1.查询，如果表中有则更新，没有则返回不存在更新的数据
//2.更新语句
//3.关闭数据库连接
//任务：更新name为杨过的数据
//第一步
$sql = "SELECT `email` FROM `userLog` WHERE `name`=?";
$name='杨过';
$mysqli_stmt= $mysqli->prepare($sql);
//绑定参数
$mysqli_stmt->bind_param('s',$name);
//执行预处理语句
if ($mysqli_stmt->execute())
{
$mysqli_stmt->store_result();
if ($mysqli_stmt->num_rows == 1)
{
//将结果集中的字段与变量进行绑定
//这个语句用于输出结果集，这里不需要
// $mysqli_stmt->bind_result($email);
//更新数据
$Email = 'gxdd@php.cn';
//创建更新的预处理SQL语句
$sql1 = "UPDATE `userLog` SET `email`=? WHERE `name`=? ";
$stmt = $mysqli->prepare($sql1);
//绑定参数
$stmt->bind_param('ss',$Email,$name);
//执行预处理语句
if ($stmt->execute())
{
echo '成功'.$stmt->affected_rows;
}else
{
echo '失败'.$stmt->error;
}
$stmt->close();
}else
{
echo '没有可供更新的数据';
}
$mysqli_stmt->free_result(); //释放预处理结果集
$mysqli_stmt->close(); //关闭当前的预处理语句
} else
{
echo '<p>';
echo '查询失败';
echo '<br>错误信息'.$mysqli_stmt->error;
echo '</p>';
}
//最后关闭总的数据库连接
$mysqli->close();5.删除操作
<?php
require 'public/connect.php';
//1.查询，如果表中有则删除，没有则返回不存在删除的数据
//2.删除语句
//3.关闭数据库连接
//任务：删除name为acer的数据
//第一步
$sql = "SELECT * FROM `userLog` WHERE `name`=?";
$name='acer';
$mysqli_stmt= $mysqli->prepare($sql);
//绑定参数
$mysqli_stmt->bind_param('s',$name);
//执行预处理语句
if ($mysqli_stmt->execute())
{
$mysqli_stmt->store_result();
if ($mysqli_stmt->num_rows == 1)
{
//删除数据
//创建删除的预处理SQL语句
$sql1 = "DELETE FROM `userLog` WHERE `name`=? ";
$stmt = $mysqli->prepare($sql1);
//绑定参数
$stmt->bind_param('s',$name);
//执行预处理语句
if ($stmt->execute())
{
echo '成功删除'.$stmt->affected_rows;
}else
{
echo '失败'.$stmt->error;
}
$stmt->close();
}else
{
echo '没有可供删除的数据';
}
$mysqli_stmt->free_result(); //释放预处理结果集
$mysqli_stmt->close(); //关闭当前的预处理语句
} else
{
echo '<p>';
echo '删除失败';
echo '<br>错误信息'.$mysqli_stmt->error;
echo '</p>';
}
//最后关闭总的数据库连接
$mysqli->close();