PHP5: mysqli 插入, 查询, 更新和删除 Insert Update Delete Using mysqli (CRUD)

admin 3月前 231

PHP 5 及以上版本建议使用以下方式连接 MySQL :

MySQLi extension (“i” 意为 improved)

PDO (PHP Data Objects)

Mysqli提供了面向对象和面向过程两种方式来与数据库交互,分别看一下这两种方式。

1. PHP 连接 MySQL

1.面向对象

在面向对象的方式中,mysqli被封装成一个类,它的构造方法如下:

__construct ([ string $host [, string $username [, string $passwd [, string $dbname [, int $port [, string $socket ]]]]]] )

在上述语法中涉及到的参数说明如下。

l         host:连接的服务器地址。

l         username:连接数据库的用户名,默认值是服务器进程所有者的用户名。

l         passwd:连接数据库的密码,默认值为空。

l         dbname:连接的数据库名称。

l         port:TCP端口号。

l         socket:UNIX域socket。

 

要建立与MySQL的连接可以通过其构造方法实例化mysqli类,例如下面的代码:

<?php

    $db_host = "localhost";                                       //连接的服务器地址

    $db_user = "root";                                            //连接数据库的用户名

    $db_psw  = "root";                                            //连接数据库的密码

    $db_name = "ikeepstudying";                                   //连接的数据库名称

    // 创建连接

    $conn    = new mysqli($db_host,$db_user,$db_psw,$db_name);

    // 检测连接

    if ($conn->connect_error) {

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

        // 或者使用

        // die('Error : ('. $conn->connect_errno .') '. $conn->connect_error);

    }

    echo "连接成功";

?>

 

mysqli还提供了一个连接MySQL的成员方法connect()。当实例化构造方法为空的mysqli类时,用mysqli对象调用connect()方法同样可连接MySQL,例如,下面的代码:

<?php

    $db_host = "localhost";                                 //连接的服务器地址

    $db_user = "root";                                      //连接数据库的用户名

    $db_psw  = "root";                                      //连接数据库的密码

    $db_name = "ikeepstudying";                             //连接的数据库名称

    $conn    = new mysqli();

    $conn->connect($db_host,$db_user,$db_psw,$db_name);

    // 检测连接

    if ($conn->connect_error) {

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

        // 或者使用

        // die('Error : ('. $conn->connect_errno .') '. $conn->connect_error);

    }

    echo "连接成功";

?>

 

关闭与MySQL服务器的连接通过mysqli对象调用close()方法即可,例如:

1

$conn->close();

2.面向过程

在面向过程的方式中,mysqli扩展提供了函数mysqli_connect()与MySQL建立连接,该函数的语法格式如下:

mysqli_connect ([ string $host [, string $username [, string $passwd [, string $dbname [, int $port [, string $socket ]]]]]] )

mysqli_connect()函数的用法与mysql扩展中的mysql_connect()函数用法十分相似,下面是mysqli_connect()函数的用法示例:

01

<?php

02

    $servername = "localhost";

03

    $username   = "username";

04

    $password   = "password";

05

    $database   = "ikeepstudying";

06

 

07

    // 创建连接

08

    $conn = mysqli_connect($servername, $username, $password, $database);

09

 

10

    // 检测连接

11

    if (!$conn) {

12

        die("Connection failed: " . mysqli_connect_error());

13

    }

14

    echo "连接成功";

15

?>

关闭与MySQL服务器的连接使用mysqli_close()函数,例如:

1

mysqli_close();

 

2. PHP MySQL 插入数据

在创建完数据库和表后,我们可以向表中添加数据。

以下为一些语法规则:

PHP 中 SQL 查询语句必须使用引号

在 SQL 查询语句中的字符串值必须加引号

数值的值不需要引号

NULL 值不需要引号

INSERT INTO 语句通常用于向 MySQL 表添加新的记录:

1

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

以下实例向 “MyGuests” 表添加了新的记录:

1.面向对象

01

<?php

02

$servername = "localhost";

03

$username = "username";

04

$password = "password";

05

$dbname = "ikeepstudying";

06

 

07

// 创建连接

08

$conn = new mysqli($servername, $username, $password, $dbname);

09

// 检测连接

10

if ($conn->connect_error) {

11

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

12

}

13

 

14

$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";

15

 

16

if ($conn->query($sql) === TRUE) {

17

    echo "新记录插入成功";

18

} else {

19

    echo "Error: " . $sql . "<br>" . $conn->error;

20

}

21

 

22

$conn->close();

23

?>

 

插入多条记录:

01

<?php

02

$servername = "localhost";

03

$username = "username";

04

$password = "password";

05

$dbname = "ikeepstudying";

06

 

07

// 创建链接

08

$conn = new mysqli($servername, $username, $password, $dbname);

09

// 检查链接

10

if ($conn->connect_error) {

11

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

12

}

13

 

14

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

15

VALUES ('John', 'Doe', 'john@example.com');";

16

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

17

VALUES ('Mary', 'Moe', 'mary@example.com');";

18

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

19

VALUES ('Julie', 'Dooley', 'julie@example.com')";

20

 

21

if ($conn->multi_query($sql) === TRUE) {

22

    echo "新记录插入成功";

23

} else {

24

    echo "Error: " . $sql . "<br>" . $conn->error;

25

}

26

 

27

$conn->close();

28

?>

请注意,每个SQL语句必须用分号隔开。

 

2.面向过程

01

<?php

02

$servername = "localhost";

03

$username = "username";

04

$password = "password";

05

$dbname = "ikeepstudying";

06

 

07

// 创建连接

08

$conn = mysqli_connect($servername, $username, $password, $dbname);

09

// 检测连接

10

if (!$conn) {

11

    die("Connection failed: " . mysqli_connect_error());

12

}

13

 

14

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

15

VALUES ('John', 'Doe', 'john@example.com')";

16

 

17

if (mysqli_query($conn, $sql)) {

18

    echo "新记录插入成功";

19

} else {

20

    echo "Error: " . $sql . "<br>" . mysqli_error($conn);

21

}

22

 

23

mysqli_close($conn);

24

?>

 

插入多条记录:

01

<?php

02

$servername = "localhost";

03

$username = "username";

04

$password = "password";

05

$dbname = "ikeepstudying";

06

 

07

// 创建链接

08

$conn = mysqli_connect($servername, $username, $password, $dbname);

09

// 检查链接

10

if (!$conn) {

11

    die("连接失败: " . mysqli_connect_error());

12

}

13

 

14

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

15

VALUES ('John', 'Doe', 'john@example.com');";

16

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

17

VALUES ('Mary', 'Moe', 'mary@example.com');";

18

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

19

VALUES ('Julie', 'Dooley', 'julie@example.com')";

20

 

21

if (mysqli_multi_query($conn, $sql)) {

22

    echo "新记录插入成功";

23

} else {

24

    echo "Error: " . $sql . "<br>" . mysqli_error($conn);

25

}

26

 

27

mysqli_close($conn);

28

?>

php5和新增了获取最新插入值的ID的函数:mysqli_insert_id($conn),和之前的mysql_insert_id()类似。

都是用在insert语句之后获取最新的自动id号,要注意的是mysqli_insert_id(),括号里面的一定要是数据库连接而不是执行结果$result。

程序实例:

1

$conn   = new mysqli("127.0.0.1","root","","ikeepstudying") or die("数据库连接超时!");

2

$sql    = "insert into my_table (name,url) values('ikeepstudying','http://justcode.ikeepstudying.com')";

3

$result = $conn->query($sql) or die("失败");

4

 

5

echo mysqli_insert_id($conn); //输出刚刚插入数据的ID,注意:括号里面的要是$conn而不是$result

 

3. PHP MySQL 预处理语句

预处理语句对于防止 MySQL 注入是非常有用的。使用预准备语句可提高重复使用语句的性能,在PHP中,使用prepare()方法来进行预准备语句查询,使用execute()方法来执行预准备语句。PHP有两种预准备语句:一种是绑定结果,另一种是绑定参数。

 

预处理语句, 绑定参数及绑定结果

预处理语句用于执行多个相同的 SQL 语句,并且执行效率更高。

预处理语句的工作原理如下:

预处理:创建 SQL 语句模板并发送到数据库。预留的值使用参数 “?” 标记 。例如:

INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)

数据库解析,编译,对SQL语句模板执行查询优化,并存储结果不输出。

执行:最后,将应用绑定的值传递给参数(”?” 标记),数据库执行语句。应用可以多次执行语句,如果参数的值不一样。

相比于直接执行SQL语句,预处理语句有两个主要优点:

预处理语句大大减少了分析时间,只做了一次查询(虽然语句多次执行)。

绑定参数减少了服务器带宽,你只需要发送查询的参数,而不是整个语句。

预处理语句针对SQL注入是非常有用的,因为参数值发送后使用不同的协议,保证了数据的合法性。

 

MySQLi 预处理语句及绑定参数

所谓绑定参数就是把PHP脚本中的自定义变量绑定到SQL语句中的参数(参数使用 “?”代替)上,绑定参数使用bind_param()方法,该方法的语法格式如下:

bool bind_param ( string $types , mixed &$var1 [, mixed &$… ] )

以下实例在 MySQLi 中使用了预处理语句,并绑定了相应的参数:

01

<?php

02

$servername = "localhost";

03

$username = "username";

04

$password = "password";

05

$dbname = "ikeepstudying";

06

 

07

// 创建连接

08

$conn = new mysqli($servername, $username, $password, $dbname);

09

 

10

// 检测连接

11

if ($conn->connect_error) {

12

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

13

}

14

 

15

// 预处理及绑定

16

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)");

17

$stmt->bind_param("sss", $firstname, $lastname, $email);

18

 

19

// 设置参数并执行

20

$firstname = "John";

21

$lastname = "Doe";

22

$email = "john@example.com";

23

$stmt->execute();

24

 

25

$firstname = "Mary";

26

$lastname = "Moe";

27

$email = "mary@example.com";

28

$stmt->execute();

29

 

30

$firstname = "Julie";

31

$lastname = "Dooley";

32

$email = "julie@example.com";

33

$stmt->execute();

34

 

35

echo "新记录插入成功";

36

 

37

$stmt->close();

38

$conn->close();

39

?>

解析以下实例的每行代码:

1

"INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)"

在 SQL 语句中,我们使用了问号 (?),在此我们可以将问号替换为整型,字符串,双精度浮点型和布尔值。

接下来,让我们来看下 bind_param() 函数:

1

$stmt->bind_param("sss", $firstname, $lastname, $email);

该函数绑定了 SQL 的参数,且告诉数据库参数的值。 “sss” 参数列处理其余参数的数据类型。s 字符告诉数据库该参数为字符串。

types:绑定的变量的数据类型,它接受的字符种类包括4个,如表所示。

字符种类

代表的数据类型

i

integer

d

double

s

string

b

blob

参数types接受的字符的种类和绑定的变量需要一一对应。

通过告诉数据库参数的数据类型,可以降低 SQL 注入的风险。

注意: 如果你想插入其他数据(用户输入),对数据的验证是非常重要的。

 

MySQLi 预处理语句及绑定结果

所谓绑定结果就是把PHP脚本中的自定义变量绑定到结果集中的相应字段上,这些变量就代表着所查询的记录,绑定结果的示例代码如下:

01

<?php

02

    $mysqli = new mysqli("localhost","root","root","ikeepstudying");     //实例化mysqli

03

    $query  = "select * from MyGuests";

04

    $result = $mysqli->prepare($query);                                  //进行预准备语句查询

05

    $result->execute();                                                  //执行预准备语句

06

    $result->bind_result($id,$firstname,$lastname,$email);                        //绑定结果

07

    while ($result->fetch()) {

08

        echo $id;

09

        echo $firstname;

10

        echo $lastname;

11

        echo $email;

12

    }

13

    $result->close();                                                     //关闭预准备语句

14

?>

在绑定结果的时候,脚本中的变量要与结果集中的字段一一对应,绑定完以后,通过fetch()方法将绑定在结果集中的变量一一取出来,最后将预准备语句和数据库连接分别关闭。

 

MySQLi 同时绑定参数和绑定结果

在一个脚本中还可以同时绑定参数和绑定结果,示例代码如下:

01

<?php

02

    $conn   = new mysqli("localhost","root","root","ikeepstudying");     //实例化mysqli

03

    $query  = "select * from MyGuests where id < ?";

04

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

05

    $result->bind_param("i",$id);                                    //绑定参数

06

    $id=4;

07

    $result->execute();

08

    $result->bind_result($id,$number,$name,$age);                   //绑定结果

09

    while ($result->fetch()) {

10

        echo $id;

11

        echo $firstname;

12

        echo $lastname;

13

        echo $email;

14

    }

15

 

16

    $result->close();

17

    $conn->close();

18

?>

 

4. PHP MySQL 读取数据, 删除及更新

SELECT 语句用于从数据表中读取数据:

1

SELECT column_name(s) FROM table_name

在mysqli中,执行查询使用query()方法,该方法的语法格式如下:

mixed query ( string $query [, int $resultmode ] )

在上述语法中涉及到的参数说明如下。

l         query:向服务器发送的SQL语句。

l         resultmode:该参数接受两个值,一个是MYSQLI_STORE_RESULT,表示结果作为缓冲集合返回;另一个是MYSQLI_USE_RESULT,表示结果作为非缓冲集合返回。

下面是使用query()方法执行查询的例子:

1.面向对象

01

<?php

02

    $mysqli = new mysqli("localhost","root","root","ikeepstudying");     //实例化mysqli

03

    $query  = "select * from MyGuests";

04

    $result = $mysqli->query($query);

05

    if ($result) {

06

        if($result->num_rows>0){                                               //判断结果集中行的数目是否大于0

07

            while($row = $result->fetch_array() ){                        //循环输出结果集中的记录

08

                echo ($row[0])."<br>";

09

                echo ($row[1])."<br>";

10

                echo ($row[2])."<br>";

11

                echo ($row[3])."<br>";

12

                echo "<hr>";

13

            }

14

        }

15

    }else {

16

        echo "查询失败";

17

    }

18

    $result->free();

19

    $mysqli->close();

20

?>

在上面代码中,num_rows为结果集的一个属性,返回结果集中行的数目。方法fetch_array()将结果集中的记录放入一个数组中并将其返回。最后使用free()方法将结果集中的内存释放,使用close()方法将数据库连接关闭。

 对于删除记录(delete)、保存记录(insert)和修改记录(update)的操作,也是使用query()方法来执行的,下面是删除记录的例子:

01

<?php

02

    $mysqli = new mysqli("localhost","root","root","ikeepstudying");              //实例化mysqli

03

    $query  = "delete from MyGuests where id=2";

04

    $result = $mysqli->query($query);

05

    if ($result){

06

        echo "删除操作执行成功";

07

    }else {

08

        echo "删除操作执行失败";

09

    }

10

    $mysqli->close();

11

?>

 

完整实例:

01

<?php

02

    $servername = "localhost";

03

    $username = "username";

04

    $password = "password";

05

    $dbname = "ikeepstudying";

06

     

07

    // 创建连接

08

    $conn = new mysqli($servername, $username, $password, $dbname);

09

    // 检测连接

10

    if ($conn->connect_error) {

11

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

12

    }

13

     

14

    $sql = "SELECT id, firstname, lastname FROM MyGuests";

15

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

16

     

17

    if ($result->num_rows > 0) {

18

        // 输出每行数据

19

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

20

            echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. " " . $row["lastname"];

21

        }

22

    } else {

23

        echo "0 个结果";

24

    }

25

    $conn->close();

26

?>

 

更多实例:

 

INSERT

1

$stmt = $mysqli->prepare("INSERT INTO movies(filmName,

2

filmDescription,filmImage,filmPrice,filmReview) VALUES (?, ?, ?, ?, ?)");

3

$stmt->bind_param('sssdi', $_POST['filmName'],

4

$_POST['filmDescription'],

5

$_POST['filmImage'],

6

$_POST['filmPrice'],

7

$_POST['filmReview']);

8

$stmt->execute();

9

$stmt->close();

 

Getting Auto Increment Key Values with insert_id

01

$stmt = $mysqli->prepare("INSERT INTO movies(filmName,

02

filmDescription,filmImage,filmPrice,filmReview) VALUES (?, ?, ?, ?, ?)");

03

$stmt->bind_param('sssdi', $_POST['filmName'],

04

$_POST['filmDescription'],

05

$_POST['filmImage'],

06

$_POST['filmPrice'],

07

$_POST['filmReview']);

08

$stmt->execute();

09

$newId = $stmt->insert_id;

10

$stmt->close();

 

UPDATE

01

$stmt = $mysqli->prepare("UPDATE movies SET filmName = ?,

02

   filmDescription = ?,

03

   filmImage = ?, 

04

   filmPrice = ?, 

05

   filmReview = ? 

06

   WHERE filmID = ?");

07

$stmt->bind_param('sssdii',

08

   $_POST['filmName'],

09

   $_POST['filmDescription'],

10

   $_POST['filmImage'],

11

   $_POST['filmPrice'],

12

   $_POST['filmReview'],

13

   $_POST['filmID']);

14

$stmt->execute();

15

$stmt->close();

 

DELETE

1

$stmt = $mysqli->prepare("DELETE FROM movies WHERE filmID = ?");

2

$stmt->bind_param('i', $_POST['filmID']);

3

$stmt->execute();

4

$stmt->close();

 

2.面向过程

01

<?php

02

    $con = mysqli_connect("localhost","username","password","ikeepstudying");

03

 

04

    // 检测连接

05

    if (mysqli_connect_errno())

06

    {

07

        echo "连接失败: " . mysqli_connect_error();

08

    }

09

 

10

    $result = mysqli_query($con,"SELECT * FROM Persons WHERE FirstName='Peter'");

11

 

12

    while($row = mysqli_fetch_array($result))

13

    {

14

        echo $row['FirstName'] . " " . $row['LastName'];

15

        echo "<br>";

16

    }

17

?>

 

5. mysqli 读取数据库的几种方式

mysqli有四种读取数据库数据的方式:mysqli_fetch_array(), mysqli_fetch_object(),  mysqli_fetch_accoc(),  mysqli_fetch_row()。

区别是:

mysqli_fetch_array(),同时返回关系型数组和计数型数组2种,读取数据的时候我们可以用比如:$result[1] 或者是$result[“username”]。

mysqli_fetch_accoc(),返回关系型数组,读取数据的时候我们可以用比如:$result[“username”]。

mysqli_fetch_row(),返回计数型数组,读取数据的时候我们可以用比如:$result[0]。

mysqli_fetch_object(),返回一个对象,读取数据的时候我们可以用比如:$result->username的方式读取数据。

 

6. mysqli 一些函数的对比 ( 面向对象 vs 面向过程 )

1. mysqli 连接数据库:

$db = new mysqli(host,user,password,database);

这是对象导向的连接方法,还有一种过程导向的连接方法。前者返回一个对象,后者返回一个资源。

$db = mysqli_connect(host,user,password,database);

 

2.mysqli 连接出错函数:

$db->connect_error

or

mysqli_connect_error();

如果连接出错,该函数返回一个错误号,否则返回0;

 

3.选择数据库:

$db->select_db(dbname);

or

mysqli_select_db(db_resource,db_name);

 

4.mysqli 执行查询

$result = $db->query($query);

or

$result = mysqli_query($db,$query);

 

5.返回结果的行数:

$num_results = $result->num_rows;

or

$num_results = mysqli_num_rows($result);

 

6.取出每一行的结果(返回关系型数组):

$row = $result->fetch_assoc();

or

$row = mysqli_fetch_assoc($result);

 

7.取出每一行的结果(返回计数型数组):

$row = $result->fetch_row($result);

or

$row = mysqli_fetch_row($result);

 

8.取出每一行的结果(返回一个对象):

$row = $result->fetch_object();

or

$row = mysqli_fetch_object($result);

 

9.释放查询结果:

$result->free();

or

mysqli_free_result($result);

 

10.关闭数据库连接:

$db->close();

or

mysqli_close($db);

 

11.一条查询所影响的行数:

$db->affected_rows;

or

mysqli_affected_rows($result);

 

12.模式化SQL语句执行:

$query = “insert into books values(?, ?, ?, ?)”;

$stmt = $db->prepare($query);

$stmt->bind_param(“sssd”, $isbn, $author, $title, $price);

$stmt->execute();

echo $stmt->affected_rows.’ book inserted into database.’;

$stmt->close();

在过程化执行方式中,

$db->prepare()对应mysqli_stmt_prepare()函数;

$stmt->bind_param()对应mysqli_stmt_bind_param()函数;

$stmt->execute() 对应mysqli_stmt_execute()函数;


欢迎联系本站长QQ:3216572
最新回复 (0)
返回