Lesson 13: Introduction to MySQL

—

by

admin
in

Databases are essential for dynamic web applications. MySQL is one of the most popular relational database management systems (RDBMS) used with PHP. In this lesson, you will learn how to set up a MySQL database and connect it to PHP using PDO and MySQLi.

13.1 Setting Up a Database

What is MySQL?

  • MySQL: An open-source relational database system for managing data.
  • Uses:
    • Storing user data.
    • Managing inventory.
    • Handling financial transactions.

Steps to Set Up a MySQL Database

Step 1: Install MySQL

  • MySQL is often bundled with tools like XAMPP, WAMP, or MAMP.
  • If not installed, download and install MySQL from the MySQL website.

Step 2: Access the MySQL Interface

  • Using phpMyAdmin:
    1. Open http://localhost/phpmyadmin.
    2. Login with:
      • Username: root
      • Password: Leave blank (default for XAMPP/WAMP).
  • Using MySQL Command-Line Interface (CLI):
    bash
    mysql -u root -p

Step 3: Create a Database

  • Using phpMyAdmin:
    1. Go to the Databases tab.
    2. Enter a name for the database (e.g., test_db).
    3. Click Create.
  • Using MySQL CLI:
    sql
    CREATE DATABASE test_db;
  • Verify Database:
    sql
    SHOW DATABASES;

Step 4: Create a Table

  • Using phpMyAdmin:
    1. Select your database.
    2. Go to the SQL tab and run the following query:
      sql
      CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(50) NOT NULL,
      email VARCHAR(100) NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
  • Using MySQL CLI:
    sql

    USE test_db;

    CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

Insert Sample Data

  • SQL Query:
    sql
    INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

13.2 Connecting PHP with MySQL

Methods of Database Connection

  1. MySQLi:
    • Procedural and object-oriented interface.
    • Supports MySQL only.
  2. PDO (PHP Data Objects):
    • Object-oriented interface.
    • Supports multiple databases (MySQL, PostgreSQL, SQLite, etc.).

Connecting Using MySQLi

Procedural MySQLi

php
<?php
// Connection parameters
$host = "localhost";
$username = "root";
$password = "";
$database = "test_db";// Connect to MySQL
$conn = mysqli_connect($host, $username, $password, $database);

// Check connection
if (!$conn) {
die(“Connection failed: “ . mysqli_connect_error());
}
echo “Connected successfully.”;
?>

Object-Oriented MySQLi

php
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "test_db";// Create connection
$conn = new mysqli($host, $username, $password, $database);

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

Connecting Using PDO

Basic Connection

php
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "test_db";try {
$conn = new PDO(“mysql:host=$host;dbname=$database“, $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo “Connected successfully.”;
} catch (PDOException $e) {
echo “Connection failed: “ . $e->getMessage();
}
?>

Querying the Database

Using MySQLi

Procedural:

php
<?php
$result = mysqli_query($conn, "SELECT * FROM users");if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo “Name: “ . $row[“name”] . “, Email: “ . $row[“email”] . “<br>”;
}
} else {
echo “No results found.”;
}
?>

Object-Oriented:

php
<?php
$result = $conn->query("SELECT * FROM users");if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo “Name: “ . $row[“name”] . “, Email: “ . $row[“email”] . “<br>”;
}
} else {
echo “No results found.”;
}
?>

Using PDO

php
<?php
$stmt = $conn->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "Name: " . $row["name"] . ", Email: " . $row["email"] . "<br>";
}
?>

Error Handling in Connections

MySQLi Procedural

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

PDO with Exception

php
try {
$conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}

Practical Example: User Management

Insert Data

Using PDO

php
<?php
$name = "Charlie";
$email = "charlie@example.com";$stmt = $conn->prepare(“INSERT INTO users (name, email) VALUES (:name, :email)”);
$stmt->bindParam(‘:name’, $name);
$stmt->bindParam(‘:email’, $email);
$stmt->execute();

echo “New record created successfully.”;
?>

Delete Data

php
<?php
$id = 1;$stmt = $conn->prepare(“DELETE FROM users WHERE id = :id”);
$stmt->bindParam(‘:id’, $id);
$stmt->execute();

echo “Record deleted successfully.”;
?>

Activities and Exercises

  1. Database Setup:
    • Create a database library with a table books (columns: id, title, author, published_year).
  2. Basic Connection:
    • Write a script to connect to the library database using both MySQLi and PDO.
  3. CRUD Operations:
    • Implement Create, Read, Update, and Delete operations for the books table using PDO.

Assignment

  1. Create a tasks table with the following structure:
    • id (INT, Auto Increment, Primary Key)
    • task_name (VARCHAR, 100)
    • status (ENUM: pending, completed)
  2. Implement the following using PHP and MySQL:
    • Insert a new task into the tasks table.
    • Display all tasks in a table format.
    • Update the status of a task.
    • Delete a task.

Summary

In this lesson, you learned:

  1. How to set up a MySQL database and create tables.
  2. How to connect PHP with MySQL using MySQLi and PDO.
  3. How to perform basic CRUD operations.

These skills form the foundation for creating dynamic web applications. Let me know if you’d like more examples or exercises!

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *