PHP MySQLi Extension Tutorial 🎯

Welcome to the PHP MySQLi Extension tutorial! In this lesson, we'll dive deep into working with databases using the MySQLi extension, which is a newer and improved version of the MySQL extension in PHP. 📝 Note: MySQLi supports both procedural and object-oriented programming styles.

What is MySQLi? 💡

MySQLi, an acronym for MySQL Interface, is a PHP extension that enables interaction with MySQL databases. It's designed to provide better performance, improved security, and easier handling of common tasks.

Installation ✅

Since PHP 5.5, MySQLi comes bundled with PHP, so no separate installation is needed. You can start using it right away!

Connection to the Database 💡

Let's create a connection to our database. In the following example, we'll use the object-oriented programming style.

php

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection object
$conn = new mysqli($servername, $username, $password, $dbname);

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

Basic CRUD Operations 💡

CRUD stands for Create, Read, Update, and Delete. Let's learn how to perform these operations using MySQLi.

Create (INSERT)

php

<?php
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
        VALUES ('John', 'Doe', 'john@example.com')";

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

Read (SELECT)

php

<?php
$sql = "SELECT * FROM MyGuests";
$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["firstname"]. " " . $row["lastname"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}
?>

Update (UPDATE)

php

<?php
$sql = "UPDATE MyGuests SET email='john_new@example.com' WHERE id=1";

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

Delete (DELETE)

php

<?php
$sql = "DELETE FROM MyGuests WHERE id=1";

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

Error Handling 💡

Error handling is crucial when working with databases. MySQLi provides several functions to handle errors.

php

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

if ($stmt = $conn->prepare("SELECT * FROM MyGuests")) {
    $stmt->execute();

    // Bind result variables
    $stmt->bind_result($id, $firstname, $lastname, $email);

    if ($stmt->fetch()) {
        printf("%d %s %s (%s)\n", $id, $firstname, $lastname, $email);
    } else {
        echo "No results found.";
    }

    // Close statement
    $stmt->close();
} else {
    echo "Error preparing statement: " . $conn->error;
}

$conn->close();

Prepared Statements 💡

Prepared statements help to prevent SQL injection attacks by separating the SQL code from the user input.

Transactions 💡

Transactions help to maintain data integrity by allowing multiple database operations to be executed as a single unit.

Quiz 💡

Stay tuned for more PHP tutorials! Let's get coding with CodeYourCraft. 💡 Note: For more advanced topics, check out our Object-Oriented PHP or MySQL Advanced tutorials. Happy learning! 🎯