mysql连接php增删改查预处理_php连接MySQL数据库及增删改查

1、连接MySQL数据库$conn = new mysqli($host,$user,$password,$db);

$conn->set_charset("utf8");

1.1 判断连接状态

if ($conn->connect_error) {

die("数据库连接异常");

}

//数据库的创建

$db = "create database db";

if($conn -> query($db)){

echo "成功创建数据库";

}else{

echo "创建失败";

}

//数据表的创建

$table = "create table table_name(

id int(10) auto_increment primary key,

user varchar(20) NOT NULL

);";

//1.1.1 添加数据方法1:预处理语句及绑定参数方法(适合多条查询语句的执行)

$table = $conn -> prepare("insert into user_table(id,user) values(NULL,?)");//准备sql语句

$name = '小明';

$table -> bind_param("s",$name); //(类型:s,类型值);参数s为string,i为interger,d为double,类型值仅可为一变量

if ($table -> execute() && $conn -> affected_rows){ //判断是否添加成功

echo "添加成功";

}else{

echo "添加失败";

}

//1.1.1 添加数据方法二:直接调用query()方法(适合单条语句;多条用";"隔开,用multi_query()方法查询);

$insert = "insert into table(name) values('你好')";

if ($conn -> query($insert) && $conn -> affected_rows){

echo "成功添加";

}else{

echo "添加失败";

}

//1.1.2 删除数据方法1直接调用query()方法;

$delete = "delete from user_table where user in('张三','1') or id in(1)";//删除user='张三'的列名 或 id=1的列名

if($conn -> query($delete) && $row = $conn -> affected_rows){

echo "删除成功,一共:$row"."行";

}else{

ehco "删除失败";

}

//1.1.2 删除数据方法2:预处理语句及绑定参数

$delete = $conn -> prepare("delete from user_table where id in(?) or user in(?);");

$id = 1;

$name = '张三';

$delete -> bind_param('ii',$id,$name);

if($delete -> execute() && $row = $conn -> affected_rows){

echo "删除成功,一共:$row"."行";

}else{

echo "删除失败";

}

//1.1.3 更改数据方法1:直接query()方法

$update = "update user_table set user = '刘老师' where id = '115';";

if($conn -> query($update) && $row = $conn -> affected_rows){

echo "更改:$row"."行";

}else{

echo "更改失败";

}

//1.1.3 更改数据方法2:预处理语句及绑定参数

$update = $conn -> prepare("update user_table set user = ? where id = ?;");

$user = "黄老师";

$id = 1;

$update -> bind_param("si",$user,$id);

if($update -> execute() && $row = $conn -> affected_rows){

echo "更改:$row"."行";

}else{

echo "更改失败";

}

//1.1.4 查询数据方法:直接query()方法

$select = "select id,user from user_table ORDER BY user,id DESC ";

$result = $conn -> query($select);

if($result -> num_rows>0){　　　　　　　　　　　　　　// ORDER BY排序 DESC降序排序

while ($row = $result -> fetch_array()){　　　　//循环查询并返回指定条件的每条语句

echo "$row[id]:$row[user]
";

}

}else{

echo "没有数据";

}

表关联合查询

$res = $db->query("select message.id,message.title,message.hits,COUNT(comment.id) from message LEFT JOIN comment ON message.id=comment.idGROUP BY message.idORDER BY COUNT(comment.id) DESC");

while ($row = $res -> fetch_assoc()){

echo '文章id:'.$row['id'].'
';

echo '文章标题:'.$row['title'].'
';

echo '文章点击率:'.$row['hits'].'
';

echo '文章评论数量:'.$row['COUNT(comment.id)'].'
';

}