Introduction

In the previous lecture, we learned:

• Advanced jQuery Functions
• HTML Forms
• Form Methods
• Form Elements
• jQuery Validation

Until now, our webpages have been static or interactive only inside the browser. However, if we want to store user information such as login details, registrations, articles, products, or orders, we need a database and a server-side programming language.

In this lecture, we will learn:

1. Introduction to PHP
2. Client-Side vs Server-Side Languages
3. What is a Database?
4. Database Management System (DBMS)
5. Database Terminology
6. Database Design
7. Types of Keys
8. Introduction to MySQL
9. Creating Databases and Tables
10. Basic SQL Queries
11. CRUD Operations
12. Student Assignment

---

1. Introduction to PHP

PHP stands for Hypertext Preprocessor.

PHP is a server-side scripting language used to build dynamic websites and web applications.

Unlike HTML and JavaScript, PHP code executes on the server before the page is sent to the user’s browser.

---

Why Do We Need PHP?

HTML can only display information.

PHP can:

• Store data
• Retrieve data
• Update records
• Delete records
• Connect with databases
• Process forms
• Create login systems
• Build dynamic websites

---

Client-Side vs Server-Side

Client-Side Languages

Executed inside the browser.

Examples:

• HTML
• CSS
• JavaScript
• jQuery

Flow

HTML File
      ↓
Browser
      ↓
Display Webpage

---

Server-Side Languages

Executed on the web server.

Examples:

• PHP
• ASP.NET
• JSP
• Python
• Node.js

Flow

Browser
      ↓
Server
      ↓
PHP Executes
      ↓
Database
      ↓
Response Sent Back

---

2. What is a Database?

A Database is a repository where information is stored in an organized manner.

It allows data to be:

• Stored
• Retrieved
• Updated
• Deleted

---

Examples

A database may store:

• Student Records
• Employee Information
• Products
• Orders
• Articles
• Users
• Customer Details

---

3. What is DBMS?

DBMS stands for Database Management System.

A DBMS is software used to create, manage, and maintain databases.

---

Examples of DBMS

• MySQL
• Oracle
• Microsoft SQL Server
• PostgreSQL
• SQLite

For this course, we will use MySQL.

---

4. Database Terminology

Before creating databases, we should understand some important concepts.

---

Entity

An Entity is anything in the universe under your consideration that can be identified.

Examples:

• Student
• Teacher
• Author
• Product
• Customer
• Employee

If we are building a library system, then:

Book
Student
Author
Publisher

are all entities.

---

Attributes (Columns)

Attributes describe an entity.

Example:

Entity:

Student

Attributes:

Student ID
Name
Email
Age
Gender

Each attribute becomes a column in a database table.

---

Record (Tuple)

A single row of data is called a Record or Tuple.

Example:

ID	Name	Age
1	Ali	22

This complete row is one record.

---

Table

A Table stores related records.

Example:

Students Table

ID	Name	Age
1	Ali	22
2	Ahmed	24

---

Relationship

A relationship connects one table with another.

Example:

Authors

↓

Articles

One author can write many articles.

---

Database Structure

Database

↓

Tables

↓

Records (Rows)

↓

Attributes (Columns)

↓

Values

---

5. Database Design

Before creating a database, we should design it properly.

This process is called Database Design.

Good database design:

• Reduces duplicate data
• Improves performance
• Makes maintenance easier
• Keeps relationships organized

---

Relational Database

A relational database stores information in multiple related tables.

Instead of placing everything in one table, data is divided logically.

Example:

Authors

↓

Articles

↓

Categories

All tables are connected through keys.

---

6. Database Keys

Keys uniquely identify records and create relationships between tables.

---

Primary Key

A Primary Key uniquely identifies each record.

Characteristics:

• Must be unique
• Cannot contain NULL values
• One primary key per table

Example:

AuthorID

---

Foreign Key

A Foreign Key creates a relationship between two tables.

Example:

AuthorID

↓

Articles Table

The AuthorID in the Articles table refers to the AuthorID in the Authors table.

---

Unique Key (Unique Index)

A Unique Key allows only unique values.

Example:

Email Address

No two users should have the same email address.

---

Composite Key

A Composite Key consists of two or more columns combined to uniquely identify a record.

Example:

StudentID + CourseID

Together they form one unique key.

---

7. Introduction to MySQL

MySQL is a Relational Database Management System (RDBMS).

It uses SQL (Structured Query Language) to manage data.

With MySQL, we can:

• Create databases
• Create tables
• Insert records
• Retrieve records
• Update data
• Delete data

---

8. SQL Basics

SQL stands for Structured Query Language.

SQL is the standard language used to communicate with databases.

---

Create a Database

CREATE DATABASE joinarticle;

This command creates a new database named joinarticle.

---

Select a Database

USE joinarticle;

This command tells MySQL which database will be used.

---

Create a Table

Example:

CREATE TABLE authors
(
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(150)
);

---

Alter Table

The ALTER TABLE command is used to modify the structure of an existing table.

Example:

ALTER TABLE authors
ADD phone VARCHAR(20);

Possible uses:

• Add new columns
• Modify columns
• Remove columns
• Rename columns

---

Truncate Table

The TRUNCATE TABLE command removes all records from a table but keeps the table structure.

Example:

TRUNCATE TABLE authors;

---

9. CRUD Operations

CRUD represents the four basic operations performed on database records.

Operation	SQL Command
Create	INSERT
Read	SELECT
Update	UPDATE
Delete	DELETE

These are the most important SQL queries you will use throughout your development career.

---

INSERT Query

Used to add new records.

Syntax

INSERT INTO table_name
(column1, column2)
VALUES
(value1, value2);

---

Example

INSERT INTO authors
(first_name, last_name, email)
VALUES
('Ali', 'Khan', 'ali@example.com');

---

SELECT Query

Used to retrieve data.

Select Specific Columns

SELECT first_name, last_name, email
FROM authors;

---

Select All Columns

SELECT *
FROM authors;

---

UPDATE Query

Used to modify existing records.

Syntax

UPDATE authors
SET email='new@email.com'
WHERE author_id=1;

Always use a WHERE clause when updating records. Otherwise, every record in the table may be updated.

---

DELETE Query

Used to remove records.

Syntax

DELETE FROM authors
WHERE author_id=1;

Always use a WHERE clause when deleting records. Otherwise, all records in the table may be deleted.

---

Student Assignment

Create a table to store user information.

The table should include the following fields:

• First Name
• Last Name
• Email
• Password
• Gender
• Date of Birth
• Registration Date
• Country
• Address

---

Task 1 – Insert Records

Insert at least 10 student records into your table.

Use the following format as a guide:

INSERT INTO table_name
(
    first_name,
    last_name,
    email,
    password,
    gender,
    date_of_birth,
    registration_date,
    country,
    address
)
VALUES
(
    'Ali',
    'Yasin',
    'ali@example.com',
    'password123',
    'Male',
    '2002-05-12',
    CURDATE(),
    'Pakistan',
    'Lahore'
);

---

Task 2 – Select Data

Retrieve selected columns:

SELECT first_name, last_name, email
FROM table_name;

Retrieve all records:

SELECT *
FROM table_name;

---

Task 3 – Update Data

Update the email address or country of one student using the UPDATE query.

---

Task 4 – Delete Data

Delete a single student record using the DELETE query.

---

Practice Exercises

Students should practice the following SQL commands until they become familiar with them:

• CREATE DATABASE
• USE
• CREATE TABLE
• ALTER TABLE
• TRUNCATE TABLE
• INSERT
• SELECT
• UPDATE
• DELETE

These SQL commands form the foundation of MySQL and will be used throughout the remainder of the PHP and database modules.

---

Summary

In this lecture, we learned:

• Introduction to PHP
• Client-Side vs Server-Side Languages
• Database Concepts
• DBMS and MySQL
• Database Terminology
• Relational Database Design
• Primary Key
• Foreign Key
• Unique Key
• Composite Key
• SQL Basics
• Creating Databases
• Creating Tables
• ALTER TABLE
• TRUNCATE TABLE
• CRUD Operations (INSERT, SELECT, UPDATE, DELETE)
• Database Practice Assignment

In the next lecture, we will connect PHP with MySQL, execute SQL queries from PHP, and build a simple form that stores user information in the database.