The MySQLi extension in PHP provides a set of functions for interacting with a MySQL database using the procedural style. Here’s an overview of the most commonly used MySQLi functions:

Connection Functions

1. mysqli_connect()

   • Establishes a connection to a MySQL database.
   • Syntax: mysqli_connect(host, username, password, database, port, socket)

2. mysqli_close()

   • Closes an open MySQL connection.
   • Syntax: mysqli_close(connection)

3. mysqli_ping()

   • Pings a server connection, or attempts to reconnect if the connection is lost.
   • Syntax: mysqli_ping(connection)

Query Functions

4. mysqli_query()

   • Executes a query on the database.
   • Syntax: mysqli_query(connection, query, resultmode)

5. mysqli_prepare()

   • Prepares an SQL statement for execution.
   • Syntax: mysqli_prepare(connection, query)

6. mysqli_stmt_execute()

   • Executes a prepared statement.
   • Syntax: mysqli_stmt_execute(statement)

7. mysqli_stmt_bind_param()

   • Binds variables to a prepared statement as parameters.
   • Syntax: mysqli_stmt_bind_param(statement, types, var1, var2, ...)

8. mysqli_stmt_bind_result()

   • Binds variables to a prepared statement for result storage.
   • Syntax: mysqli_stmt_bind_result(statement, var1, var2, ...)

9. mysqli_stmt_get_result()

   • Returns a result set from a prepared statement.
   • Syntax: mysqli_stmt_get_result(statement)

10. mysqli_stmt_fetch()

    • Fetches the next row of a result set from a prepared statement.
    • Syntax: mysqli_stmt_fetch(statement)

11. mysqli_stmt_close()

    • Closes a prepared statement.
    • Syntax: mysqli_stmt_close(statement)

Result Set Functions

12. mysqli_fetch_assoc()

    • Fetches a result row as an associative array.
    • Syntax: mysqli_fetch_assoc(result)

13. mysqli_fetch_row()

    • Fetches a result row as a numeric array.
    • Syntax: mysqli_fetch_row(result)

14. mysqli_fetch_array()

    • Fetches a result row as an associative, numeric, or both array.
    • Syntax: mysqli_fetch_array(result, resulttype)

15. mysqli_fetch_object()

    • Fetches a result row as an object.
    • Syntax: mysqli_fetch_object(result, class_name, params)

16. mysqli_num_rows()

    • Returns the number of rows in a result set.
    • Syntax: mysqli_num_rows(result)

17. mysqli_affected_rows()

    • Returns the number of affected rows in the last INSERT, UPDATE, REPLACE, or DELETE query.
    • Syntax: mysqli_affected_rows(connection)

18. mysqli_free_result()

    • Frees the memory associated with a result.
    • Syntax: mysqli_free_result(result)

Error Handling Functions

19. mysqli_error()

    • Returns a string description of the last error.
    • Syntax: mysqli_error(connection)

20. mysqli_errno()

    • Returns the error code for the last error.
    • Syntax: mysqli_errno(connection)

Utility Functions

21. mysqli_real_escape_string()

    • Escapes special characters in a string for use in an SQL statement.
    • Syntax: mysqli_real_escape_string(connection, string)

22. mysqli_fetch_lengths()

    • Returns the lengths of the columns in the result set.
    • Syntax: mysqli_fetch_lengths(result)

23. mysqli_fetch_all()

    • Fetches all result rows as an array.
    • Syntax: mysqli_fetch_all(result, resulttype)

Transaction Functions

24. mysqli_autocommit()

    • Turns automatic commit on or off.
    • Syntax: mysqli_autocommit(connection, mode)

25. mysqli_commit()

    • Commits the current transaction.
    • Syntax: mysqli_commit(connection)

26. mysqli_rollback()

    • Rolls back the current transaction.
    • Syntax: mysqli_rollback(connection)

Example Usage

Here’s a brief example that demonstrates some of these functions:

<?php
// Connect to the database
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Execute a SELECT query
$sql = "SELECT username, email FROM users";
$result = mysqli_query($conn, $sql);

if ($result) {
    // Get number of rows
    $num_rows = mysqli_num_rows($result);
    echo "Number of rows: " . $num_rows . "<br>";

    // Fetch and display rows
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Username: " . $row['username'] . " - Email: " . $row['email'] . "<br>";
    }

    // Free the result set
    mysqli_free_result($result);
} else {
    echo "Error: " . mysqli_error($conn);
}

// Close the connection
mysqli_close($conn);
?>