Basic operations of Mysqli-CURD learning and development summary

PHP has completely abolished the mysql extension since 5.5, and officially recommends using MySQLI or PDO instead. This article mainly talks about some basic operations of mysqli. There are two styles of writing using mysqli, object style or functional style. Below we only show the functional writing style.

Database connection

You must first connect to the database. Before connecting to data, we generally need to prepare the database account, password, port number, and database name.

Because of network reasons or other reasons, there are often cases where the database cannot be connected, so we need to handle the connection failure.

<?php
// 数据库连接
$mysqli = mysqli_connect(
   &#39;localhost&#39;,
   &#39;root&#39;,
   &#39;&#39;,
   &#39;test&#39;
);

if (mysqli_connect_errno()) {
   throw new Exception(&#39;databases connect error：&#39;
       .mysqli_connect_error());
}

// ...一些数据库操作
// 关闭数据库资源
mysqli_close($mysqli);

Set the character set

To set the character set use mysqli_set_charset();

Character set Currently we mostly use utf8mb4, Because he supports 4-byte length characters. It supports some emoji characters. Of course, if you don't need to support 4-byte characters, you can choose to use utf8, because it has more space.

mysqli_set_chartset($mysqli, 'utf8mb4');

Reselect the database

After the database is connected, you can also reselect the database.

mysqli_select_db($mysqli, '数据库名');

CURD operation

mysqli_query(): Perform an operation on the database, DDL, DQL, and DML are all supported.

mysqli_affected_rows(): Get the number of affected record rows.

New data operation

mysqli_query($mysqli,
   "INSERT INTO users(username, age, sex)
               VALUES('周杰伦', 35, '男'),
                      ('谢霆锋', 35, '男')"
);

if (mysqli_affected_rows($mysqli) <= 0) {
   throw new Exception(&#39;databases insert error:&#39;
       . mysqli_error($mysqli));
}

Modify data operation

There is a point to note here, the value returned by mysqli_affected_rows is 0. It updates the exact same information as last time, which is very common. For example, when modifying, the modify button is clicked multiple times in succession. But it does not mean that there is a problem with the program code. Therefore, unlike new addition, returning 0 should also be a success status.

mysqli_query($mysqli,
   "UPDATE users SET age=40 WHERE user_id = 1"
);

if (mysqli_affected_rows($mysqli) < 0) {
   throw new Exception(&#39;databases update error:&#39;
       . mysqli_error($mysqli));
}

Delete data operation

Note: Modification and deletion operations must add where conditions , otherwise the data of the entire table will be modified, with disastrous consequences (delete the database and run away o(╯□╰)o).

mysqli_query($mysqli,
   "Delete FROM users  WHERE user_id = 100"
);

if (mysqli_affected_rows($mysqli) <= 0) {
   throw new Exception(&#39;databases delete error:&#39;
       . mysqli_error($mysqli));
}

Query operation

When mysqli_query executes a query statement, this function will return the mysqli_result result set.

mysqli_fetch_all() gets all the data from the result set. The second parameter of this function can specify the format of the returned data. They are:

• MYSQLI_NUM: Returns the index array

• MYSQLI_ASSOC: Returns the associative array

• MYSQLI_BOTH : There are both index arrays and associative arrays

In addition, please note that after the operation on the result set is completed, remember to destroy the result set resources.

// 数据库连接
$mysqli = mysqli_connect(
   'localhost',
   'root',
   '',
   'test'
);
if (mysqli_connect_errno()) {
   throw new Exception('databases connect error：'
       .mysqli_connect_error());
}
mysqli_set_charset($mysqli, 'utf8mb4');
$result = mysqli_query($mysqli,
   "SELECT * FROM users"
);
if (mysqli_affected_rows($mysqli) < 0) {
   throw new Exception(&#39;databases select error:&#39;
       . mysqli_error($mysqli));
}
$users = mysqli_fetch_all($result, MYSQLI_ASSOC);
// 销毁结果集资源
mysqli_free_result($result);
// 关闭数据库资源
mysqli_close($mysqli);

The above is the basic operation of Mysqli, a summary of personal actual learning and development, welcome to discuss!

The above is the detailed content of Basic operations of Mysqli-CURD learning and development summary. For more information, please follow other related articles on the PHP Chinese website!