PHP and MySQL Interview Questions and Answers: 60 Questions from Easy to Hard

PHP and MySQL interview questions: Easy questions (freshers)

1. What is the difference between echo and print in PHP?

Both output text and both are language constructs, so parentheses are optional. echo has no return value and accepts several comma-separated arguments, for example echo $a, ' ', $b. print accepts exactly one argument and always returns 1, so it can be used inside an expression, although that is rarely useful. The speed difference is negligible. In real projects most teams use echo, or the short tag <?= $value ?> in templates, and always escape the value with htmlspecialchars() when it came from a user.

<?php
$name = 'Asha';
echo 'Hello, ', $name, "\n";
$result = print "Printed once\n";
echo $result, "\n"; // 1

2. What is the difference between == and === in PHP?

== compares values after type juggling, while === is true only when both the value and the type match. PHP 8 made loose comparison safer: comparing a number with a non-numeric string now compares them as strings, so 0 == 'abc' is false, whereas PHP 7 returned true. Two numeric strings are still compared as numbers, so '1' == '01' is true. Use === by default, especially with functions such as strpos(), where 0 is a valid position and false means not found.

<?php
var_dump(0 == "abc");    // bool(false) in PHP 8, true in PHP 7
var_dump("1" == "01");   // bool(true): both are numeric strings
var_dump("1" === "01");  // bool(false)
var_dump(null == false); // bool(true)
var_dump(strpos("abc", "a") === false); // bool(false): found at 0

3. What are PHP's scalar types, and what does declare(strict_types=1) change?

PHP has four scalar types: int, float, string and bool. By default PHP runs in coercive mode, so passing the string '2' to an int parameter is silently converted. Adding declare(strict_types=1) as the first statement of a file makes PHP throw a TypeError instead. The setting applies to calls made from that file, not to the file where the function is defined. One allowed exception is that an int is still accepted where a float is expected. Many teams enable it in every file to catch bugs early.

<?php
declare(strict_types=1);
function add(int $a, int $b): int { return $a + $b; }
echo add(2, 3), "\n"; // 5
try {
    echo add("2", 3);
} catch (TypeError $e) {
    echo "TypeError: ", $e->getMessage(), "\n";
}

4. What are indexed, associative and multidimensional arrays in PHP?

PHP has a single array type, an ordered map, used in three ways. An indexed array has integer keys starting from 0. An associative array uses string keys, like a dictionary. A multidimensional array holds arrays as values, which is how database rows usually look after fetchAll(). Because every array is an ordered map, keys keep insertion order and you can even mix key types. Useful functions to know are count(), in_array(), array_keys(), array_key_exists() and array_column().

<?php
$cities = ['Pune', 'Delhi', 'Chennai'];
$salary = ['Ravi' => 52000, 'Meena' => 75000];
$rows = [
    ['id' => 1, 'name' => 'Ravi'],
    ['id' => 2, 'name' => 'Meena'],
];
echo $cities[1], "\n";       // Delhi
echo $salary['Meena'], "\n"; // 75000
echo $rows[1]['name'], "\n"; // Meena
echo count($rows), "\n";     // 2

5. What is the difference between include, require, include_once and require_once?

All four load and run another PHP file. If the file cannot be loaded, include emits a warning and the script carries on, while require stops the script with a fatal error. The _once variants first check whether that file was already loaded and skip it if so, which prevents errors such as redeclaring a function or class. Use require_once for files the page cannot work without, such as configuration. In modern projects you rarely load class files by hand, because Composer's autoloader loads each class the first time it is used.

6. How do sessions differ from cookies in PHP?

A cookie is stored in the browser and sent with every request to the site, so the user can read and change it, and each cookie is limited to about 4 KB. A session keeps the data on the server; the browser holds only a random session ID, by default in a cookie named PHPSESSID. Call session_start() before any output, then read and write $_SESSION. Keep login state and roles in the session, and use plain cookies only for harmless preferences such as language. Mark cookies HttpOnly, Secure and SameSite where possible.

<?php
session_start();
$_SESSION['user_id'] = 101;
setcookie('lang', 'hi', [
    'expires' => time() + 86400 * 30,
    'path' => '/',
    'secure' => true,
    'samesite' => 'Lax',
]);

7. When should you use GET and when should you use POST?

GET sends data in the URL query string, which PHP exposes in $_GET. It shows up in browser history and server logs, can be bookmarked and cached, and URLs have practical length limits. POST sends data in the request body, available in $_POST, and is the right choice for forms that create or change data, logins and file uploads. A GET request should never change anything on the server. Neither method is encrypted by itself: POST only keeps data out of the URL, so HTTPS is still required for anything sensitive.

8. How do you connect to MySQL from PHP using PDO?

Create a PDO object with a DSN that names the driver, host, database and charset, then use prepared statements for every query that takes input. charset=utf8mb4 in the DSN gives full Unicode, including emoji. ERRMODE_EXCEPTION makes failures throw a PDOException; it is the default since PHP 8.0 but still worth setting explicitly. FETCH_ASSOC returns rows as associative arrays. Turning off emulated prepares makes MySQL prepare the statement on the server. Read the credentials from environment variables, never hard-code them in the repository.

<?php
$dsn = 'mysql:host=localhost;dbname=shop;charset=utf8mb4';
$pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = ?');
$stmt->execute(['asha@example.com']);
$user = $stmt->fetch(); // array or false

9. What is SQL injection, and how do prepared statements prevent it?

SQL injection happens when user input is concatenated into a SQL string, so the input can change the query itself. A login query built as WHERE email = '$email' can be bypassed with input such as ' OR '1'='1. Prepared statements keep the query and the values separate: with native prepares MySQL parses the SQL first and treats bound values only as data, and with PDO's emulated prepares the driver escapes them safely as long as the charset is set in the DSN. Placeholders work only for values, so table and column names must come from a whitelist.

<?php
// Vulnerable: the input becomes part of the SQL text
$sql = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";

// Safe: the value is sent separately from the SQL
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = :email');
$stmt->execute(['email' => $_POST['email']]);
$user = $stmt->fetch();

10. How do you store user passwords securely in PHP?

Use password_hash() with PASSWORD_DEFAULT and check logins with password_verify(). PASSWORD_DEFAULT currently means bcrypt; it creates a random salt for you and stores the algorithm, cost and salt inside the hash string, so one column is enough. Make that column VARCHAR(255), because the default algorithm can change in future versions. After a successful login, call password_needs_rehash() and save a fresh hash if it returns true. Never use md5() or sha1() for passwords: they are fast, which makes cracking a leaked table cheap.

<?php
$hash = password_hash('S3cret!pass', PASSWORD_DEFAULT);
var_dump(password_verify('S3cret!pass', $hash)); // bool(true)
var_dump(password_verify('wrong', $hash));       // bool(false)
var_dump(password_needs_rehash($hash, PASSWORD_DEFAULT)); // bool(false)

11. What is the difference between a class and an object, and what does a constructor do?

A class is a blueprint that defines properties and methods; an object is one instance of that class, created with new. The constructor, __construct(), runs automatically when the object is created and sets up its initial state. Since PHP 8.0, constructor property promotion lets you declare and assign properties directly in the constructor's parameter list, which removes a lot of boilerplate. In the example, Product has a public name and a private price that outside code can read only through the price() method.

<?php
class Product {
    public function __construct(
        public string $name,
        private float $price,
    ) {}
    public function price(): float { return $this->price; }
}
$p = new Product('Pen', 20.5);
echo $p->name, ' costs ', $p->price(), "\n"; // Pen costs 20.5

12. What do public, protected and private mean in PHP?

They control where a property, method or constant can be used. public members can be used from anywhere. protected members can be used inside the class and its child classes. private members can be used only inside the class that declares them, not even in children. A good default is to keep properties private and expose behaviour through public methods, so the class enforces its own rules, such as never accepting a negative deposit. PHP 8.1 also added readonly properties, which can be set once and never changed afterwards.

<?php
class Account {
    private float $balance = 0;
    public function deposit(float $amount): void {
        if ($amount <= 0) {
            throw new InvalidArgumentException('Amount must be positive');
        }
        $this->balance += $amount;
    }
    public function balance(): float { return $this->balance; }
}
$acc = new Account();
$acc->deposit(500);
echo $acc->balance(), "\n"; // 500; $acc->balance would throw an Error

13. What is the difference between isset() and empty()?

isset() returns true when a variable or array key exists and is not null. empty() returns true when the variable does not exist or holds a falsy value: false, 0, 0.0, '0', '', null or an empty array. Neither one raises a warning for an undefined variable. The usual trap is the string '0': a form field holding 0 counts as empty, which breaks validation for quantity or rating fields. To detect a key whose value is null, use array_key_exists(). For defaults, the null coalescing operator ?? is cleaner.

<?php
$qty = '0';
var_dump(isset($qty));   // bool(true)
var_dump(empty($qty));   // bool(true): '0' is falsy
var_dump(isset($none));  // bool(false), no warning
$row = ['phone' => null];
var_dump(isset($row['phone']));             // bool(false)
var_dump(array_key_exists('phone', $row));  // bool(true)
echo $_GET['page'] ?? 1, "\n";              // 1

14. Explain the main types of JOIN in MySQL.

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table, with NULLs where the right table has no match, which is how you find customers who never placed an order. RIGHT JOIN is the mirror image and is rarely needed. CROSS JOIN returns every combination of rows. A self join joins a table to itself, for example employees to their managers. MySQL has no FULL OUTER JOIN; you emulate it with a LEFT JOIN combined with a RIGHT JOIN through UNION.

-- All customers, with their orders if any
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- Customers who have never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

15. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping, so it cannot use aggregate functions such as COUNT(). HAVING filters groups after GROUP BY has run, so conditions like COUNT(*) > 100 belong there. Put every condition that does not need an aggregate in WHERE, because filtering early lets MySQL use indexes and leaves fewer rows to group. In the example, WHERE keeps only orders from 2026, and HAVING then keeps only the cities with more than 100 of those orders.

SELECT city, COUNT(*) AS orders_count
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY city
HAVING COUNT(*) > 100
ORDER BY orders_count DESC;

16. What are a primary key and a foreign key?

A primary key uniquely identifies each row; it cannot be NULL and a table has only one. InnoDB stores the table's rows in primary key order (the clustered index), so a short, increasing key such as a BIGINT AUTO_INCREMENT is a good default. A foreign key is a column that references the primary or a unique key of another table, and InnoDB enforces it: you cannot insert an order for a customer that does not exist. ON DELETE RESTRICT or CASCADE decides what happens to child rows when the parent row is deleted.

CREATE TABLE customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

17. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes the rows that match a WHERE clause, one row at a time; it fires DELETE triggers and can be rolled back inside a transaction. TRUNCATE empties the whole table by recreating it, so it is far faster on big tables, resets AUTO_INCREMENT and fires no triggers; it causes an implicit commit, so it cannot be rolled back. MySQL also refuses to truncate a table that another table references through a foreign key. DROP removes the table itself, structure and data, together with its indexes and triggers.

18. What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed length: values are padded with spaces to n characters when stored, and trailing spaces are removed when you read them back. VARCHAR(n) stores only the actual characters plus one or two bytes for the length. Use CHAR for values that always have the same length, such as a two-letter state code or a 32-character MD5 hex string, and VARCHAR for names, emails and addresses. With utf8mb4 a character can take up to four bytes, which matters for index key limits and the 65,535-byte row size limit.

19. What is an index in MySQL, and why does it make queries faster?

An index is a separate structure, a B-tree in InnoDB, that keeps a column's values sorted together with the primary key of each row. Without one, a query such as WHERE email = ? must read every row in the table; with one, MySQL walks the tree and reaches the matching rows in a few page reads. Indexes also help ORDER BY and joins. The cost is extra disk space and slower INSERT, UPDATE and DELETE, since each index must be updated too, so index the columns you filter, join and sort on, not every column.

CREATE INDEX idx_users_email ON users (email);

-- key column in the output should show idx_users_email
EXPLAIN SELECT id, name FROM users WHERE email = 'asha@example.com';

20. What is Composer, and how does autoloading work with namespaces?

Composer is PHP's dependency manager. You list packages in composer.json, run composer require or composer install, and it downloads them into vendor/, recording exact versions in composer.lock; commit the lock file so every machine installs the same versions. Composer also builds an autoloader. With a PSR-4 mapping of the App\ namespace to src/, the class App\Service\Mailer is loaded from src/Service/Mailer.php the first time it is used, so you only require vendor/autoload.php once. Namespaces stop class names from different packages clashing.

{
    "require": {
        "php": ">=8.2",
        "monolog/monolog": "^3.0"
    },
    "autoload": {
        "psr-4": { "App\\": "src/" }
    }
}

PHP and MySQL interview questions: Medium questions (1-3 years)

21. What is the difference between an interface and an abstract class?

An interface is a contract: it declares public method signatures (and constants) without code, and a class can implement many interfaces. An abstract class can hold real methods, properties and a constructor alongside abstract methods, but a class can extend only one. Use an interface for a capability that unrelated classes share, such as a PaymentGateway implemented by different gateway adapters, so the rest of the code depends only on the contract. Use an abstract class when subclasses share real implementation. They are often used together, as in the example.

<?php
interface PaymentGateway {
    public function charge(int $paise): string;
}
abstract class BaseGateway implements PaymentGateway {
    protected function log(string $msg): void { echo "[log] $msg\n"; }
}
class FakeGateway extends BaseGateway {
    public function charge(int $paise): string {
        $this->log("charging $paise");
        return 'txn_' . $paise;
    }
}
echo (new FakeGateway())->charge(49900), "\n"; // txn_49900

22. What are traits in PHP, and how do you resolve a conflict between two traits?

A trait is a group of methods and properties copied into a class with use, giving code reuse without inheritance. Traits suit small cross-cutting behaviour such as logging or soft deletes; Laravel's SoftDeletes and HasFactory are traits. If two traits define the same method, PHP throws a fatal error unless you choose one with insteadof, and you can keep the other under a new name with as. Precedence is: the class's own method beats the trait's, and the trait's beats an inherited one. Keep traits small, because they can hide dependencies.

<?php
trait Hello { public function greet() { return 'Hello'; } }
trait Hi    { public function greet() { return 'Hi'; } }
class Greeter {
    use Hello, Hi {
        Hello::greet insteadof Hi;
        Hi::greet as greetShort;
    }
}
$g = new Greeter();
echo $g->greet(), ' / ', $g->greetShort(), "\n"; // Hello / Hi

23. What is the difference between self and static in PHP?

self refers to the class where the code is written and is fixed at compile time. static refers to the class that was actually called at runtime; this is late static binding. Inside a parent method, new self() always creates the parent, while new static() creates whichever child class made the call. That is why factory methods and fluent builders return static, and why a framework can define a create() method once on a base Model and still get a User or an Order back. The static return type has been allowed since PHP 8.0.

<?php
class Model {
    public static function create(): static { return new static(); }
    public static function selfName(): string { return self::class; }
    public static function staticName(): string { return static::class; }
}
class User extends Model {}
echo User::staticName(), "\n";        // User
echo User::selfName(), "\n";          // Model
echo get_class(User::create()), "\n"; // User

24. How do enums work in PHP 8.1, and when would you use a backed enum?

An enum defines a type with a fixed set of values. A pure enum has only cases; a backed enum maps each case to an int or a string, which makes it easy to store in a database column and rebuild later with from(), which throws a ValueError for an unknown value, or tryFrom(), which returns null. Enums can have methods, constants and interfaces. They replace class constants and magic strings, so a parameter typed OrderStatus can never receive a typo like 'payed'. A match expression over the cases keeps the logic readable.

<?php
enum OrderStatus: string {
    case Pending = 'pending';
    case Paid = 'paid';
    public function label(): string {
        return match ($this) {
            self::Pending => 'Awaiting payment',
            self::Paid => 'Paid',
        };
    }
}
echo OrderStatus::from('paid')->label(), "\n"; // Paid
var_dump(OrderStatus::tryFrom('lost'));        // NULL

25. How do you prevent XSS in a PHP application?

XSS happens when user-supplied text is placed into a page without escaping, so the browser runs it as HTML or JavaScript. The main defence is escaping on output, for the right context. In HTML, use htmlspecialchars() with ENT_QUOTES and UTF-8, which converts <, >, & and both quote types into entities; since PHP 8.1 ENT_QUOTES is in the default flags, but passing it keeps intent clear. Inside a script block, use json_encode() with the JSON_HEX_* flags; in URLs, use rawurlencode(). Blade's {{ }} escapes automatically. Add a Content-Security-Policy header as a second layer.

<?php
$name = '<script>alert("hi")</script>';
echo htmlspecialchars($name, ENT_QUOTES, 'UTF-8'), "\n";
// &lt;script&gt;alert(&quot;hi&quot;)&lt;/script&gt;

26. What is CSRF, and how do you protect a PHP form against it?

CSRF tricks a logged-in user's browser into sending a request they did not intend, such as a hidden form on another site posting to your /transfer URL; the browser attaches your session cookie automatically. Protect every state-changing request with a random token stored in the session and placed in a hidden form field, then compare it on submit with hash_equals(), which runs in constant time. Also set the session cookie to SameSite=Lax or Strict, and never change data on GET. Laravel does this for you with the @csrf directive and its CSRF middleware.

<?php
session_start();
if (empty($_SESSION['csrf'])) {
    $_SESSION['csrf'] = bin2hex(random_bytes(32));
}
// Print $_SESSION['csrf'] into a hidden input named csrf in every form
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (!hash_equals($_SESSION['csrf'], $_POST['csrf'] ?? '')) {
        http_response_code(403);
        exit('Invalid request');
    }
}

27. How do you protect PHP sessions from hijacking and session fixation?

Hijacking means stealing a valid session ID; fixation means making the victim log in with an ID the attacker already knows. Serve the whole site over HTTPS and set the session cookie Secure, HttpOnly (so JavaScript cannot read it) and SameSite. Call session_regenerate_id(true) right after login and after any privilege change, so a planted ID becomes useless. Enable session.use_strict_mode so PHP rejects IDs it did not create, keep the ID out of URLs, expire idle sessions on the server, and destroy the session on logout.

<?php
ini_set('session.use_strict_mode', '1');
session_set_cookie_params([
    'lifetime' => 0,
    'path' => '/',
    'secure' => true,
    'httponly' => true,
    'samesite' => 'Lax',
]);
session_start();
// after the password has been verified:
session_regenerate_id(true);
$_SESSION['user_id'] = 42;

28. How does exception handling work in PHP 8, and what is the difference between Exception and Error?

Both implement the Throwable interface. Exception is for problems the application expects and can handle, such as a failed payment call; Error covers engine problems such as TypeError, ValueError or DivisionByZeroError. A plain catch (Exception $e) does not catch an Error, so a top-level handler should catch Throwable. You can catch several types in one block with a pipe, as in catch (A | B $e), and a finally block runs whether or not anything was thrown, which makes it the place to release locks or close files. Log details; show users a generic message.

<?php
function divide(int $a, int $b): int {
    return intdiv($a, $b);
}
try {
    echo divide(10, 0);
} catch (DivisionByZeroError $e) {
    echo 'Error: ', $e->getMessage(), "\n"; // Division by zero
} catch (Throwable $e) {
    echo 'Unexpected: ', get_class($e), "\n";
} finally {
    echo "done\n";
}

29. What is the difference between PDO and mysqli?

Both support prepared statements and are safe when used correctly. PDO gives one API for many databases, including MySQL, PostgreSQL and SQLite, supports named placeholders, and has useful fetch modes such as FETCH_ASSOC or fetching rows straight into a class. mysqli works only with MySQL but exposes MySQL-specific features such as multi_query() and asynchronous queries, and offers both procedural and object styles. Since PHP 8.1 mysqli throws exceptions by default, as PDO has since 8.0. Most new projects use PDO, or a layer built on it such as Laravel's query builder.

30. Can you bind a column name or an IN list with PDO placeholders?

Not directly. Placeholders stand only for values, so a table name, column name or sort direction cannot be bound; compare that input against a whitelist and fall back to a fixed default. An IN list needs one placeholder per value: build the string of question marks from the array size, then pass the array to execute(). Named placeholders (:email) are easier to read in long queries and positional ones (?) are shorter, but you cannot mix both styles in one statement. Never interpolate the raw values themselves.

<?php
$ids = [3, 7, 9];
$in  = implode(',', array_fill(0, count($ids), '?'));
$sql = "SELECT id, name FROM products WHERE id IN ($in)";
echo $sql, "\n"; // ... WHERE id IN (?,?,?)
// $stmt = $pdo->prepare($sql); $stmt->execute($ids);

$allowed = ['name', 'price', 'created_at'];
$sort = in_array($_GET['sort'] ?? '', $allowed, true) ? $_GET['sort'] : 'name';
echo "ORDER BY $sort\n"; // ORDER BY name

31. Explain array_map, array_filter and array_reduce with an example.

array_map() applies a callback to every element and returns a new array of the same size. array_filter() keeps only the elements for which the callback returns true, and it preserves the original keys, so wrap it in array_values() if you need a clean list, for example before json_encode(). array_reduce() folds the whole array into one value, such as a total. None of them changes the input array. Arrow functions (fn) keep these one-liners short and capture outer variables by value automatically.

<?php
$prices = [120, 450, 80, 999];
$withGst = array_map(fn($p) => round($p * 1.18, 2), $prices);
$costly  = array_filter($prices, fn($p) => $p > 100);
$total   = array_reduce($prices, fn($sum, $p) => $sum + $p, 0);
print_r($withGst);
print_r($costly);   // keys 0, 1 and 3 are kept
echo $total, "\n";  // 1649

32. Write a query to find the second highest salary from an employees table.

The portable answer uses a subquery: take the highest salary that is lower than the overall maximum. It handles ties at the top correctly and returns NULL when there is no second value. On MySQL 8, window functions are cleaner and generalise to the Nth highest: DENSE_RANK() gives equal salaries the same rank with no gaps, so rank 2 is the second distinct salary. If you use ORDER BY salary DESC LIMIT 1 OFFSET 1, add DISTINCT, otherwise two people tied at the top would return the highest salary again.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- MySQL 8: Nth highest, here N = 2
SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

33. What is the N+1 query problem, and how do you fix it in Laravel?

N+1 happens when code loads a list with one query and then runs another query for each row to fetch a relationship, so 100 posts cause 101 queries. In Laravel it usually comes from reading $post->user inside a loop over lazily loaded models. Fix it with eager loading: Post::with('user')->get() runs one query for posts and one WHERE IN query for all their users. Use load() when you already have the collection. In development, call Model::preventLazyLoading() in a service provider so Laravel throws an exception on lazy loading.

<?php
// N+1: one query for posts, then one more per post
foreach (Post::all() as $post) {
    echo $post->user->name;
}

// Eager loading: two queries in total
foreach (Post::with('user')->get() as $post) {
    echo $post->user->name;
}

34. What is middleware in Laravel, and where would you use it?

Middleware wraps the request before it reaches a controller and can also change the response on the way out. Each middleware either passes the request on with $next($request) or stops it, for example with a redirect or a 403. Built-in examples are auth, which sends guests to the login page, CSRF verification for forms, and throttle for rate limiting. Custom middleware fits cross-cutting rules such as blocking disabled accounts. In Laravel 11 and later it is registered in bootstrap/app.php instead of the old app/Http/Kernel.php.

<?php
namespace App\Http\Middleware;
use Closure;
use Illuminate\Http\Request;

class EnsureUserIsActive {
    public function handle(Request $request, Closure $next) {
        if ($request->user() && ! $request->user()->is_active) {
            abort(403, 'Account disabled');
        }
        return $next($request);
    }
}

35. What is a composite index, and what is the leftmost prefix rule?

A composite index covers several columns in a fixed order: sorted by the first column, then by the second within it, and so on. MySQL can seek into it only through a leftmost prefix. An index on (city, status, created_at) serves WHERE city = ?, WHERE city = ? AND status = ?, and a range or sort on created_at once both earlier columns are matched by equality. A filter on status alone cannot seek into it; MySQL 8's skip scan sometimes helps, but do not design around it. Put equality columns first, then the range or sort column.

CREATE INDEX idx_city_status_created ON orders (city, status, created_at);

-- Can seek on the index:
SELECT id FROM orders WHERE city = 'Pune' AND status = 'paid';
SELECT id FROM orders WHERE city = 'Pune' AND status = 'paid'
  ORDER BY created_at DESC;
-- Cannot seek on it (city, the first column, is missing):
SELECT id FROM orders WHERE status = 'paid';

36. How do you read the output of EXPLAIN in MySQL?

EXPLAIN shows the plan MySQL chose, without running the query. Start with type: const and eq_ref are single-row lookups, ref and range use an index, index is a full index scan, and ALL is a full table scan. key shows which index was chosen and rows estimates how many rows will be examined. In Extra, Using index means the index covers the query, while Using filesort or Using temporary means sorting or grouping the index could not handle. From MySQL 8.0.18, EXPLAIN ANALYZE actually runs the query and reports real row counts and timings.

EXPLAIN SELECT id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

-- Runs the query and shows actual rows and time per step
EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42;

37. What are ACID properties, and how do you run a transaction with PDO?

ACID stands for Atomicity (all statements succeed or none do), Consistency (constraints hold before and after), Isolation (concurrent transactions do not see each other's half-finished work) and Durability (committed data survives a crash). In PDO, call beginTransaction(), run the statements, then commit(); on any exception, roll back and rethrow. The tables must use InnoDB, because MyISAM ignores transactions. Keep transactions short and never wait on a network call, such as a payment API, while one is open, because its locks are held until commit.

<?php
try {
    $pdo->beginTransaction();
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
        ->execute([500, 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
        ->execute([500, 2]);
    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

38. What is the difference between InnoDB and MyISAM?

InnoDB has been MySQL's default engine since 5.5 and is the right choice for almost every table. It supports transactions, row-level locking, foreign keys, crash recovery through its redo log, and MVCC, so readers do not block writers. MyISAM has none of these: it locks the whole table on every write and can be left corrupted after a crash. It was once chosen for full-text search and fast COUNT(*) without a WHERE clause, but InnoDB has supported FULLTEXT indexes since 5.6. Convert legacy tables with ALTER TABLE ... ENGINE=InnoDB.

39. Explain normalisation and the first three normal forms with examples.

Normalisation organises tables to remove duplicated data and the update anomalies it causes. 1NF: each column holds one atomic value, so no comma-separated skills in one field; put skills in their own table. 2NF: every non-key column depends on the whole primary key, which matters for composite keys; an order_items table keyed on (order_id, product_id) should not store the product name. 3NF: non-key columns depend only on the key, not on each other, so store city_id rather than both city and its state. Normalise to 3NF, then denormalise deliberately for heavy reports.

40. How do you implement LIMIT and OFFSET pagination safely from PHP?

Compute the offset as (page - 1) multiplied by the page size, after casting the page number to an integer and clamping it to at least 1. Always add an ORDER BY on a unique column, otherwise rows can repeat or disappear between pages. Bind LIMIT and OFFSET with PDO::PARAM_INT: with emulated prepares, which is PDO MySQL's default, values passed to execute() are quoted as strings, and LIMIT '20' is a MySQL syntax error. Large offsets are slow because MySQL still reads and discards every skipped row.

<?php
$perPage = 20;
$page = max(1, (int) ($_GET['page'] ?? 1));
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare('SELECT id, title FROM jobs ORDER BY id DESC LIMIT :lim OFFSET :off');
$stmt->bindValue(':lim', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':off', $offset, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll();

PHP and MySQL interview questions: Hard questions (experienced)

41. How does InnoDB's MVCC work at the default REPEATABLE READ isolation level?

A plain SELECT is a consistent read: the first read in a transaction creates a snapshot, and later plain SELECTs in that transaction see the data as of that snapshot, rebuilt from undo logs, so readers never block writers. Locking reads (SELECT ... FOR UPDATE or FOR SHARE), UPDATE and DELETE work on the latest committed rows instead, and take next-key locks, a row lock plus a lock on the gap before it, which stop other sessions inserting phantom rows into the scanned range. READ COMMITTED takes a fresh snapshot per statement and mostly drops gap locks, which reduces deadlocks.

42. Two users try to buy the last item in stock at the same time. How do you stop overselling?

The bug is read-then-write: both requests read stock = 1, both pass the check, and both decrement. The simplest fix is one atomic statement whose WHERE clause carries the condition, followed by a check of affected rows: if rowCount() is 0, the item was already gone. When the logic needs several steps, open a transaction and lock the row with SELECT ... FOR UPDATE, so the second buyer waits until the first commits and then sees the new stock. Avoid PHP-level locks such as flock(), which stop working once you run more than one server.

<?php
$stmt = $pdo->prepare(
    'UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0'
);
$stmt->execute([$productId]);
if ($stmt->rowCount() === 0) {
    throw new RuntimeException('Out of stock');
}
// one unit is now reserved; create the order next

43. What causes deadlocks in MySQL, and how should a PHP application handle them?

A deadlock happens when two transactions each hold a lock the other needs, for example one updates account 1 then 2 while another updates 2 then 1. InnoDB detects the cycle, rolls back one transaction and returns error 1213, so the application should retry the whole transaction a few times with a short backoff. To make deadlocks rare, touch rows in a consistent order (sort the IDs first), keep transactions short, make sure UPDATE and DELETE conditions use indexes so fewer rows are locked, and read the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS.

<?php
function withRetry(PDO $pdo, callable $work, int $tries = 3): mixed {
    for ($attempt = 1; ; $attempt++) {
        try {
            $pdo->beginTransaction();
            $result = $work($pdo);
            $pdo->commit(); return $result;
        } catch (PDOException $e) {
            if ($pdo->inTransaction()) { $pdo->rollBack(); }
            if (($e->errorInfo[1] ?? 0) !== 1213 || $attempt >= $tries) { throw $e; }
            usleep(100000 * $attempt); // back off before retrying
        }
    }
}

44. A payment gateway sends the same webhook twice. How do you make the handler idempotent?

Gateways retry webhooks until they get a success response, so duplicates are normal and the handler must give the same result however many times it runs. Verify the signature first. Then, in one transaction, insert the provider's event ID into a table with a primary or unique key before doing the business update. If the insert fails with duplicate-key error 1062, the event was already processed: return 200 and do nothing else. Also make the update conditional, such as WHERE status = 'pending', so a late or out-of-order retry cannot move an order backwards.

CREATE TABLE webhook_events (
  event_id VARCHAR(64) PRIMARY KEY,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Inside one transaction:
INSERT INTO webhook_events (event_id) VALUES ('evt_7Hc2kQ');
UPDATE orders SET status = 'paid'
WHERE id = 5501 AND status = 'pending';
-- A second delivery fails on the INSERT with error 1062

45. Why does OFFSET pagination get slow on large tables, and what is keyset pagination?

With OFFSET, MySQL reads and throws away every skipped row, so page 5,000 of a large table is slow, and rows shift between pages when new data arrives. Keyset (seek) pagination remembers the last row of the current page and asks for rows after it, so MySQL jumps straight to that point through an index and every page costs about the same. Sort on a unique combination such as created_at plus id, and back it with a matching index. The trade-off is that you cannot jump to an arbitrary page number, which is fine for feeds, infinite scroll and APIs with a next cursor.

CREATE INDEX idx_jobs_created_id ON jobs (created_at, id);

-- First page
SELECT id, title, created_at FROM jobs
ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page: the last row's created_at and id are the cursor
SELECT id, title, created_at FROM jobs
WHERE created_at < '2026-10-30 18:45:00'
   OR (created_at = '2026-10-30 18:45:00' AND id < 98123)
ORDER BY created_at DESC, id DESC LIMIT 20;

46. Write a query to get the top three earners in each department.

Use a window function. ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) numbers employees within each department, and an outer query keeps numbers 1 to 3. Window functions are evaluated after WHERE, so you cannot filter on them in the same SELECT; that is why the CTE or a derived table is needed. Pick the function by how ties should behave: ROW_NUMBER returns exactly three rows per department, while RANK and DENSE_RANK include everyone tied at the cut-off. Before MySQL 8 this needed correlated subqueries or user variables, which were slow and fragile.

WITH ranked AS (
  SELECT department_id, name, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department_id, name, salary
FROM ranked
WHERE rn <= 3
ORDER BY department_id, rn;

47. What is a clustered index in InnoDB, and what is a covering index?

InnoDB stores each table as a B-tree ordered by the primary key, with the full row in the leaf pages; that is the clustered index. Every secondary index stores its own columns plus the primary key, so a lookup through a secondary index normally needs a second lookup into the clustered index for the rest of the row. If the index already holds every column the query needs, that second step is skipped and EXPLAIN shows Using index: a covering index. So keep primary keys short, since they are copied into every secondary index, and avoid SELECT * on hot queries.

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at, total);

-- Covered: id is the primary key, the rest are in the index
EXPLAIN SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

48. You added an index but MySQL still does a full scan. What could be wrong?

Common causes: the column is wrapped in a function or expression, so the sorted values cannot be searched; a LIKE pattern starts with a wildcard; a string column is compared with a number, which forces a conversion on every row; the columns are in the wrong order for a composite index; or the optimizer estimates the condition matches so much of the table that a full scan is cheaper. Rewrite the condition so the bare column is compared, match the types, or add a functional index (MySQL 8.0.13+). Confirm with EXPLAIN, and run ANALYZE TABLE if statistics look stale.

-- No seek on the created_at index: the column is inside a function
SELECT id FROM orders WHERE DATE(created_at) = '2026-10-01';
-- Same result as a range, which can use the index
SELECT id FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02';

-- phone is VARCHAR: a numeric literal forces a per-row conversion
SELECT id FROM users WHERE phone = 9876543210;   -- no seek, scans all
SELECT id FROM users WHERE phone = '9876543210'; -- uses the index

49. Why should you use utf8mb4 instead of utf8 in MySQL?

In MySQL, utf8 has always meant utf8mb3, which stores at most three bytes per character. Emoji and some rare characters need four bytes, so inserting them fails with an 'Incorrect string value' error in strict mode, or gets truncated otherwise. utf8mb4 is real UTF-8 and has been the server default since MySQL 8.0, with utf8mb4_0900_ai_ci as the default collation. Use it for the database, the tables and the connection, by putting charset=utf8mb4 in the PDO DSN. Watch index sizes: four bytes per character makes long VARCHAR keys bigger, which adds up in composite indexes.

50. How do you add a column or an index to a table with 50 million rows without downtime?

First check what kind of change it is. MySQL 8.0 can add a column with ALGORITHM=INSTANT, which only changes metadata and finishes immediately even on huge tables. Adding a secondary index runs as online DDL with ALGORITHM=INPLACE, LOCK=NONE, so reads and writes continue while it builds. State the algorithm explicitly: if MySQL cannot honour it, the statement fails instead of silently copying the table. Changing a column's type usually needs a full copy, so teams use gh-ost or pt-online-schema-change. Watch replica lag, and kill long transactions first, because they block the metadata lock.

ALTER TABLE orders
  ADD COLUMN coupon_code VARCHAR(32) NULL,
  ALGORITHM=INSTANT;

ALTER TABLE orders
  ADD INDEX idx_orders_coupon (coupon_code),
  ALGORITHM=INPLACE, LOCK=NONE;

51. How do you handle file uploads securely in PHP?

Treat every upload as hostile. Check the upload error code and size on the server, then detect the real type from the file's content with finfo, because both the browser-supplied type and the extension can be faked. Allow a short list of types and choose the extension yourself from that list. Generate a random file name instead of reusing the user's, and store files outside the web root or in object storage such as S3, so an uploaded .php file can never execute. Serve downloads through a script that checks permissions.

<?php
$f = $_FILES['resume'] ?? null;
if (!$f || $f['error'] !== UPLOAD_ERR_OK || $f['size'] > 2 * 1024 * 1024) {
    exit('Upload failed or file too large');
}
$mime = (new finfo(FILEINFO_MIME_TYPE))->file($f['tmp_name']);
$allowed = ['application/pdf' => 'pdf', 'image/png' => 'png', 'image/jpeg' => 'jpg'];
if (!isset($allowed[$mime])) {
    exit('File type not allowed');
}
$name = bin2hex(random_bytes(16)) . '.' . $allowed[$mime];
move_uploaded_file($f['tmp_name'], '/var/app/uploads/' . $name);

52. Why can comparing hashes or tokens with == be a security bug in PHP?

With ==, PHP compares two numeric strings as numbers. A string such as '0e462097...' with only digits after 0e is valid scientific notation for zero, so two completely different hashes of that shape compare as equal. An attacker who can find an input whose hash has that form can pass a check written with ==. PHP 8 changed number-to-string comparison, but comparing two numeric strings is still numeric, so this trap remains. Compare secrets with hash_equals(), which is strict and constant-time, and use === for other string comparisons.

<?php
var_dump(md5('240610708') == md5('QNKCDZO'));   // bool(true)
var_dump(md5('240610708') === md5('QNKCDZO'));  // bool(false)
var_dump(hash_equals(md5('240610708'), md5('QNKCDZO'))); // bool(false)

53. How would you design a secure password reset flow?

Generate the token with random_bytes(32) and send it in the link, but store only its SHA-256 hash with the user ID, an expiry of around 30 minutes and a used flag. A fast hash is fine here because the token is long and random, unlike a password. When the link is used, hash the incoming token, look it up, check that it is unexpired and unused, set the new password with password_hash(), mark the token used and end other sessions. Show the same message whether or not the email exists, to avoid account enumeration, and rate-limit the endpoint.

<?php
$token = bin2hex(random_bytes(32));      // goes into the email link only
$tokenHash = hash('sha256', $token);     // this is what you store
$expiresAt = date('Y-m-d H:i:s', time() + 1800);
// INSERT INTO password_resets (user_id, token_hash, expires_at, used)
//   VALUES (?, ?, ?, 0)

// When the link is opened, hash the token from the URL and look it up:
// SELECT user_id FROM password_resets
//   WHERE token_hash = ? AND used = 0 AND expires_at > NOW()
echo strlen($token), ' ', strlen($tokenHash), "\n"; // 64 64

54. Why do several AJAX requests from the same user seem to run one at a time?

PHP's default file session handler locks the session file from session_start() until the script ends or the session is closed. If a dashboard fires several AJAX calls at once, each waits for the previous request to release the lock, so one slow report makes the whole page feel frozen. Read what you need and call session_write_close() as early as possible, or open the session with read_and_close for requests that never write to it. With several servers behind a load balancer, move sessions to Redis or a database so any server can handle any request.

<?php
// Read-only request: read the data and release the lock at once
session_start(['read_and_close' => true]);
$userId = $_SESSION['user_id'] ?? null;
// slow work here no longer blocks this user's other requests

// Request that must write: keep the locked window short
session_start();
$_SESSION['last_seen'] = time();
session_write_close();

55. How do PHP-FPM and OPcache affect performance in production?

PHP-FPM keeps a pool of worker processes; each handles one request at a time and starts it with a clean state, which is why sessions, caches and queues must live outside PHP's memory. Without OPcache, every request parses and compiles each file again; OPcache keeps the compiled opcodes in shared memory and is usually the biggest single speed-up. Many teams set opcache.validate_timestamps=0 in production and reload PHP-FPM on deploy. The JIT from PHP 8.0 mainly helps CPU-heavy code, not apps waiting on MySQL. Size pm.max_children from available RAM divided by memory per worker.

56. How would you export a million rows to CSV in PHP without running out of memory?

fetchAll() builds one huge array and can hit memory_limit. Stream instead: a generator uses yield to hand back one row at a time, so the caller loops with foreach while memory stays flat. For MySQL, also turn off buffered queries for that statement (PHP 8.4 also names the option Pdo\Mysql::ATTR_USE_BUFFERED_QUERY), because by default the driver copies the whole result set into PHP memory before you read the first row. Write each row straight to php://output with fputcsv(). Note that an unbuffered result must be read to the end before the same connection can run another query.

<?php
function streamRows(PDO $pdo, string $sql): Generator {
    $stmt = $pdo->prepare($sql, [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false]);
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        yield $row;
    }
}
header('Content-Type: text/csv');
$out = fopen('php://output', 'w');
foreach (streamRows($pdo, 'SELECT id, name, email FROM candidates') as $row) {
    fputcsv($out, $row, ',', '"', '');
}

57. How do you use MySQL read replicas without showing users stale data?

Send SELECTs to one or more replicas and keep every write on the primary. Replication is asynchronous by default, so a replica can lag by seconds. The classic bug: a user saves a profile, the next page reads from a lagging replica and shows the old values. Use read-your-writes rules: after a write, read from the primary for the rest of that request, which is what Laravel's sticky option does, or for that user for a few seconds. Reads that decide a write, such as checking a balance, belong on the primary inside the transaction. Monitor lag and drop lagging replicas from rotation.

58. How would you add caching to a slow PHP and MySQL application?

MySQL 8.0 removed the old query cache, so caching is the application's job, usually with Redis or Memcached. The common pattern is cache-aside: read the cache, on a miss query MySQL and store the result with a TTL, and delete the key when the data changes. Discuss the hard parts: invalidation, stampedes when a hot key expires and hundreds of requests hit MySQL at once (use a lock or refresh early), and never storing one user's private data under a shared key. Cache computed results such as job counts per city, not every query.

<?php
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

$counts = Cache::remember('job_counts_by_city', now()->addMinutes(10), function () {
    return DB::table('jobs')
        ->where('status', 'open')
        ->groupBy('city')
        ->selectRaw('city, COUNT(*) AS total')
        ->pluck('total', 'city');
});
// After a job is posted or closed:
Cache::forget('job_counts_by_city');

59. How do you move slow work, such as sending emails, out of the request in Laravel?

Anything slow or unreliable that the user need not wait for, such as emails, PDF generation or third-party API calls, should be a queued job. Create a job class that implements ShouldQueue, dispatch it, and run workers with php artisan queue:work under Supervisor, against Redis, SQS or the database driver. Set $tries and $backoff for retries, and add a failed() method; jobs that run out of attempts land in the failed_jobs table. A job can run more than once, so make it idempotent. Run queue:restart on deploy, because workers keep old code in memory.

<?php
namespace App\Jobs;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Queue\Queueable;

class SendOfferLetter implements ShouldQueue
{
    use Queueable;
    public int $tries = 3;
    public array $backoff = [10, 60, 300];
    public function __construct(public int $candidateId) {}
    public function handle(): void { /* build the PDF and email it */ }
}
// Dispatch from a controller: SendOfferLetter::dispatch($candidate->id);

60. How should you store and calculate money in PHP and MySQL?

Never use FLOAT or DOUBLE for money. They store binary approximations, so 0.1 + 0.2 is not exactly 0.3, and rounding errors add up across invoices and GST calculations. In MySQL use DECIMAL, such as DECIMAL(12,2), which is exact. PHP floats have the same problem, so either keep amounts as integer paise and format only for display, or use bcmath functions on strings. Decide one rounding rule, such as GST per line or per invoice, and apply it in one place. Payment gateways such as Razorpay already take amounts in paise.

<?php
var_dump(0.1 + 0.2 == 0.3);     // bool(false)
printf("%.17f\n", 0.1 + 0.2);   // 0.30000000000000004
$paise = 1999 + 2999;           // Rs 19.99 + Rs 29.99 as integers
echo number_format($paise / 100, 2), "\n"; // 49.98
echo bcadd('19.99', '29.99', 2), "\n";     // 49.98