Knowledge / Computer Applications / PHP and MySQL web development

PHP and MySQL web development

Computer Applications (BCA) · Applications · free study notes for exams, placements and interviews

In short

PHP (PHP: Hypertext Preprocessor) is a server-side scripting language embedded in HTML that generates dynamic pages; MySQL (and its drop-in fork MariaDB) is the relational database that stores the data behind them. Together with Linux and Apache they form the LAMP stack, which still runs a very large share of Indian web hosting, college project work and WordPress sites (WordPress alone powers a big fraction of all websites and is pure PHP+MySQL). BCA syllabi examine it as a full paper — syntax, arrays, strings, forms, sessions, file handling, OOP, database connectivity, CRUD — and lab exams ask for a working registration/login or inventory module. Placement rounds for web-developer and PHP-developer roles at Indian service and product companies ask prepared statements, sessions, joins and password_hash almost every time.

Explained

PHP language core

PHP OOP, sessions and forms

MySQL: database side

Connecting PHP to MySQL — and doing it safely

Deployment and the wider ecosystem

Key points

Formulas and examples

Index/record math: rows scanned in a full table scan = N; with a B-tree index ≈ log_b(N) node reads + matching rows, where N = number of rows, b = index fan-out
Pagination: LIMIT $per_page OFFSET ($page − 1) × $per_page; total pages = ceil(total_rows ÷ per_page)
Storage estimate: table size ≈ rows × (sum of column byte widths + row overhead); INT = 4 bytes, BIGINT = 8, DATETIME = 5, DECIMAL(10,2) ≈ 5, VARCHAR(n) = actual bytes + 1 or 2 length bytes (utf8mb4 = up to 4 bytes per character)
Session/upload limits: effective upload size = min(upload_max_filesize, post_max_size, memory_limit)

Example 1 — a safe login with PDO and password hashing

<?php // db.php
$pdo = new PDO('mysql:host=localhost;dbname=campus;charset=utf8mb4',
               'app_user', getenv('DB_PASS'),
               [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false]);
<?php // login.php
session_start();
require 'db.php';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $email = trim($_POST['email'] ?? '');
    $pass  = $_POST['password'] ?? '';
    $st = $pdo->prepare('SELECT id, name, pass_hash FROM users WHERE email = ?');
    $st->execute([$email]);
    $u = $st->fetch();
    if ($u && password_verify($pass, $u['pass_hash'])) {
        session_regenerate_id(true);
        $_SESSION['uid'] = $u['id'];
        header('Location: dashboard.php'); exit;
    }
    $err = 'Invalid email or password.';   // never say which one is wrong
}
?>
<p><?= isset($err) ? htmlspecialchars($err, ENT_QUOTES, 'UTF-8') : '' ?></p>

Registration side: $hash = password_hash($pass, PASSWORD_DEFAULT); then INSERT INTO users (name,email,pass_hash) VALUES (?,?,?).

Example 2 — injection, arithmetic of the damage

Unsafe code: $sql = "SELECT * FROM users WHERE email='$e' AND pass='$p'"; With $e = "x' OR '1'='1' -- " the query becomes ... WHERE email='x' OR '1'='1' -- ' AND pass='', whose WHERE is always true, so it returns all rows; a script taking $u = $rows[0] logs the attacker in as the first user (often the admin, id 1). Prepared version sends email = ? and binds the whole string as one value — zero rows match, login fails. Fix cost: 2 lines. Breach cost under the DPDP Act, 2023: penalties for failing to take reasonable security safeguards run to a maximum of ₹250 crore per instance (the Board decides the quantum) once the duty applies from 13 May 2027 - until then compensation under IT Act s.43A - plus CERT-In reporting within 6 hours.

Example 3 — joins and aggregation on a campus placement database

CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80) NOT NULL,
  branch VARCHAR(30), cgpa DECIMAL(3,2)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE offers (id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL,
  company VARCHAR(80), ctc DECIMAL(10,2),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE) ENGINE=InnoDB;

-- students with their offers (only placed students)
SELECT s.name, o.company, o.ctc FROM students s
INNER JOIN offers o ON o.student_id = s.id ORDER BY o.ctc DESC;

-- every student, placed or not: unplaced show NULL
SELECT s.name, o.company FROM students s
LEFT JOIN offers o ON o.student_id = s.id;

-- branches whose average CTC exceeds 6 lakh, among students with CGPA above 7
SELECT s.branch, COUNT(o.id) AS offers, ROUND(AVG(o.ctc),2) AS avg_ctc
FROM students s JOIN offers o ON o.student_id = s.id
WHERE s.cgpa > 7.00
GROUP BY s.branch HAVING AVG(o.ctc) > 600000 ORDER BY avg_ctc DESC;

With rows BCA ₹4,50,000 and ₹5,50,000, and BCom ₹7,00,000 — BCA average = (450000+550000)/2 = ₹5,00,000 (excluded by HAVING), BCom average = ₹7,00,000 (kept). If students has 5 rows and offers has 3 (two students unplaced), the INNER JOIN returns 3 rows and the LEFT JOIN returns 5.

Example 4 — pagination and index arithmetic

A jobs table has 5,00,000 rows; a listing page shows 20 per page.

Example 5 — row size estimate

users(id INT, name VARCHAR(50), email VARCHAR(100), pass_hash CHAR(60), created DATETIME), data mostly ASCII averaging 20 and 25 characters.

MySQLiPDO
DatabasesMySQL/MariaDB only12+ drivers (MySQL, PostgreSQL, SQLite, SQL Server)
API styleProcedural and OOPOOP only
Placeholders? only? and named :email
Prepared statementsYesYes
Named binding / fetch into objectLimitedRich (FETCH_CLASS, fetchAll)
Best forLegacy MySQL-only codeNew projects, portability
SessionCookie
Stored whereServer (file/Redis)Browser
SizePractically largeAbout 4 KB
LifetimeTill browser close or timeoutTill expiry date set
Tamper-proofYes (id only on client)No — user can edit
Typical useLogin state, cart, flash messages"Remember me", theme, consent

Where it is used

Interview questions

Common mistakes

Related topics

Open in the Knowledge reader →Find jobs →

More in Applications