Objective:

1. Understand the basics of databases and MySQL.
2. Learn how to create and manage databases and tables in MySQL.
3. Use fundamental SQL queries: SELECT, INSERT, UPDATE, DELETE.
4. Connect PHP to MySQL using MySQLi or PDO.
5. Complete a hands-on lab exercise to apply concepts.

Basics of Databases and MySQL

What is a Database?

• Definition: A structured collection of data stored electronically.
• Real-life examples: E-commerce sites storing product catalogs, user data in apps.

What is MySQL?

• MySQL is an open-source Relational Database Management System (RDBMS).
• Uses SQL (Structured Query Language) to manage data.
• Known for its performance, scalability, and security.

Key Database Concepts:

• Tables: Rows and columns for storing structured data.
• Rows and Columns: A row = one data entry, a column = data type (e.g., name, age).
• Primary Key: A unique identifier for each row in a table.
• Foreign Key: A reference to another table’s primary key (used for relationships).

Creating and Managing Databases and Tables

Creating a Database:

CREATE DATABASE demo_database;

The above statement creates a database named demo_database.

Using a Database:

USE demo_database;

Creating a Table:

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

Explanation:

• AUTO_INCREMENT: Automatically generates a unique ID.
• NOT NULL: Ensures a value is provided.
• UNIQUE: Prevents duplicate entries (e.g., email).

Viewing Tables:

SHOW TABLES;

Describing a Table:

SQL Queries: SELECT, INSERT, UPDATE, DELETE

SELECT: Retrieving Data

Retrieve all users:

SELECT * FROM users;

Retrieve specific columns:

SELECT name, email FROM users;

Filter data with conditions:

SELECT * FROM users WHERE age > 18;

INSERT: Adding Data

Insert a new user:

INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 25);

UPDATE: Modifying Data

Update a user’s age:

UPDATE users SET age = 26 WHERE id = 1;

DELETE: Removing Data

Delete a user:

DELETE FROM users WHERE id = 1;

Connecting PHP to MySQL

Using MySQLi

Example connection script:

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo_database";

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

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

echo "Connected successfully";

?>

Using PDO

Example connection script:

<?php

$dsn = "mysql:host=localhost;dbname=demo_database";
$username = "root";
$password = "";
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {

echo "Connection failed: " . $e->getMessage();

}

?>

Lab Exercise:

Create a database and insert user data by using responsive HTML form and PHP script.