PHP operation MySQL

This article mainly introduces PHP to operate MySQL, which has certain reference value. Now I share it with everyone. Friends in need can refer to

related mysql video tutorials Recommended: "mysql tutorial"

1. Use PHP to connect to the MySQL database

Use the PHP function mysqli_connect to connect to the mysql database
Syntax: $conn = mysqli_connect('host name', 'user name', 'password', 'database name', port); Returns the resource object if the connection is successful, false if failed
$conn It is called [database connection identification]

header("Content-type:text/html;charset=utf-8");
$conn = @mysqli_connect('localhost','root','root','student') or die("数据库连接错误！");
echo "<pre class="brush:php;toolbar:false">";
var_dump($conn);echo "

";

2. Use PHP to access the MySQL database

Use the PHP function mysqli_query to send SQL instructions to the MySQL database to query, and encapsulate the results returned by the MySQL database into objects as The return value of the function.
Grammar: $result = mysqli_query (database connection identifier, SQL command);
$result is called [result set]

3. Results Set processing

There are two result sets, which are mainly different from the currently executed SQL command and whether there is data returned.

1. There is return data: select, show, desc
   mysqli_query will return a resource type. If the execution is successful, the result set will be returned. If it fails, false will be returned.
   Note: Since the resource type is always true, it cannot be used to determine whether there is data. It can only be used to determine whether the command is successfully executed.

2. No data returned: insert, delete, update, set, DDL (data definition language, such as create, alter, drop)
   mysqli_query will return a Boolean value. Returns true if successful, false if failed.

Use mysqli_fetch_assoc to read data from the result set, and return the read data in the form of an associative array. The key name of the associative array is the field name. , and at the same time, the pointer of the result set moves down one row.
Syntax: $row = mysqli_fetch_assoc(result set);

header("Content-type:text/html;charset=utf-8");
$conn = @mysqli_connect('localhost','root','root','student') or die("数据库连接错误！");
$rs = mysqli_query($conn, 'set names utf8');//设置PHP与MySQL交互默认字符集var_dump($rs);
$rs = mysqli_query($conn, 'select * from student');
while ($row = mysqli_fetch_assoc($rs)) {    
echo "<pre class="brush:php;toolbar:false">";
    var_dump($row);    
    echo "

"; }

Three functions to get data from the result set:

1. mysqli_fetch_assoc () Get a row of data and return it as an [association] array

2. mysqli_fetch_row() Get a row of data and return it as an [index] array

3. mysqli_fetch_array() Get a row of data as the [association] array and the [index] array and return

The number of rows of data in the statistical result set
Syntax: mysqli_num_rows(result Set);
Usually used to determine whether there is data in the result set. If it is greater than 0, it proves that there is data. If it is equal to 0, it means that there is no data.

echo mysqli_num_rows($conn);

Release the result set, release the memory space occupied by the result set
Syntax: mysqli_free_result(result set);

mysqli_free_result($rs);

four , Close the database connection

Syntax: mysqli_close(database connection identification);
If you do not use mysqli_close to close the database connection, PHP will automatically close it by default after the code execution is completed.

mysqli_close($conn);

5. Other usages

Error message
Syntax: mysqli_error (database connection identification);
Usually used for Debugging and viewing errors

echo mysqli_error($conn);

Get the ID of the data just inserted
Syntax: mysqli_insert_id (database connection identification);

6. Project character encoding

MySQL character set
MySQL data is ultimately saved in databases, tables, and records.
When the data is actually saved, it is affected by several places:

• The encoding of the field

• The encoding of the table

• Encoding of the library

• Built-in encoding of MySQL server

If encoding is set on the field, save the data to The field encoding shall prevail.
If no encoding is set for the field, the encoding of the table shall prevail.
If the table does not have an encoding set, the encoding set on the library shall prevail.
If the library does not set the encoding, the built-in encoding of the MySQL server shall prevail.

The above encodings are determined by the server. You only need to set the encodings of databases, tables, and fields.

The encoding of data displayed on the client
Four places affect the display of the client

1. The encoding of data stored on the server

The following three are related to the client

1. Data encoding sent by the client to the server
   Server variables (configuration items):character_set_client

2. Encoding used by the connection layer between client and server
   Server variable (configuration item):character_set_connection

3. Data encoding of processing results sent from the server to the client
   Server variables (configuration items): character_set_results

可以使用 show variables like 'char%'; 在MySQL命令行模式下查看相关变量。
可使用 set指令单独设置，如：set character_set_results=utf8;

set names 是一个快捷操作，同时设置了以上三个服务器变量（配置项），当使用set names utf8;时，相当于

set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_results=utf8;

项目中确保字符编码五个地方统一

1. MySQL数据库服务器保存数据的编码

2. 设置php与MySQL交互层编码 mysqli_query($conn, 'set names utf8');

3. PHP与浏览器之间交互的编码 header("Content-type:text/html;charset=utf-8");

4. HTML meta 标签声明使用编码 <meta charset="UTF-8">

5. PHP文件保存的编码（在编辑器中设置）

更多mysql相关知识请关注php中文网mysql视频教程频道

相关推荐：

PHP操作JSON方法大全

PHP操作MongoDB实现增删改查功能

【mysql视频教程】2019年最火的5个mysql视频教程推荐

The above is the detailed content of PHP operation MySQL. For more information, please follow other related articles on the PHP Chinese website!