The use of the MYSQLI function library

Source: Internet
Author: User
Tags mysql host mysql version php mysql stmt

Overview 1. What is Mysqli

Php-mysql function Library is the PHP operation MySQL Database The most primitive extension library, php-mysqli I for improvement, equivalent to the former enhanced version, also contains the relative advanced function, in addition to increase the security itself, such as can greatly reduce SQL injection and other questions The occurrence of the problem.

2. MySQL is related to the concept of mysqli

(1) MySQL and mysqli are both functional sets of PHP, which are not associated with MySQL database.

(2) before the PHP5 version, the general is to use the PHP MySQL function to drive the MySQL database, such as mysql_query () function, belongs to the process-oriented

(3) After the PHP5 version, added mysqli function, in a sense, it is the MySQL system function enhanced version, more stable more efficient and more secure, and mysql_query () corresponding to the Mysqli_query (), belong to the object-oriented, Manipulating the MySQL database with the object's way

3. The main difference between MySQL and mysqli

(1) MySQL is a non-holding connection function, MySQL each link will open a connected process, so mysqli cost less resources.

(2) Mysqli is a permanent connection function, mysqli multiple runs mysqli will use the same connection process, thus reducing the server overhead. Mysqli encapsulates some of the advanced operations, such as transactions, while encapsulating many of the methods available in the DB operation process.

(3) Mysqli provides object-oriented programming and process-oriented programming, while MySQL can only process-oriented.

For example, the following code is MYSQLI object-oriented programming and process-oriented approach

Object-oriented approach

1

2

3

4

5

6

7

8

9

10

11

12

13

14

<?php

$mysqli = new Mysqli ("localhost", "My_user", "My_password", "World");

/* Check connection */

if (Mysqli_connect_errno ()) {

printf ("Connect failed:%s\n", Mysqli_connect_error ());

Exit ();

}

printf ("Host Information:%s\n", $mysqli->host_info);

/* Close Connection */

$mysqli->close ();

?>

Process oriented approach

1

2

3

4

5

6

7

8

9

10

11

12

13

14

<?php

$link = Mysqli_connect ("localhost", "My_user", "My_password", "World");

/* Check connection */

if (! $link) {

printf ("Connect failed:%s\n", Mysqli_connect_error ());

Exit ();

}

printf ("Host Information:%s\n", Mysqli_get_host_info ($link));

/* Close Connection */

Mysqli_close ($link);

?>

(4) Mysqli can reduce the risk of overhead and SQL injection by preprocessing statements, while MySQL does not.

To sum up, if you use PHP5, and MySQL version in more than 5.0, I hope you can use mysqli in the future as far as possible using mqsqli, not only efficient, but also more secure, and recommend that you use object-oriented programming.

Here, we also only introduce object-oriented programming methods.

The function uses 1. Connect to the database and get relevant information

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21st

<?php

$mysqli = @new mysqli ("localhost", "root", "" "," MySQL ");

If the connection is wrong

if (Mysqli_connect_errno ()) {

echo "Connection Database failed:". Mysqli_connect_error ();

$mysqli =null;

Exit

}

Get the current character set

echo $mysqli->character_set_name (). " <br> ";

Get client Information

echo $mysqli->get_client_info (). " <br> ";

Get MySQL host information

echo $mysqli->host_info. " <br> ";

Get Server information

echo $mysqli->server_info. " <br> ";

Get Server version

echo $mysqli->server_version. " <br> ";

To close a database connection

$mysqli->close ();

?>

Run results if the connection is successful

Latin1
Mysqlnd 5.0.10–20111026– $Id: e707c415db32080b3752b232487a435ee0372157 $
localhost via TCP/IP
5.6.12-log
50612

If the connection fails, the result may be

Failed to connect to database: Access denied for user ' root ' @ ' localhost ' (using Password:yes)

Failed to connect to database: Unknown ' Hello '

2. Querying data

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21st

<?php

$mysqli = @new mysqli ("localhost", "root", "" "," Design ");

If the connection is wrong

if (Mysqli_connect_errno ()) {

echo "Connection Database failed:". Mysqli_connect_error ();

$mysqli =null;

Exit

}

Constructing SQL statements

$query = "SELECT * from Designer order by ID LIMIT 3";

Execute SQL statement

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

Traverse results

while ($row = $result->fetch_array (mysqli_both)) {

echo "id". $row [' id ']. " <br> ";

}

Releasing the result set

$result->free ();

To close a database connection

$mysqli->close ();

?>

Run results

1

2

3

id10062

id10063

id10064

It's important to note here that

1

Fetch_array (Mysqli_both)

In this method, there are three parameters, namely Mysqli_both,mysqli_num,mysqli_assoc.

If the parameter is passed in Mysqli_both, the index of the returned array includes both the number and the name.

1

2

3

4

5

6

7

8

9

Array (size=26)

0 = String ' 10062 ' (length=5)

' id ' = = String ' 10062 ' (length=5)

1 = String '?? ' (length=2)

' Name ' = = String '?? ' (length=2)

2 = string ' [email protected] ' (LENGTH=17)

' Email ' + string ' [email protected] ' (LENGTH=17)

3 = String ' 18366119732 ' (length=11)

' Phone ' = String ' 18366119732 ' (length=11)

If the parameter is passed in Mysqli_num, the index of the returned array contains only numbers.

1

2

3

4

5

Array (size=13)

0 = String ' 10062 ' (length=5)

1 = String '?? ' (length=2)

2 = string ' [email protected] ' (LENGTH=17)

3 = String ' 18366119732 ' (length=11)

If the parameter is passed in Mysqli_both, the index of the returned array contains only the name.

1

2

3

4

5

Array (size=13)

' id ' = = String ' 10062 ' (length=5)

' Name ' = = String '?? ' (length=2)

' Email ' + string ' [email protected] ' (LENGTH=17)

' Phone ' = String ' 18366119732 ' (length=11)

In fact, there is an equivalent method Fetch_row (), FETCH_ASSOC ()

The relationship between them is as follows

$result->fetch_row () = mysql_fetch_row () = $result->fetch_array (mysqli_num) = Mysql_fetch_array (Mysqli_num) Returns an indexed array

$result->fetch_assoc () = MYSQL_FETCH_ASSOC () = $result->fetch_array (MYSQLI_ASSOC) = Mysql_fetch_array (MYSQLI_ ASSOC) returns the index column name

If the Fetch_array () method does not pass anything, the Mysqli_both is passed in by default

3. Inserting data

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21st

22

23

<?php

[Email protected] mysqli ("localhost", "root", "" "," Design ");

If the connection is wrong

if (Mysqli_connect_errno ()) {

echo "Connection Database failed:". Mysqli_connect_error ();

$mysqli =null;

Exit

}

Inserting data

$sql = "INSERT INTO designer (Name,phone) VALUES (' Hello ', ' 18352682923 ')";

Execute INSERT statement

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

If you perform an error

if (! $result) {

echo "SQL statement error <br>";

echo "ERROR:" $mysqli->errno. "|". $mysqli->error;

Exit

}

If the insert succeeds, the number of rows affected is returned

Echo $mysqli->affected_rows;

To close a database connection

$mysqli->close ();

?>

If the insert succeeds, the result is 1, and if it fails, an error is reported.

4. Modify the Content

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21st

22

23

<?php

[Email protected] mysqli ("localhost", "root", "" "," Design ");

If the connection is wrong

if (Mysqli_connect_errno ()) {

echo "Connection Database failed:". Mysqli_connect_error ();

$mysqli =null;

Exit

}

Inserting data

$sql = "Update designer set name = ' Hello ' where id = 10062";

Execute INSERT statement

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

If you perform an error

if (! $result) {

echo "SQL statement error <br>";

echo "ERROR:" $mysqli->errno. "|". $mysqli->error;

Exit

}

If the insert succeeds, the number of rows affected is returned

Echo $mysqli->affected_rows;

To close a database connection

$mysqli->close ();

?>

If the modification succeeds, the number of rows modified is also returned.

5. Preprocessing statements

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21st

22

23

24

25

26

27

28

29

30

<?php

$mysqli = @new mysqli ("localhost", "root", "" "," Design ");

If the connection is wrong

if (Mysqli_connect_errno ()) {

echo "Connection Database failed:". Mysqli_connect_error ();

$mysqli =null;

Exit

}

Prepare a statement to be placed on the server, insert the statement

$sql = "INSERT into designer (name, email) values (?,?)";

To generate a preprocessing statement

$stmt = $mysqli->prepare ($sql);

Give the placeholder symbol a value for each number (binding parameter) I d s B, the first argument is a formatted character, the SS represents two strings, and D represents a number

$stmt->bind_param ("ss", $name, $email);

Assigning a value to a variable

$name = "Mike";

$email = "[email protected]";

Perform

$stmt->execute ();

Assigning a value to a variable

$name = "Larry";

$email = "[email protected]";

Perform

$stmt->execute ();

Final output

echo "Last ID". $stmt->insert_id. " <br> ";

echo "affected". $stmt->affected_rows. " Line <br> ";

To close a database connection

$mysqli->close ();

?>

With the above preprocessing statements, we can also implement data insertion.

So what are the characteristics of preprocessing statements?

1. More efficient, that is, if you execute the same statement multiple times, only the statement data is different, because a statement is prepared on the server side, and then pass the different values to the server, and then let this statement execute. Equivalent to compiling once, using multiple times.

2. Security: You can prevent SQL injection (? PLACEHOLDER) This prevents the injection of abnormal variables.

Therefore, it is not only efficient but also more secure to recommend the use of MYSQLI's preprocessing statements.

Review

The above is the introduction of some methods of mysqli, more detailed content, please check the PHP manual.

Hope to help everyone!

Reprint Please specify:»php advanced features eight of the use of the MYSQLI function library

The use of the MYSQLI function library

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.