MySQLi (MySQL Improved) is an extension in PHP that allows you to interact with MySQL databases. It provides a more modern and secure way to interact with MySQL than the older mysql_* functions (which are now deprecated). MySQLi offers both procedural and object-oriented approaches for accessing and interacting with MySQL databases.

Key Features of MySQLi:

• Supports both Procedural and Object-Oriented programming.

• Offers prepared statements for better security (prevents SQL injection).

• Supports transactions and multiple statements.

• Provides functions to fetch data in multiple formats (e.g., associative arrays, numeric arrays, etc.).

How to Use MySQLi in PHP

Here are the basic steps for working with MySQLi in PHP:

1. Connecting to MySQL Database

You need to connect to a MySQL database before performing any operations like querying or inserting data.

Procedural Approach:

<?php// Database connection parameters$host = "localhost";  // Host name$username = "root";   // Database username$password = "";       // Database password$dbname = "test";     // Database name// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}echo "Connected successfully";// Close the connectionmysqli_close($conn);?>

Object-Oriented Approach:

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "test";// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}echo "Connected successfully";// Close the connection$conn->close();?>

2. Querying the Database

Once connected, you can execute queries to interact with the database. For example, you can retrieve data, insert new records, or update existing ones.

Select Query (Fetching Data)

Procedural Approach:

<?php$conn = mysqli_connect("localhost", "root", "", "test");$sql = "SELECT id, name, email FROM users";$result = mysqli_query($conn, $sql);if (mysqli_num_rows($result) > 0) {    // Output data of each row    while ($row = mysqli_fetch_assoc($result)) {        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";    }} else {    echo "0 results";}mysqli_close($conn);?>

Object-Oriented Approach:

<?php$conn = new mysqli("localhost", "root", "", "test");$sql = "SELECT id, name, email FROM users";$result = $conn->query($sql);if ($result->num_rows > 0) {    // Output data of each row    while ($row = $result->fetch_assoc()) {        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";    }} else {    echo "0 results";}$conn->close();?>

3. Inserting Data into the Database

Procedural Approach:

<?php$conn = mysqli_connect("localhost", "root", "", "test");$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";if (mysqli_query($conn, $sql)) {    echo "New record created successfully";} else {    echo "Error: " . $sql . "<br>" . mysqli_error($conn);}mysqli_close($conn);?>

Object-Oriented Approach:

<?php$conn = new mysqli("localhost", "root", "", "test");$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";if ($conn->query($sql) === TRUE) {    echo "New record created successfully";} else {    echo "Error: " . $sql . "<br>" . $conn->error;}$conn->close();?>

4. Prepared Statements (For Security)

Prepared statements protect against SQL injection by separating the query structure from the data.

Procedural Approach (Prepared Statement):

<?php$conn = mysqli_connect("localhost", "root", "", "test");$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email) VALUES (?, ?)");mysqli_stmt_bind_param($stmt, "ss", $name, $email);// Set parameters and execute$name = "Jane Doe";$email = "jane@example.com";mysqli_stmt_execute($stmt);echo "New record created successfully";mysqli_stmt_close($stmt);mysqli_close($conn);?>

Object-Oriented Approach (Prepared Statement):

<?php$conn = new mysqli("localhost", "root", "", "test");$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");$stmt->bind_param("ss", $name, $email);// Set parameters and execute$name = "Jane Doe";$email = "jane@example.com";$stmt->execute();echo "New record created successfully";$stmt->close();$conn->close();?>

5. Updating Data in the Database

Procedural Approach:

<?php$conn = mysqli_connect("localhost", "root", "", "test");$sql = "UPDATE users SET email = 'newemail@example.com' WHERE id = 1";if (mysqli_query($conn, $sql)) {    echo "Record updated successfully";} else {    echo "Error: " . $sql . "<br>" . mysqli_error($conn);}mysqli_close($conn);?>

Object-Oriented Approach:

<?php$conn = new mysqli("localhost", "root", "", "test");$sql = "UPDATE users SET email = 'newemail@example.com' WHERE id = 1";if ($conn->query($sql) === TRUE) {    echo "Record updated successfully";} else {    echo "Error: " . $sql . "<br>" . $conn->error;}$conn->close();?>

6. Deleting Data from the Database

Procedural Approach:

<?php$conn = mysqli_connect("localhost", "root", "", "test");$sql = "DELETE FROM users WHERE id = 1";if (mysqli_query($conn, $sql)) {    echo "Record deleted successfully";} else {    echo "Error: " . $sql . "<br>" . mysqli_error($conn);}mysqli_close($conn);?>

Object-Oriented Approach:

<?php$conn = new mysqli("localhost", "root", "", "test");$sql = "DELETE FROM users WHERE id = 1";if ($conn->query($sql) === TRUE) {    echo "Record deleted successfully";} else {    echo "Error: " . $sql . "<br>" . $conn->error;}$conn->close();?>

7. Error Handling

In MySQLi, you can handle errors gracefully by checking the connection or query result using mysqli_error() (procedural) or $conn->error (object-oriented).

For example:

<?php$conn = new mysqli("localhost", "root", "", "test");if ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}$sql = "SELECT id, name FROM users";$result = $conn->query($sql);if ($result === FALSE) {    die("Error in query: " . $conn->error);}$conn->close();?>

Summary of MySQLi Functions

When to Use MySQLi:

• If you're specifically working with MySQL databases and need a more modern approach than the deprecated mysql_* functions.

• If you want to use prepared statements for security, particularly against SQL injection attacks.

• If you want more control over the query results (e.g., fetching data in different formats, handling transactions, etc.).

Let me know if you need any further clarification or have any questions!