Setting Up MySQLi

MySQLi (MySQL Improved) is a PHP extension that provides an interface for interacting with MySQL databases. It offers enhanced features over the older MySQL extension, such as support for prepared statements, transactions, and object-oriented programming.

This article will guide you through setting up the MySQL server and using MySQLi in PHP, with code examples to demonstrate its usage.

Installing MySQL Server

Before you can use MySQLi, you need to have MySQL server installed on your system. Here are the steps to install MySQL server on various operating systems.

Installing MySQL Server on Windows

Download MySQL Installer:

Visit the MySQL download page and download the MySQL Installer for Windows.

Run the Installer:

Open the downloaded file and follow the installation wizard.

Choose Setup Type:

Select the setup type that suits your needs. The “Developer Default” is a good choice for most users.

Configure MySQL Server:

Set the configuration options, including server type and port number.
Create a root password and optionally set up a user account.

Complete Installation:

Follow the remaining steps to complete the installation. Once finished, MySQL server should be running on your system.

Installing MySQL Server on macOS

Download MySQL DMG Archive:

Visit the MySQL download page and download the MySQL DMG archive for macOS.

Install MySQL:

Open the DMG file and run the installer. Follow the on-screen instructions to complete the installation.

Start MySQL Server:

After installation, you can start the MySQL server using System Preferences. Open System Preferences, click on the MySQL icon, and start the server.

Installing MySQL Server on Linux

For Linux distributions like Ubuntu, you can install MySQL server using the package manager.

Update Package Index:

				
					sudo apt update

Install MySQL Server:

				
					sudo apt install mysql-server

Secure MySQL Installation:

				
					sudo mysql_secure_installation

Follow the prompts to set the root password and secure your installation.

Start MySQL Server:

				
					sudo systemctl start mysql

Using MySQLi in PHP

With MySQL server installed, you can now use MySQLi to interact with your database. Here are some examples to get you started.

Connecting to MySQL Database

To connect to a MySQL database using MySQLi, you can use either the procedural or object-oriented approach.

Procedural Style

				
					$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$connection = mysqli_connect($servername, $username, $password, $dbname);

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

echo "Connected successfully";
mysqli_close($connection);

Object-Oriented Style

				
					$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$mysqli = new mysqli($servername, $username, $password, $dbname);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

echo "Connected successfully";
$mysqli->close();

Creating a Database

You can create a new database using the following MySQLi commands.

Procedural Style

				
					$connection = mysqli_connect($servername, $username, $password);

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

$sql = "CREATE DATABASE myDatabase";
if (mysqli_query($connection, $sql)) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . mysqli_error($connection);
}

mysqli_close($connection);

Object-Oriented Style

				
					$mysqli = new mysqli($servername, $username, $password);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "CREATE DATABASE myDatabase";
if ($mysqli->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $mysqli->error;
}

$mysqli->close();

Creating a Table

Once you have a database, you can create tables within it.

Procedural Style

				
					$connection = mysqli_connect($servername, $username, $password, $dbname);

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

$sql = "CREATE TABLE Users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)";

if (mysqli_query($connection, $sql)) {
    echo "Table Users created successfully";
} else {
    echo "Error creating table: " . mysqli_error($connection);
}

mysqli_close($connection);

Object-Oriented Style

				
					$mysqli = new mysqli($servername, $username, $password, $dbname);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "CREATE TABLE Users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)";

if ($mysqli->query($sql) === TRUE) {
    echo "Table Users created successfully";
} else {
    echo "Error creating table: " . $mysqli->error;
}

$mysqli->close();

Inserting Data

You can insert data into your tables using MySQLi.

Procedural Style

				
					$connection = mysqli_connect($servername, $username, $password, $dbname);

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

$sql = "INSERT INTO Users (username, email) VALUES ('JohnDoe', 'john@example.com')";

if (mysqli_query($connection, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($connection);
}

mysqli_close($connection);

Object-Oriented Style

				
					$mysqli = new mysqli($servername, $username, $password, $dbname);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "INSERT INTO Users (username, email) VALUES ('JohnDoe', 'john@example.com')";

if ($mysqli->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $mysqli->error;
}

$mysqli->close();

Retrieving Data

You can retrieve data from your tables using MySQLi.

Procedural Style

				
					$connection = mysqli_connect($servername, $username, $password, $dbname);

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

$sql = "SELECT id, username, email FROM Users";
$result = mysqli_query($connection, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($connection);

Object-Oriented Style

				
					$mysqli = new mysqli($servername, $username, $password, $dbname);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT id, username, email FROM Users";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}

$mysqli->close();

Updating Data

You can update existing records in your tables using MySQLi.

Procedural Style

				
					$connection = mysqli_connect($servername, $username, $password, $dbname);

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

$sql = "UPDATE Users SET email='jane@example.com' WHERE username='JohnDoe'";

if (mysqli_query($connection, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($connection);
}

mysqli_close($connection);

Object-Oriented Style

				
					$mysqli = new mysqli($servername, $username, $password, $dbname);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "UPDATE Users SET email='jane@example.com' WHERE username='JohnDoe'";

if ($mysqli->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $mysqli->error;
}

$mysqli->close();

Deleting Data

You can delete records from your tables using MySQLi.

Procedural Style

				
					$connection = mysqli_connect($servername, $username, $password, $dbname);

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

$sql = "DELETE FROM Users WHERE username='JohnDoe'";

if (mysqli_query($connection, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($connection);
}

mysqli_close($connection);

Object-Oriented Style

				
					$mysqli = new mysqli($servername, $username, $password, $dbname);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "DELETE FROM Users WHERE username='JohnDoe'";

if ($mysqli->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $mysqli->error;
}

$mysqli->close();

Conclusion

Setting up MySQLi involves installing the MySQL server and configuring your PHP environment to use the MySQLi extension. This article provided a comprehensive guide on installing MySQL server on various operating systems and demonstrated how to use MySQLi to perform common database operations in both procedural and object-oriented styles.

By leveraging MySQLi’s advanced features, such as prepared statements, transactions, and support for both programming paradigms, you can build robust and secure PHP applications that efficiently interact with MySQL databases. Whether you’re creating a new application or upgrading an existing one, understanding and using MySQLi will enhance your development workflow and improve your application’s performance and security.