• 周四. 12月 1st, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

Extended learning of mysqli in PHP (2) some rare attribute methods of mysqli class

[db:作者]

1月 6, 2022

Although it is a rare property method , But there may be many students who have used it in their daily development , This is just a study, maybe we use less mysqli Property or method of . It’s like expanding your knowledge system .

Switching users

The first step is to switch the users who are currently connected to the database .

// Switching users
$mysqli->change_user('root2', "123", 'blog_test');
// error message
$res = $mysqli->query( "SELECT * FROM zyblog_test_user");
var_dump($res); // bool(false)
var_dump($mysqli->error_list);
// array(1) {
// [0]=>
// array(3) {
// ["errno"]=>
// int(1142)
// ["sqlstate"]=>
// string(5) "42000"
// ["error"]=>
// string(78) "SELECT command denied to user 'root2'@'localhost' for table 'zyblog_test_user'"
// }
// }

Use change_user() Method can be easily used in mysqli After the object has been instantiated, you can switch users . In this code , our root2 User does not blog_test Tabular SELECT jurisdiction , So back query() The query result is empty . By printing mysqli Of error_list Property to see the current error message .

error message

Next to the last paragraph , Error messages for executing statements , We can go through a couple of mysqli To get . For example, in the above error_list . It contains a list of all the error messages , And it’s a very detailed error message . However, we can also get separate error number and error message string through the other two properties .

var_dump($mysqli->errno); // int(1142)
var_dump($mysqli->error); // string(78) "SELECT command denied to user 'root2'@'localhost' for table 'zyblog_test_user'"

errno Attribute is the error number of the database ,error Property is the detailed text description of the error message . Both of these error messages are executed SQL What the database returns after the statement .

Connection error message

Of course , If an error occurs when connecting to the database , We can also pass connect_errno and connect_error To get their error number and error message content .

$mysqli2 = @new mysqli("xxx", "root", "", "blog_test");
var_dump($mysqli2->connect_errno); // int(2002)
var_dump($mysqli2->connect_error); // string(90) "php_network_getaddresses: getaddrinfo failed: nodename nor servname provided, or not known"

In this code , We used the wrong host Information . If you don’t add an error suppressor when instantiating , When instantiating, it will report PHP Warning message Warning . Here we test the clarity of the output , Added error suppressor .

Statistics of client connections

var_dump($mysqli->get_connection_stats());
// array(163) {
// ["bytes_sent"]=>
// string(3) "306"
// ["bytes_received"]=>
// string(3) "287"
// ["packets_sent"]=>
// string(2) "10"
// ["packets_received"]=>
// string(1) "6"
// ["protocol_overhead_in"]=>
// string(2) "24"
// ["protocol_overhead_out"]=>
// string(2) "40"
// ["bytes_received_ok_packet"]=>
// string(1) "0"
// ["bytes_received_eof_packet"]=>
// string(1) "0"
// ……
// ……
// ["bytes_received_real_data_normal"]=>
// string(1) "0"
// ["bytes_received_real_data_ps"]=>
// string(1) "0"
// }

adopt get_connection_stats() Method , We can get some statistics about the current database connection . There’s a lot to return , There is no official document describing each field . However, we can still see some useful information from the field name , such as bytes_sent The number of bytes sent ,bytes_received The number of bytes received .

Character set

Recent years , We use PHP + MySQL Development is basically used in a unified way UTF-8 As the default character set encoding . But in the early days , Include Discuz 、 DedeCMS These early open source site building programs will provide a set of UTF-8 And a set GBK Source code for everyone to use . and mysqli You can easily and quickly get and switch the character set used by the current database .

Get database characters

// Get database characters
var_dump($mysqli->character_set_name()); // string(4) "utf8"

character_set_name() Don’t think you see one set Keyword is to set or modify something , This method is to get the current character set information .

Character set details

var_dump($mysqli->get_charset());
// object(stdClass)#2 (8) {
// ["charset"]=>
// string(4) "utf8"
// ["collation"]=>
// string(15) "utf8_general_ci"
// ["dir"]=>
// string(0) ""
// ["min_length"]=>
// int(1)
// ["max_length"]=>
// int(3)
// ["number"]=>
// int(33)
// ["state"]=>
// int(1)
// ["comment"]=>
// string(13) "UTF-8 Unicode"
// }

We can also pass get_charset() Get the detailed character set information of the current database connection . among charset It’s the character type , Here we are UTF-8 Type of , The character set is utf8_general_ci , This set is basically our standard configuration for development .

Set character set

$mysqli->set_charset('gbk');
$mysqli->query("insert into zyblog_test_user(username, password, salt) values('GBK character ','dd','d')");
var_dump($mysqli->error); // string(65) "Incorrect string value: '\xAC\xA6' for column 'username' at row 1"
$mysqli->set_charset('utf8');
$mysqli->query("insert into zyblog_test_user(username, password, salt) values('UTF character ','dd','d')");
var_dump($mysqli->error);
echo $mysqli->insert_id, PHP_EOL;

adopt set_charset() Method to set the character of the current database connection . In the first code, we set the connection character to gbk , Then execute the insert statement , The information of character mismatch will be returned directly .

Special character escape

Now that we’re talking about characters , By the way, let’s say one more thing about SQL Injection problem . Besides using Preprocessing Function SQL Beyond the injection ,MySQLi It also provides us with a real_escape_string() Method , It can be solved by hand SQL Some special symbol problems in sentences .

$username = "aaa ' bbb";
$username = $mysqli->real_escape_string($username);
var_dump($username); // string(10) "aaa \' bbb"

Using this method, we can say and addslashes() The method is similar to , But it’s better than addslashes() The content of method escape is more , It includes : NUL (ASCII 0),\n,\r,\,’,” and Control-Z These characters .

Thread operation

About MySQL The thread problem of , We are going to study deeply and brush MySQL We’ll talk about it when we get to the documentation ( Because now I’m not very clear ~~). ad locum , Let’s take a look at mysqli About China MySQL Several properties and methods of thread .

var_dump($mysqli->thread_safe); // NULL
var_dump($mysqli->thread_id); // int(600)
$thread_id = $mysqli->thread_id;
$mysqli->kill($thread_id);
if (!$mysqli->query("insert into zyblog_test_user(username, password, salt) values('kill Thread ','dd','d')")) {
var_dump($mysqli->error); // string(26) "MySQL server has gone away"
}

thread_safe Property is to save whether the current database connection is thread safe , What we returned in our test was NULL Instead of normal Boolean values , We’ll learn about this in the future .thread_id Property holds the thread of the current connection ID, Through this thread ID, We can use another kill() Method to kill the current thread . In fact, it is equivalent to closing the current mysqli Connection of objects , When we use this connection object for other operations, we will see MySQL server has gone away I’ve got a message for you .

mysqli object

Many of the above attributes can be printed directly mysqli Object to see .

var_dump($mysqli);
// object(mysqli)#1 (19) {
// ["affected_rows"]=>
// int(1)
// ["client_info"]=>
// string(79) "mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $"
// ["client_version"]=>
// int(50012)
// ["connect_errno"]=>
// int(2002)
// ["connect_error"]=>
// string(90) "php_network_getaddresses: getaddrinfo failed: nodename nor servname provided, or not known"
// ["errno"]=>
// int(0)
// ["error"]=>
// string(0) ""
// ["error_list"]=>
// array(0) {
// }
// ["field_count"]=>
// int(0)
// ["host_info"]=>
// string(25) "Localhost via UNIX socket"
// ["info"]=>
// NULL
// ["insert_id"]=>
// int(59)
// ["server_info"]=>
// string(6) "8.0.17"
// ["server_version"]=>
// int(80017)
// ["stat"]=>
// string(139) "Uptime: 355128 Threads: 4 Questions: 35696 Slow queries: 0 Opens: 764 Flush tables: 3 Open tables: 636 Queries per second avg: 0.100"
// ["sqlstate"]=>
// string(5) "00000"
// ["protocol_version"]=>
// int(10)
// ["thread_id"]=>
// int(606)
// ["warning_count"]=>
// int(0)
// }

Like host_info It shows our connection to the database host Information ,server_info Is the version information of the database connected to , Like what we use here is MySQL8 .stat Simple statistics , You can see it in there 4 Threads , No slow queries and so on . You can study these properties in this object carefully , Maybe something is what you need in the actual project .

summary

Today’s article is about some simple mysqli Properties and methods held by classes . We haven’t officially started learning about queries and things like that , But as you can see from these attribute methods , be relative to PDO Come on ,mysqli The functions provided are really more comprehensive . In the future, we will continue to study and explore in depth mysqli Various methods and uses of .

Test code :

https://github.com/zhangyue0503/dev-blog/blob/master/php/202009/source/5.PHP Medium MySQLi Extended learning ( Two )mysqli Some rare property methods of class .php

Reference documents :

https://www.php.net/manual/zh/book.mysqli.php

Official account :【 Hard core project manager 】 Get the latest article

Add wechat /QQ Good friends :【xiaoyuezigonggong/149844827】 Free PHP、 Project management learning materials

You know 、 official account 、 Tiktok 、 Headline search 【 Hard core project manager 】

B standing ID:482780532

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注