How to use mysqli in php : A Friendly Guide to Building Dynamic Websites Like a Pro

If you are starting on your journey into the world of web design and development using PHP and need to connect your website to a database with PHP, then you are likely going to get the MySQLi. It is a very powerful, very flexible, and very secure way to interact with MySQL databases in PHP, and it is easier than you would think!

The most significant advantages of MySQLi are:

In effect, it simply provides your PHP code the means to communicate with your database in a quick, efficient, and secure manner.

Security:

MySQLi supports prepared statements that removes the possibility of SQL injections, which is one of the biggest security problem's in your new skill.

Performance:

You can run multiple queries at once along with less overhead.

OOP Support:

You can use MySQLi in both a procedural and object-oriented way, so it depends on could style and opinion.

Modern Standards:

The mysql_* functions have been removed from PHP.

✅ MySQLi Procedural Style

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

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

echo "Connected successfully!";
?>

✅ MySQLi Object-Oriented Style

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

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully!";
?>

As you can see, either method will work — it just depends on what you enjoy or what the project architecture dictates. Some developers feel that the object-oriented method is newer and more scalable for larger applications.

CRUD Operations with MySQLi in PHP

1. Insert Data

$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;
}

2. Read Data

$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";
}

3. Update Data

$sql = "UPDATE users SET email='newemail@example.com' WHERE id=1";

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

4. Delete Data

$sql = "DELETE FROM users WHERE id=1";

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

PDO vs MySQLi, Real-Life Advice, and Final Thoughts

MySQLi vs PDO — Which Do You Choose?

Use MySQLi if you only have a need to access MySQL and want the utmost in performance.
Use PDO if you may need flexibility for other database systems.

Real-Life Recommendations when Using MySQLi

• Use prepared statements always.
• Prefer object-oriented style. It looks cooler, is cleaner, and is more scalable.
• Remember to close your database connection when finished:

$conn->close();

Why prepared statements?

Prepared statements provide separation of SQL logic and user data. Rather than injecting user data as raw values directly into SQL strings, you use placeholders (?) and bind the raw values later. This means that even if someone tried to inject malicious code, it would not get a chance to run as SQL.

Example:

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

$name = "Jane Doe";
$email = "jane@example.com";
$stmt->execute();

This is obviously safer than injecting raw input directly into your SQL query. You can use this same structure for SELECT, UPDATE, and DELETE queries as well.

Do not simply swallow your errors. Handle them properly so you aren't surprised when something unexpected happens.

Always, sanitize and validate all input — even with prepared statements.

Final Thoughts

In the fast-moving, fast-past industry of web application development, MySQLi is still one of the more reliable and developer-friendly solutions you will find for accessing a PHP and MySQL database. It is modern and offers secure solutions, while still having a large community for support.

It may not be the most elegant or popular tool that you have in your stack, but understanding what is running on your code at 2AM when you don't know why something doesn't work, is much more valuable than using the sexy, trendy tools.