To create and interact with a MySQL database using MySQLi in PHP, you need to follow these steps:

1. Connect to the MySQL server.

2. Create a new database (if needed).

3. Select the database to use.

4. Perform queries (e.g., SELECT, INSERT, UPDATE, DELETE) on the database.

Here’s an example showing how to create a database and perform basic operations with MySQLi in PHP.

1. Create a MySQL Database Using MySQLi (Procedural Approach)

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";// Create connection$conn = mysqli_connect($host, $username, $password);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// SQL query to create a new database$sql = "CREATE DATABASE IF NOT EXISTS testDB";// Execute the queryif (mysqli_query($conn, $sql)) {    echo "Database created successfully or already exists.";} else {    echo "Error creating database: " . mysqli_error($conn);}// Close the connectionmysqli_close($conn);?>

2. Create a MySQL Database Using MySQLi (Object-Oriented Approach)

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";// Create connection$conn = new mysqli($host, $username, $password);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// SQL query to create a new database$sql = "CREATE DATABASE IF NOT EXISTS testDB";// Execute the queryif ($conn->query($sql) === TRUE) {    echo "Database created successfully or already exists.";} else {    echo "Error creating database: " . $conn->error;}// Close the connection$conn->close();?>

3. Select a Database and Use It

After creating the database, you need to select it before performing operations like creating tables or inserting data. The mysqli_select_db() function is used in the procedural approach, while in the object-oriented approach, it’s done automatically when you pass the database name while creating the connection.

Procedural Approach:

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database to be used// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// Your queries can now be executed on the 'testDB' databaseecho "Connected to the database '$dbname' successfully.";// Close the connectionmysqli_close($conn);?>

Object-Oriented Approach:

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database to be used// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// Your queries can now be executed on the 'testDB' databaseecho "Connected to the database '$dbname' successfully.";// Close the connection$conn->close();?>

4. Perform Queries on the Database

Now that you have selected the database, you can perform SQL queries like SELECT, INSERT, UPDATE, and DELETE.

Creating a Table

Procedural Approach:

<?php$conn = mysqli_connect("localhost", "root", "", "testDB");$sql = "CREATE TABLE IF NOT EXISTS users (    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100) NOT NULL,    email VARCHAR(100) NOT NULL,    reg_date TIMESTAMP)";if (mysqli_query($conn, $sql)) {    echo "Table 'users' created successfully.";} else {    echo "Error creating table: " . mysqli_error($conn);}mysqli_close($conn);?>

Object-Oriented Approach:

<?php$conn = new mysqli("localhost", "root", "", "testDB");$sql = "CREATE TABLE IF NOT EXISTS users (    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100) NOT NULL,    email VARCHAR(100) NOT NULL,    reg_date TIMESTAMP)";if ($conn->query($sql) === TRUE) {    echo "Table 'users' created successfully.";} else {    echo "Error creating table: " . $conn->error;}$conn->close();?>

Inserting Data into a Table

Procedural Approach:

<?php$conn = mysqli_connect("localhost", "root", "", "testDB");$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", "", "testDB");$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: " . $conn->error;}$conn->close();?>

Selecting Data from a Table

Procedural Approach:

<?php$conn = mysqli_connect("localhost", "root", "", "testDB");$sql = "SELECT id, name, email FROM users";$result = mysqli_query($conn, $sql);if (mysqli_num_rows($result) > 0) {    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", "", "testDB");$sql = "SELECT id, name, email FROM users";$result = $conn->query($sql);if ($result->num_rows > 0) {    while ($row = $result->fetch_assoc()) {        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";    }} else {    echo "0 results";}$conn->close();?>

Updating Data in a Table

Procedural Approach:

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

Object-Oriented Approach:

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

Deleting Data from a Table

Procedural Approach:

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

Object-Oriented Approach:

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

Summary of MySQLi Functions for Database Operations

Let me know if you need further details!