UNIT V

PHP and MySQL

Web Application Development — End Term Study Notes

• MySQL Client & phpMyAdmin

• MySQL Commands (Database & Table Operations)

• PHP MySQL Functions (MySQLi & PDO)

• Connecting PHP to MySQL

• Executing Queries (SELECT, INSERT, UPDATE, DELETE)

• Retrieving Query Results & Counting Records

• CRUD Operations

• Exception Handling in PHP

• Django ORM & Database Concepts

1. Accessing MySQL — MySQL Client & phpMyAdmin

1.1 What is a MySQL Client?

A MySQL client is any tool that connects to the MySQL server so you can send commands and manage
data. The most basic form is the MySQL Command-Line Interface (CLI), where you type SQL commands
directly.

1.2 Uses of the MySQL Client

• Executing SQL Queries: Run SELECT, INSERT, UPDATE, DELETE and other SQL directly against a
database.
• Database Administration: Create/drop databases, create/alter tables, manage users and permissions.
• Import & Export Data: Bring data in from CSV or other sources; export data to files.
• Backup & Restoration: Dump a database to a file for backup; restore it later.
• Monitoring & Troubleshooting: View server status, check query execution times, find slow queries.

1.3 How to Connect Using MySQL CLI

After installing MySQL, open your terminal or command prompt and use these commands:

Purpose Command

Connect (any host) mysql -u username -p

Connect to specific database mysql -u username -p database_name

Connect to remote server mysql -u username -p -h hostname

Set new root password mysqladmin -u root password your_password

Clear console (Linux) mysql> system clear;

Exit the client exit OR quit

After connecting you will see the mysql> prompt. From here you can run any SQL command:
mysql> SHOW DATABASES;
mysql> USE your_database_name;
mysql> SHOW TABLES;
mysql> SELECT * FROM your_table_name;

1.4 Using phpMyAdmin

phpMyAdmin is a free, web-based tool written in PHP that lets you manage MySQL databases through a
browser. It is much easier to use than the command line because it has a graphical interface with buttons,
menus, and forms.

Key Features of phpMyAdmin

• Intuitive Web Interface — Browse databases, create tables, manage users, and run SQL through a
visual interface.
 • MySQL Administration — Create databases, run queries, add user accounts without typing commands.
• Multi-Language Support — Available in 72 languages; supports left-to-right and right-to-left scripts.
• Export and Import Data — Export to CSV, SQL, XML, PDF and more; import from CSV and SQL files.
• Server Maintenance — Manage server settings, databases, and tables easily.

1.5 MySQL Client vs phpMyAdmin — Quick Comparison

Feature MySQL Client (CLI) phpMyAdmin

Interface Command-line only Graphical (web browser)

Ease of Use Requires knowledge of SQL commands Beginner-friendly, visual menus

Copy-Paste Queries Not supported in console Yes — paste queries into SQL box

Security Built-in user auth & access control Depends on web server settings

Language Support Works with any programming language Designed specifically for MySQL

Remote Access Via terminal with client apps Via any web browser (any location)

Advanced Features Full MySQL feature set Most features but may miss some advanced ones
2. MySQL Commands — Full Reference

MySQL commands let you work with databases, tables, and data. They are written in SQL (Structured Query
Language). Here is a complete list of important commands you need to know.

2.1 Database Operations

a) CREATE DATABASE — Create a new database

CREATE DATABASE database_name;

-- Example:
CREATE DATABASE my_database;

This creates a brand new, empty database on the MySQL server.

b) DROP DATABASE — Delete a database

DROP DATABASE database_name;

-- Example:
DROP DATABASE my_database;

This permanently deletes the database and ALL tables inside it. Be very careful with this command.

c) USE DATABASE — Select/switch to a database

USE database_name;

-- Example:
USE my_database;

After this command, all your queries will run on the selected database.

d) SHOW DATABASES — List all databases

SHOW DATABASES;

Shows a list of all databases available on the MySQL server.

2.2 Table Operations

a) CREATE TABLE — Create a new table

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);

-- Example:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
department VARCHAR(50)
);
This creates a table with named columns. AUTO_INCREMENT means the id value increases automatically
for each new row. PRIMARY KEY means this column uniquely identifies each row.

b) DROP TABLE — Delete a table

DROP TABLE table_name;

-- Example:
DROP TABLE employees;

Permanently deletes the table and all its data from the database.

c) ALTER TABLE — Modify an existing table

-- Add a new column:
ALTER TABLE table_name ADD COLUMN column_name datatype;

-- Example:
ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

-- Drop a column:
ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE lets you change the structure of an existing table — add columns, remove columns, or
change column types.

d) SHOW TABLES — List all tables in current database

SHOW TABLES;

2.3 Data Manipulation (CRUD)

a) INSERT — Add new rows

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

-- Example:
INSERT INTO employees (name, age, department, salary)
VALUES ('Janavi', 30, 'HR', 50000.00);

b) SELECT — Read/retrieve data

SELECT column1, column2 FROM table_name WHERE condition;

-- Get all columns:

SELECT * FROM employees;

-- With condition:
SELECT * FROM employees WHERE department = 'HR';

The WHERE clause filters which rows are returned. Without WHERE, all rows are returned.

c) UPDATE — Modify existing rows

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

-- Example:
UPDATE employees SET salary = 55000.00 WHERE name = 'John';
 Note: Always include WHERE in UPDATE. Without it, ALL rows in the table will be updated!

d) DELETE — Remove rows

DELETE FROM table_name WHERE condition;

-- Example:
DELETE FROM employees WHERE age > 60;

Note: Always include WHERE in DELETE. Without it, ALL rows will be deleted!

e) TRUNCATE — Remove all rows but keep the table

TRUNCATE TABLE table_name;

-- Example:
TRUNCATE TABLE employees;

Unlike DELETE (which removes row by row), TRUNCATE is faster and removes everything at once, but
cannot use a WHERE clause.

2.4 User & Permission Management

a) CREATE USER
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- Example:
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

b) GRANT PRIVILEGES — Give permissions to a user

GRANT privileges ON database_name.table_name TO 'username'@'localhost';

-- Example (give select, insert, update on all tables):

GRANT SELECT, INSERT, UPDATE ON my_database.* TO 'myuser'@'localhost';

c) REVOKE PRIVILEGES — Remove permissions

REVOKE INSERT ON my_database.* FROM 'myuser'@'localhost';

d) DROP USER — Delete a user

DROP USER 'myuser'@'localhost';

2.5 Miscellaneous Commands

Command What it does

SELECT VERSION(); Shows MySQL server version

EXIT; or QUIT; Exits the MySQL command-line client

SHOW DATABASES; Lists all databases

SHOW TABLES; Lists all tables in current DB

DESCRIBE table_name; Shows structure (columns) of a table

3. PHP MySQL Functions — MySQLi and PDO

PHP can talk to MySQL using two methods: MySQLi (MySQL Improved) and PDO (PHP Data Objects). Both
work well. The main difference is that PDO works with 12 different databases while MySQLi only works with
MySQL.

3.1 MySQLi — Procedural Style

MySQLi (the 'i' stands for improved) gives you two ways to write code: procedural (function-based) and
object-oriented. Here we cover the procedural approach first.

1. Establishing a Connection
<?php
$conn = mysqli_connect('localhost', 'username', 'password', 'database');
if (!$conn) {
die('Connection failed: ' . mysqli_connect_error());
}
echo 'Connected successfully';
?>

mysqli_connect() takes: server name, username, password, and database name. If it fails,
mysqli_connect_error() tells you what went wrong.

2. Executing a Query
<?php
$result = mysqli_query($conn, 'SELECT * FROM users');
?>

mysqli_query() takes the connection object and the SQL string. It returns the result set for SELECT queries,
or TRUE/FALSE for other queries.

3. Fetching Data from Results

<?php
while ($row = mysqli_fetch_assoc($result)) {
echo 'ID: ' . $row['id'] . ', Name: ' . $row['name'] . '<br>';
}
?>

mysqli_fetch_assoc() returns one row at a time as an associative array where the key is the column name.
The while loop keeps going until all rows are fetched.

4. Handling Errors
<?php
if (mysqli_error($conn)) {
die('Query failed: ' . mysqli_error($conn));
}
?>

mysqli_error() returns the error message from the last MySQL operation.

5. Closing the Connection

<?php
mysqli_close($conn);
 ?>

MySQLi Fetch Functions — Quick Reference

Function What it returns

mysqli_fetch_assoc() One row as associative array (key = column name)

mysqli_fetch_array() One row as both associative and numeric array

mysqli_fetch_row() One row as a numbered/enumerated array

mysqli_fetch_object() One row as an object (use $row->column_name)

mysqli_num_rows() Total number of rows in the result set

3.2 MySQLi — Object-Oriented Style

In object-oriented style, you use the new mysqli() class. This is the modern, preferred way.
<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'library';

// Create connection object

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

// Check connection
if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}
echo 'LIBRARY DATABASE CONNECTED SUCCESSFULLY';
?>

3.3 PDO — PHP Data Objects

PDO is a flexible way to connect PHP to databases. It works with MySQL, PostgreSQL, SQLite, and 9 other
databases. If you ever need to switch databases, PDO makes it easy — you only change the connection
string, not all the code.

1. Establishing a Connection with PDO

<?php
try {
$pdo = new PDO('mysql:host=localhost;dbname=library', 'root', '');
// Set error mode to throw exceptions
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo 'Connected successfully';
} catch (PDOException $e) {
die('Connection failed: ' . $e->getMessage());
}
?>
PDO uses a DSN (Data Source Name) string: mysql:host=hostname;dbname=database. Errors are
caught using try-catch blocks.

2. Executing Queries with PDO

<?php
$stmt = $pdo->query('SELECT * FROM users');
?>

3. Fetching Data with PDO

<?php
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo 'ID: ' . $row['id'] . ', Name: ' . $row['name'] . '<br>';
}
?>

4. Closing Connection in PDO

// Simply set the variable to null
$pdo = null;

3.4 MySQLi vs PDO — Comparison

Feature MySQLi PDO

Database Support MySQL only 12 different databases

Object-Oriented Yes (also procedural) Yes

Prepared Statements Yes Yes

Error Handling mysqli_error() try-catch with PDOException

Portability Low (MySQL specific) High (switch databases easily)

Performance Slightly faster for MySQL Very similar

 4. Connecting PHP to MySQL & Selecting a Database

To use a database in PHP, you need to: (1) connect to the MySQL server, (2) select the database you want
to use, and (3) close the connection when done.

4.1 Full Connection Example (MySQLi Object-Oriented)

<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'library';

// Step 1: Create connection

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

// Step 2: Check connection

if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}
echo 'Connected successfully!';

// ... do your database operations here ...

// Step 3: Close connection

$conn->close();
?>

4.2 Selecting a Database (MySQLi Procedural)

If you want to switch to a different database after connecting, use mysqli_select_db():
<?php
$conn = mysqli_connect('localhost', 'root', '', '');
if (!$conn) {
die('Connection failed: ' . mysqli_connect_error());
}
// Select a specific database
if (!mysqli_select_db($conn, 'library')) {
die('Database selection failed: ' . mysqli_error($conn));
}
echo 'Database selected successfully';
mysqli_close($conn);
?>

4.3 Selecting a Database in SQL

You can also switch databases using the SQL USE command:
USE database_name;

-- Example:
USE library;
 Note: PDO selects the database automatically through the DSN string (dbname=library), so you don't need a
separate SELECT step with PDO.

4.4 Real Example — Library Database

Here is a practical example. First, create the database and table in MySQL:
-- SQL Commands:
CREATE DATABASE library;

USE library;

CREATE TABLE books (

id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
price INT(100),
quantity INT(100)
);

Then connect and query from PHP:

<?php
$conn = new mysqli('localhost', 'root', '', 'library');
if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}
$sql = 'SELECT * FROM books';
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo $row['title'] . ' by ' . $row['author'] . '<br>';
}
} else {
echo '0 results';
}
$conn->close();
?>
5. Executing Simple Queries (SELECT, INSERT, UPDATE, DELETE)

Executing queries means sending SQL commands to the MySQL server from PHP and handling the
response. The four main operations are called CRUD — Create, Read, Update, Delete.

5.1 SELECT Query — Read Data

<?php
$conn = new mysqli('localhost', 'root', '', 'library');

// Execute SELECT query

$sql = 'SELECT id, name, email FROM users';
$result = $conn->query($sql);

// Check and display results

if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo 'ID: ' . $row['id'] . '<br>';
echo 'Name: ' . $row['name'] . '<br>';
echo 'Email: ' . $row['email'] . '<br><br>';
}
} else {
echo '0 results found';
}
$conn->close();
?>

num_rows tells you how many rows were returned. The while loop runs once for every row.

5.2 INSERT Query — Add New Data

<?php
$conn = new mysqli('localhost', 'root', '', 'library');

$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@[Link]')";

if ($conn->query($sql) === TRUE) {

echo 'New record created successfully';
} else {
echo 'Error: ' . $conn->error;
}
$conn->close();
?>

For INSERT, UPDATE, DELETE — the query returns TRUE on success or FALSE on failure. You check with
=== TRUE.

5.3 UPDATE Query — Modify Existing Data

<?php
$conn = new mysqli('localhost', 'root', '', 'library');

$sql = "UPDATE users SET email = 'new@[Link]' WHERE id = 1";

 if ($conn->query($sql) === TRUE) {
echo 'Record updated successfully';
} else {
echo 'Error updating record: ' . $conn->error;
}
$conn->close();
?>

5.4 DELETE Query — Remove Data

<?php
$conn = new mysqli('localhost', 'root', '', 'library');

$sql = 'DELETE FROM users WHERE id = 1';

if ($conn->query($sql) === TRUE) {

echo 'Record deleted successfully';
} else {
echo 'Error deleting record: ' . $conn->error;
}
$conn->close();
?>

5.5 All CRUD Operations in One Script

<?php
$conn = new mysqli('localhost', 'root', '', 'library');
if ($conn->connect_error) { die('Connection failed: ' . $conn->connect_error); }

// READ
$result = $conn->query('SELECT id, name, email FROM users');
while ($row = $result->fetch_assoc()) {
echo $row['id'] . ' - ' . $row['name'] . '<br>';
}

// CREATE
$conn->query("INSERT INTO users (name, email) VALUES ('Alice', 'alice@[Link]')");

// UPDATE
$conn->query("UPDATE users SET email='updated@[Link]' WHERE id=1");

// DELETE
$conn->query('DELETE FROM users WHERE id=1');

$conn->close();
?>
6. Retrieving Query Results & Counting Records

6.1 Retrieving Query Results — Step by Step

When you run a SELECT query, MySQL sends back a result set. Here is how to get the data out of it:

• Step 1: Establish Connection — Use MySQLi or PDO to connect to the database.

• Step 2: Execute SELECT Query — Use $conn->query() or mysqli_query() with your SQL.
• Step 3: Process Results — Use fetch functions inside a loop to get rows one by one.
• Step 4: Display or Store Data — Use $row['column_name'] to access each value.
• Step 5: Close Connection — Always close when done.

6.2 Full Retrieval Example

<?php
$conn = new mysqli('localhost', 'username', 'password', 'database');
if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}

// Execute SELECT query

$sql = 'SELECT id, name, email FROM users';
$result = $conn->query($sql);

// Process results
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo 'ID: ' . $row['id'] . '<br>';
echo 'Name: ' . $row['name'] . '<br>';
echo 'Email: ' . $row['email'] . '<br><hr>';
}
} else {
echo '0 results';
}
$conn->close();
?>

6.3 Counting Returned Records

To count how many rows a query returns, use the SQL COUNT() function. This is useful when you want a
number rather than the actual data.

SQL Syntax for Counting

SELECT COUNT(*) AS total_records
FROM table_name
WHERE condition;

-- Example: Count active users

SELECT COUNT(*) AS total_records FROM users WHERE status = 'active';
COUNT(*) counts all rows. AS total_records gives the result column a name so you can easily access it in
PHP.

PHP Example — Counting Records

<?php
$conn = new mysqli('localhost', 'username', 'password', 'database');
if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}

// Query to count active users

$sql = "SELECT COUNT(*) AS total_records FROM users WHERE status = 'active'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
$total = $row['total_records'];
echo 'Total Active Users: ' . $total;
} else {
echo '0 records';
}
$conn->close();
?>

Note: You can also use $result->num_rows to quickly check how many rows were returned by any SELECT
query — without using COUNT() in SQL.

6.4 Updating Records with PHP

Updating records means changing existing data in a table. You use the SQL UPDATE command with a
WHERE clause to target specific rows.
-- SQL Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

-- Example:
UPDATE users SET email = 'new_email@[Link]' WHERE id = 1;

<?php
$conn = new mysqli('localhost', 'root', '', 'library');
if ($conn->connect_error) {
die('Connection failed: ' . $conn->connect_error);
}

$sql = "UPDATE users SET email = 'new_email@[Link]' WHERE id = 1";

if ($conn->query($sql) === TRUE) {

echo 'Record updated successfully';
} else {
echo 'Error updating record: ' . $conn->error;
}
$conn->close();
?>
7. CRUD Operations — Complete Overview

CRUD stands for Create, Read, Update, Delete. These are the four fundamental operations for managing
data in any database. Everything you do with a database falls into one of these four categories.

Letter Operation SQL Command What it does

C Create INSERT INTO Adds new rows/records to a table

R Read SELECT Retrieves/reads data from a table

U Update UPDATE Modifies existing data in a table

D Delete DELETE Removes rows/records from a table

7.1 Key PHP Functions for CRUD

Function / Method Purpose

mysqli_connect() / new mysqli() Connect to MySQL server

mysqli_query() / $conn->query() Execute any SQL query

mysqli_fetch_assoc() Fetch one row as associative array

mysqli_fetch_array() Fetch one row as numeric or associative array

mysqli_num_rows() Get total number of rows in result

mysqli_error() Get error message from last operation

mysqli_close() Close database connection

new PDO() Create PDO connection

$pdo->query() Execute query with PDO

$stmt->fetch() Fetch row with PDO

7.2 Prepared Statements — Why & How

A prepared statement is a safer way to run queries. It separates the SQL structure from the actual data. This
protects against SQL injection attacks — where a hacker puts malicious SQL code into a form field.

MCQ Insight: Primary advantage of prepared statements

Advantage Explanation

Security (SQL Injection prevention) User input is treated as data, not SQL code — hackers cannot break the query

Performance (Query plan caching) MySQL compiles the query once and reuses it for multiple executions

Both of the above Correct answer: 'All of the above' (security + performance)
8. Exception Handling in PHP

Exception handling is a way to deal with errors in a controlled manner. Instead of the program crashing or
showing confusing error messages, you catch the error and handle it gracefully.

8.1 Key Terms

• Exception: An error or unexpected event that happens when a program runs.
• try block: The code that might cause an error goes inside try { }.
• catch block: If an error happens in try, the catch block runs and handles it.
• throw: Used to manually trigger (create) an exception.
• finally block: Optional. Code in finally always runs, whether or not an error occurred.

8.2 Basic Exception Handling Structure

<?php
try {
// Code that might throw an error
throw new Exception('Something went wrong!');
} catch (Exception $e) {
// Handle the error here
echo 'Error caught: ' . $e->getMessage();
} finally {
// This always runs
echo 'This always executes.';
}
?>

8.3 Exception Handling with Database Connection (PDO)

PDO automatically throws exceptions when something goes wrong. You must use try-catch with PDO:
<?php
try {
$conn = new PDO('mysql:host=localhost;dbname=library', 'root', '');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo 'Connected successfully';

// Run a query
$stmt = $conn->query('SELECT * FROM books');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['title'] . '<br>';
}
} catch (PDOException $e) {
echo 'Database error: ' . $e->getMessage();
} finally {
$conn = null; // Always close connection
}
?>
8.4 Exception Object Methods
Method Returns

$e->getMessage() The error message string

$e->getCode() The error code number

$e->getFile() The file name where the error happened

$e->getLine() The line number where the error happened

$e->getTrace() Array showing the call stack (path to the error)

8.5 Custom Exception Classes

You can create your own exception classes by extending the built-in Exception class:
<?php
class DatabaseException extends Exception {
public function errorMessage() {
return 'DB Error on line ' . $this->getLine() . ': ' . $this->getMessage();
}
}

try {
throw new DatabaseException('Connection refused');
} catch (DatabaseException $e) {
echo $e->errorMessage();
}
?>
9. Django — ORM and Database Concepts

Django is a Python web framework. Instead of writing raw SQL, Django uses an ORM (Object-Relational
Mapper) that lets you work with databases using Python classes and objects. Django's ORM translates your
Python code into SQL automatically.

9.1 Database Configuration ([Link])

Django connects to databases using the DATABASES dictionary in the [Link] file:
# [Link]

# Default: SQLite (for local development)

DATABASES = {
'default': {
'ENGINE': '[Link].sqlite3',
'NAME': BASE_DIR / 'db.sqlite3',
}
}

# For MySQL production database:

DATABASES = {
'default': {
'ENGINE': '[Link]',
'NAME': 'my_database',
'USER': 'db_user',
'PASSWORD': 'db_password',
'HOST': 'localhost',
'PORT': '3306',
}
}

• ENGINE: The database backend driver (e.g., [Link] for PostgreSQL).

• NAME: The name of the database.
• USER / PASSWORD: Credentials (username and password) for the database.
• HOST / PORT: Where the database server is running (e.g., localhost, port 5432 for PostgreSQL).
• Adapters: External databases need a driver: psycopg2 for PostgreSQL, mysqlclient for MySQL.

9.2 Django ORM — Models and Migrations

The Django ORM is the bridge between your Python code and the database tables.

Models — Python Classes as Database Tables

Each model is a Python class that inherits from [Link]. Each attribute (field) of the class becomes a
column in the database table.
# [Link]
from [Link] import models

class Book([Link]):
title = [Link](max_length=255)
 author = [Link](max_length=255)
price = [Link](max_digits=8, decimal_places=2)

def __str__(self):
return [Link]

This model creates a database table named app_book with columns: id (auto), title, author, price.

Migrations — Applying Changes to the Database

When you change your models, you must run migrations to update the actual database:

Command What it does

python [Link] makemigrations Scans models for changes, creates migration files (blue

python [Link] migrate Applies migration files to the actual database (creates

9.3 CRUD Operations with Django ORM

Django's ORM replaces SQL with Python methods. Here is how each CRUD operation works:

Create — Add a new record

# Method 1: create()
book = [Link](title='Python Basics', author='Smith', price=29.99)

# Method 2: save()
book = Book(title='Python Basics', author='Smith', price=29.99)
[Link]()

Read — Retrieve records

# Get ALL records
books = [Link]()

# Get ONE record by primary key (raises error if not found)

book = [Link](id=1)

# Filter records (returns a QuerySet list)

cheap_books = [Link](price__lt=20)

# Get first result

first_book = [Link]()

Update — Modify existing records

# Method 1: Change attribute and save() (single record)
book = [Link](id=1)
[Link] = 39.99
[Link]()

# Method 2: update() on a QuerySet (bulk update)

[Link](author='Smith').update(price=35.00)

Delete — Remove records

 # Delete a single record
book = [Link](id=1)
[Link]()

# Delete multiple records

[Link](price__gt=100).delete()

9.4 Django ORM CRUD Summary Table

Operation SQL Equivalent Django ORM

Create INSERT INTO [Link]() or [Link]()

Read All SELECT * FROM [Link]()

Read One SELECT WHERE id=X [Link](id=X)

Read Filtered SELECT WHERE condition [Link](field=value)

Update (single)UPDATE WHERE id=X [Link]=value; [Link]()

Update (bulk) UPDATE WHERE condition [Link](...).update(...)

Delete DELETE WHERE condition [Link]() or [Link]()

9.5 Django Model Relationships

Django models can define relationships between tables using special field types:

Relationship Django Field Example

One-to-Many ForeignKey An Album has many Songs. Song has a ForeignKey to Album.

Many-to-Many ManyToManyField Articles can have many Tags, Tags can belong to many Articles.

One-to-One OneToOneField A User has exactly one Profile; a Profile belongs to one User.
10. Quick Revision — Key Points for Exam

10.1 Must-Know Definitions

• MySQL: A popular open-source relational database management system (RDBMS) used to store,
retrieve, and manage data.
• PHP: A server-side scripting language used to build dynamic web pages. It connects to MySQL to read
and write data.
• MySQLi: MySQL Improved — a PHP extension for connecting to MySQL. 'i' = improved. Supports
procedural and object-oriented styles.
• PDO: PHP Data Objects — a PHP extension that works with 12 different databases (not just MySQL).
More portable than MySQLi.
• CRUD: Create, Read, Update, Delete — the four basic database operations.
• SQL Injection: A security attack where malicious SQL code is inserted into a query through user input.
Prevented by prepared statements.
• ORM: Object-Relational Mapper — translates between Python/PHP objects and database tables.
Django uses its own ORM.
• Migration: A file that tracks changes to database models. Run makemigrations then migrate to apply
changes.
• Query: An SQL instruction sent to the database. Examples: SELECT, INSERT, UPDATE, DELETE.
• Result Set: The data returned by a SELECT query. You loop through it using fetch functions.

10.2 Common Exam MCQ Topics

• Prepared statements prevent SQL injection AND improve performance — answer is 'All of the above'.
• MySQLi works only with MySQL. PDO works with 12 different databases.
• mysqli_fetch_assoc() returns an associative array (key = column name).
• mysqli_num_rows() tells you how many rows were returned.
• DROP TABLE deletes the table. TRUNCATE keeps the table but removes all data.
• USE database_name; switches to a specific database.
• COUNT(*) counts all rows in a result set.
• The WHERE clause is optional but important — without it UPDATE/DELETE affects ALL rows.
• PDO uses try-catch for error handling. MySQLi uses mysqli_error().
• Django makemigrations creates blueprint files. migrate applies them to the database.
• ForeignKey = One-to-Many. ManyToManyField = Many-to-Many. OneToOneField = One-to-One.
• [Link]() = SELECT *. [Link]() = SELECT WHERE unique id.

10.3 PHP MySQL Cheat Sheet

Task MySQLi Code

Connect new mysqli('host','user','pass','db')

Check error if ($conn->connect_error) die(...)

Run query $result = $conn->query($sql)

 Fetch row $row = $result->fetch_assoc()

Count rows $result->num_rows

INSERT $conn->query('INSERT INTO ... VALUES ...')

UPDATE $conn->query('UPDATE ... SET ... WHERE ...')

DELETE $conn->query('DELETE FROM ... WHERE ...')

Count SQL SELECT COUNT(*) AS total FROM table

Close $conn->close()

10.4 SQL Commands Cheat Sheet

SQL Command Syntax / Example

Create DB CREATE DATABASE dbname;

Delete DB DROP DATABASE dbname;

Use DB USE dbname;

List DBs SHOW DATABASES;

Create Table CREATE TABLE t (col1 INT, col2 VARCHAR(50));

Delete Table DROP TABLE tablename;

Modify Table ALTER TABLE t ADD COLUMN col datatype;

List Tables SHOW TABLES;

Insert Row INSERT INTO t (col1,col2) VALUES (val1,val2);

Read Rows SELECT * FROM t WHERE condition;

Update Row UPDATE t SET col=val WHERE condition;

Delete Row DELETE FROM t WHERE condition;

Clear Table TRUNCATE TABLE t;

Count Rows SELECT COUNT(*) AS total FROM t;

Note: Good luck on your exam! Key focus areas: (1) MySQLi vs PDO differences, (2) All CRUD query syntax,
(3) How to connect PHP to MySQL, (4) Exception handling with try-catch, (5) Django ORM methods and
relationships.