mysql> create table user(

    -> id int primary key auto_increment,

    -> name varchar(32) not null,

    -> password varchar(64) not null,

    -> email varchar(128) not null,

    -> age tinyint unsigned not null

-> );

mysql> insert into user (name,password,email,age) values("gjp",md5(123456),'gjp@

sohu.com',24);

mysql> insert into user (name,password,email,age) values("郭卢",md5(123456),'郭

路@sohu.com',24);

wps_clip_image-29793

wps_clip_image-2147

Mysql客户端的限制,只能接受gbk码,utf8不支持,数据库是支持的

<?php

$conn=mysql_connect("localhost","root","123456");

if(!$conn)

{

die("出错了".mysql_errno());

}

mysql_select_db("test",$conn) or die(mysql_errno());

mysql_query("set names utf8");

$sql="insert into user (name,password,email,age)  values('zhangsan',md5('123'),'zs@163.com',34)";

$res=mysql_query($sql,$conn);

if(!$res)

{

echo "操作失败";

}

if (mysql_affected_rows($conn)>0)

{

echo "操作成功!";

}else{ 

echo "没有受影响的行数!";

}

mysql_close($conn); //要不要无所谓

?>

wps_clip_image-8479

wps_clip_image-21396

Sql语句换成删除:

$sql="delete  from user where id=4";

wps_clip_image-5478

$sql="update user set age=26 where name='lzw'";

wps_clip_image-5836

将上面的文件,封装成类,提高复用性!

两个文件:

index.php

1. <?php

2. require_once 'Sqltool.php';

3. $sql="insert into user(name,password,email,age)  values('lisi',md5('123'),'ls@163.com',36)";

4. $sqlTool=new Sqltool();

5. $res=$sqlTool->execute_dml($sql);

6. if($res==0){

7.  echo "失败";

8. }else if($res==1){

9.  echo "success";

10. }else if($res==2){

11.  echo "没有受影响的行数";

12. }

13. ?>

Sqltool.php

<?php

class Sqltool

{

private $conn;

private $host="localhost";

private $user="root";

private $password="123456";

private $db="test";

function Sqltool()

  {

    $this->conn=mysql_connect($this->host,$this->user,$this->password);

if(!$this->conn)

{

die("fail".mysql_error());

}

mysql_select_db($this->db,$this->conn);

    mysql_query("set names utf8");

  }

//dql 针对select

public function execute_dql($sql)

  {

$res=mysql_query($sql,$this->conn)or die(mysql_error());

return $res;

  }

// dml语句是针对update delete insert 命令,返回值为true false

public function execute_dml($sql)

  {

echo $sql;

    $b=mysql_query($sql,$this->conn);

if(!$b)

    {

return 0;

    }else {

if(mysql_affected_rows($this->conn)>0)

{

return 1;

}else{

return 2;

}

    }

  }

}

?>

wps_clip_image-1721

命令改为$sql="delete from user where id=3";

wps_clip_image-16513

//DQL语句获取的是结果集,执行这的时候,要将dml语句先注释掉

$sql="select * from user";

$sqlTool=new Sqltool();

$res=$sqlTool->execute_dql($sql);

while($row=mysql_fetch_row($res))

{

foreach ($row as $key=>$val)

{

echo "--$val";

}

echo "<br/>";

}

mysql_free_result($res);

执行结果如下:

wps_clip_image-1199

MYSQli的讲解2!

在php.ini 中开启

extension=php_mysqli.dll

例1:使用面向对象的方式

<?php

//header("Content-type: text/html; charset=utf-8");

//1.创建mysql对象

$msi=new mysqli("localhost","root","123456","test");

//验证是否ok

if($msi->cononnect_error){

die("连接失败".$msi->connect_error);

}else {

echo "连接ok"."</br>";

}

//2.操作数据库(发送sql命令)

$sql="select * from user ";

$res=$msi->query($sql);

/* echo "</br>";

print "<pre>";

var_dump($res);

print"</pre>"; */

//3.处理结果

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

foreach ($row as $key=>$val){

echo "--$val";

}

echo "<br/>";

}

//4.关闭资源

//释放内存;

$res->free();

//关闭链接

$msi->close();

?>

wps_clip_image-30977

由于上面少了这个$msi->query("set names utf8");所以出现汉字的乱码

wps_clip_image-22824

正常了!

例2:用面向过程的方法

<?php

header("Content-type: text/html; charset=utf-8");

//1.得到mysqli连接

$msi=mysqli_connect("localhost","root","123456","test");

if(!$msi){

die("连接失败".mysqli_connect_errno($msi));

}

//2.向数据库发送sql语句(ddl dml dql)

$sql="select * from user";

$res=mysqli_query($msi,$sql);

//var_dump($res);

//3.处理得到的结果

//循环取出结果集

while($row=mysqli_fetch_row($res)){

foreach($row as $key=>$val){

echo "--$val";

}

echo "<br/>";

}

//4.关闭资源

mysqli_free_result($res);

mysqli_close($msi);

?>

wps_clip_image-16975

wps_clip_image-26420

如何区分这几个?

通过上面的程序修改演示:

while($row=mysqli_fetch_row($res)){

print "<pre>";

var_dump($row);

print "</pre>";

echo "<br/>";

}

wps_clip_image-8174

上面是以下标

while($row=mysqli_fetch_assoc($res)){

print "<pre>";

var_dump($row);

print "</pre>";

echo "<br/>";

}

wps_clip_image-26161

上面是以字段名,如id

wps_clip_image-19971

wps_clip_image-752

例3:

<?php

//1.创建mysql对象

$msi=new mysqli("localhost","root","123456","test");

//验证是否ok

if($msi->cononnect_error){

die("连接失败".$msi->connect_error);

}else {

echo "连接ok"."</br>";

}

//2.操作数据库(发送sql命令)

$msi->query("set names utf8");

$sql="insert into user  (name,password,email,age) values('李想',md5('aaaa'),'lixiang@163.com',18)";

$res=$msi->query($sql);

//3.处理结果

if(!$res){

echo "操作失败".$msi->error;

}else{

//影响多少行记录

if($msi->affected_rows>0){

echo "执行ok";

}else{

echo "没有受影响的行数";

}

}

//4.关闭资源

//关闭链接

$msi->close();

?>

wps_clip_image-2667

执行了2次,添加了2条

wps_clip_image-32479

把sql语句改为:(不存在的id)

$sql="update  user set name='haha' where id=11";

wps_clip_image-18373

$sql="update  user set name='haha' where id=9";

wps_clip_image-25984

$sql="delete from user where name='haha'";

增删改都使用了,这里我们来封装成工具类

wps_clip_image-8570

Mysqli封装为工具类sqlHelper:

<?php

class sqlHelper{

private $mysqli;

private static $host="localhost";

private static $user="root";

private static $pwd="123456";

private static $db="test";

//下面这个函数__construct()其实是两个下划线,下面由于我写了一个,所以无法自动调用

该函数,需要手动调,如果写成两个_,创建对象时,则会自动调!

public function _construct(){

$this->mysqli=new  mysqli(self::$host,self::$user,self::$pwd,self::$db);

if($this->mysqli->connect_error){

die("连接失败".$this->mysqli->connect_error);

}else{

echo "success";  //为了调试

}

//设置访问数据库的字符集,保证php是以utf8的方式来操作我们的mysql数据库

$this->mysqli->query("set names utf8");

}

public function execute_dql($sql){

$res=$this->mysqli->query($sql) or die("操作dql".$this->mysqli->error);

return  $res;

}

public function execute_dml($sql){

echo $sql; //为了调试

$res=$this->mysqli->query($sql) or die("操作

dql".$this->mysqli->error);

echo $res;//为了调试

if(!$res){

return 0;

}else{

if($this->mysqli->affected_rows>0){

return 1;

}else {

return 2;

}

}

}

}

使用工具类,实现其功能:

<?php

require_once 'sqlHelper.php';

//header("Content-type: text/html;charset=utf-8");

//创建sqlHelper对象

$sqlhelper=new sqlHelper();

$sql="insert into user(name,password,email,age) values('卢伟da',md5('aaaa'),'lzw@sohu.com','8')";

$sqlhelper->_construct();

$res=$sqlhelper->execute_dml($sql);

if($res==0){

echo "失败";

}else {

if($res==1){

echo "恭喜,成功!";

}else{

echo "没有受影响的行数";

}

}

?>

wps_clip_image-30923

操作数据库成功如下:

wps_clip_image-18311