PHP and Databases: MySQL Database Connectivity

Introduction

The capability to interact with databases is crucial in modern web development, enabling dynamic and interactive web applications. PHP, with its extensive functionalities and MySQL, a popular relational database management system, create a formidable pair in web development, providing robustness and scalability.

Establishing a Connection with MySQL

To initiate interaction with the database, a connection must be established using PHP’s mysqli or PDO extension.

<?php
$servername = "your_server_name";
$username = "your_username";
$password = "your_password";
$database = "your_database_name";

// Creating connection
$conn = new mysqli($servername, $username, $password, $database);

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

Performing CRUD Operations

Create: Inserting Data

Inserting data into MySQL involves SQL's INSERT INTO statement.

$sql = "INSERT INTO Users (username, 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: Fetching Data

Fetching data involves the SELECT statement, and fetched data can be displayed on the web page.

$sql = "SELECT id, username, email FROM Users";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}

Update: Modifying Data

To update data in MySQL, use the UPDATE statement.

$sql = "UPDATE Users SET email='john_doe@example.com' WHERE username='john_doe'";
if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

Delete: Removing Data

Deleting data is performed using the DELETE statement.

$sql = "DELETE FROM Users WHERE username='john_doe'";
if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}

Ensuring Secure Data Handling

When dealing with databases, ensuring security, especially while handling user data, is pivotal. Utilize prepared statements and parameterized queries to prevent SQL injection, and always validate and sanitize user inputs.

Closing the Database Connection

Always ensure to close the database connection after operations to optimize resource usage.

$conn->close();

Conclusion

Integrating PHP with MySQL provides developers the tools to build dynamic and interactive web applications, allowing data manipulation and retrieval to craft varied web content. Ensuring secure practices and optimizing queries ensures smooth and secure web application functionality.

Additional Resources

• PHP Manual: MySQLi
• MySQL Documentation