Using PHP mysqli Extension: Connecting, Error Handling, CRUD Operations, and Transactions

The PHP mysqli extension offers all the features of its predecessor and adds support for newer MySQL capabilities, making it suitable for modern backend development.

1. Establishing and Closing Connections

You can create a connection either procedurally or with object‑oriented syntax.

$_mysqli = new mysqli();
$_mysqli->connect('localhost','root','yangfan','guest');
$_mysqli = new mysqli('localhost','root','yangfan','guest');
$_mysqli->select_db('testguest');
$_mysqli->close();

2. Handling Connection Errors

Always check for connection failures using mysqli_connect_errno() and mysqli_connect_error(), or the object’s errno and error properties.

if (mysqli_connect_errno()) {
    echo '数据库连接错误，错误信息： '.mysqli_connect_error();
    exit();
}
if ($_mysqli->errno) {
    echo '数据库操作时发生错误，错误代码是： '.$_mysqli->error;
}

3. CRUD Operations

Typical queries involve creating, retrieving, updating, and deleting records (CRUD).

3.1 Retrieving Data

$_mysqli->set_charset("utf8");
$_sql = "SELECT * FROM tg_user";
$_result = $_mysqli->query($_sql);
print_r($_result->fetch_row());
$_result->free();

3.2 Parsing Query Results

// Object style
$_row = $_result->fetch_object();
echo $_row->tg_username;
while (!!$_row = $_result->fetch_object()) {
    echo $_row->tg_username.'<br/>';
}
// Array styles
$_row = $_result->fetch_array();
echo $_row[3];
$_row = $_result->fetch_row();
echo $_row[3];
$_row = $_result->fetch_assoc();
echo $_row['tg_username'];

3.3 Row and Affected Row Counts

echo $_result->num_rows; // SELECT row count
echo $_mysqli->affected_rows; // INSERT/UPDATE/DELETE affected rows

3.4 Moving Pointers and Accessing Field Information

echo $_result->field_count;
$_field = $_result->fetch_field();
echo $_field->name;
while (!!$_field = $_result->fetch_field()) {
    echo $_field->name.'<br/>';
}
print_r($_result->fetch_fields());
$_result->data_seek(5);
$_result->field_seek(2);

4. Executing Multiple SQL Statements

$_sql .= "SELECT * FROM tg_user;";
$_sql .= "SELECT * FROM tg_photo;";
$_sql .= "SELECT * FROM tg_article";
if ($_mysqli->multi_query($_sql)) {
    $_result = $_mysqli->store_result();
    print_r($_result->fetch_array());
    $_mysqli->next_result();
    $_result = $_mysqli->store_result();
    print_r($_result->fetch_array());
    $_mysqli->next_result();
    $_result = $_mysqli->store_result();
    print_r($_result->fetch_array());
} else {
    echo 'sql语句有误！';
}

5. Managing Transactions

$_mysqli->autocommit(false);
$_sql .= "UPDATE tg_friend SET tg_state = tg_state + 5 WHERE tg_id = 1;";
$_sql .= "UPDATE tg_flower SET tg_flower = tg_flower - 5 WHERE tg_id = 1;";
if ($_mysqli->multi_query($_sql)) {
    $_success = $_mysqli->affected_rows == 1 ? true : false;
    $_mysqli->next_result();
    $_success2 = $_mysqli->affected_rows == 1 ? true : false;
    if ($_success && $_success2) {
        $_mysqli->commit();
        echo '完美提交！';
    } else {
        $_mysqli->rollback();
        echo '程序出现异常！' . $_mysqli->errno . $_mysqli->error;
    }
}
$_mysqli->autocommit(true);

Although newer projects often use PDO or MySQLi’s object‑oriented API, many legacy applications still rely on the procedural style shown here.

Feel free to like and share if you found this tutorial helpful.