Connecting PHP with MySQL

Introduction

Connecting PHP with MySQL allows developers to create dynamic and database-driven web applications. PHP is used to process data on the server side, while MySQL stores and manages the data efficiently.

This connection is essential for websites that require user registration, login systems, product management, student records, and other data-related operations.

Objectives

By the end of this training, you will be able to:

• Understand the relationship between PHP and MySQL
• Create a database connection using PHP
• Write PHP code to connect with MySQL
• Insert, retrieve, update, and delete data
• Handle database connection errors
• Build basic database-driven applications

What is MySQL

MySQL is an open-source relational database management system used to store structured data in tables.

MySQL is commonly used with PHP because:

• It is fast and reliable
• It supports large databases
• It works well with web applications
• It is easy to learn and manage

Requirements

Before connecting PHP with MySQL, install:

• PHP
• MySQL Server
• XAMPP, WAMP, MAMP, or Laragon

These tools provide a local server environment for development.

Creating a Database

First, create a database in MySQL.

Example SQL Query:

CREATE DATABASE student_db;

Creating a Table

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Connecting PHP with MySQL

PHP provides two main methods for database connection:

• MySQLi
• PDO

MySQLi is commonly used by beginners.

MySQLi Database Connection

<?php

$servername = "localhost";
$username = "root";
$password = "";
$database = "student_db";

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

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

echo "Database connected successfully";

?>

Understanding the Connection Code

Server Name

Defines the database server location.

$servername = "localhost";

Username

Specifies the MySQL username.

$username = "root";

Password

Defines the MySQL password.

$password = "";

Database Name

Specifies the database to connect.

$database = "student_db";

mysqli_connect Function

Used to establish the database connection.

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

Inserting Data into MySQL

<?php

$conn = mysqli_connect("localhost", "root", "", "student_db");

$sql = "INSERT INTO students(name, email)
VALUES ('Ali', 'ali@example.com')";

if (mysqli_query($conn, $sql)) {
    echo "Data inserted successfully";
} else {
    echo "Error: " . mysqli_error($conn);
}

?>

Retrieving Data from MySQL

<?php

$conn = mysqli_connect("localhost", "root", "", "student_db");

$sql = "SELECT * FROM students";

$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . " - " . $row['email'];
}

?>

Updating Data in MySQL

<?php

$conn = mysqli_connect("localhost", "root", "", "student_db");

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

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
}

?>

Deleting Data from MySQL

<?php

$conn = mysqli_connect("localhost", "root", "", "student_db");

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

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
}

?>

Closing the Database Connection

<?php

mysqli_close($conn);

?>

Common Database Errors

Connection Failed

Occurs when database credentials are incorrect.

Database Not Found

Happens if the database name does not exist.

SQL Syntax Error

Caused by incorrect SQL statements.

Access Denied

Occurs when MySQL permissions are restricted.

Best Practices

• Always validate user input
• Use prepared statements for security
• Keep database credentials secure
• Close database connections after use
• Backup databases regularly

Advantages of PHP and MySQL Integration

• Easy to develop dynamic websites
• Fast database operations
• Cost-effective open-source technologies
• Strong community support
• Suitable for small and large applications

Real World Applications

PHP and MySQL are used in:

• E-commerce websites
• Student management systems
• Blogging platforms
• Content management systems
• Online registration portals
• Social networking websites

Final Presentation

In your final presentation, explain:

• What PHP and MySQL are
• Importance of database connectivity
• Steps to connect PHP with MySQL
• CRUD operations in PHP
• Benefits of database-driven applications
• Security practices for database handling