Master PHP & SQL Integration in 2025

Comprehensive tutorials, examples, and projects to become an expert in database-driven web development

PHP Programming Basics

PHP Syntax & Variables

Learn the fundamentals of PHP syntax and variable handling

  • PHP tags and output statements
  • Variable declaration and data types
  • Constants and magic constants
  • Variable scope and superglobals
Beginner
// Basic PHP syntax example
<?php
// Variable declaration
$message = "Hello, PHP World!";
$number = 42;
$pi = 3.14159;
$isTrue = true;

// Outputting variables
echo $message; // Output: Hello, PHP World!
print $number; // Output: 42
<?= "Short syntax: $pi" ?> // Output: Short syntax: 3.14159
?>

Control Structures

Conditional statements and loops in PHP

  • If, else, and elseif statements
  • Switch statements
  • While and do-while loops
  • For and foreach loops
Beginner
<?php
// If-else statement
$age = 25;
if ($age >= 18) {
  echo "You are an adult";
} else {
  echo "You are a minor";
}

// Foreach loop with arrays
$colors = ["red", "green", "blue"];
foreach ($colors as $color) {
  echo "Color: $color<br>";
}
?>

Functions & Forms

Creating functions and handling form data

  • Function declaration and parameters
  • Return values and type declarations
  • Form handling with GET and POST
  • Form validation techniques
Intermediate
<?php
// Function with type declaration
function calculateArea(float $width, float $height): float {
  return $width * $height;
}

// Using the function
$area = calculateArea(5.5, 10);
echo "Area: $area"; // Output: Area: 55

// Form handling example
if ($_SERVER["REQUEST_METHOD"] == "POST") {
  $name = htmlspecialchars($_POST['name']);
  echo "Hello, $name!";
}
?>

SQL Database Fundamentals

Database Design

Principles of effective database design

  • Relational database concepts
  • Normalization forms
  • Table relationships (1:1, 1:M, M:M)
  • Primary and foreign keys
Beginner
-- Creating a well-designed users table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Creating a posts table with foreign key
CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

SQL Queries

Essential SQL query techniques

  • SELECT statements with filtering
  • JOIN operations
  • Aggregate functions
  • Subqueries and derived tables
Intermediate
-- Basic SELECT with WHERE clause
SELECT id, username, email
FROM users
WHERE created_at >= '2025-01-01'
ORDER BY username ASC;

-- JOIN example with users and posts
SELECT
  u.username,
  p.title,
  p.created_at
FROM posts p
INNER JOIN users u ON p.user_id = u.id
ORDER BY p.created_at DESC;

-- Aggregate function with GROUP BY
SELECT
  u.username,
  COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id
HAVING post_count > 5
ORDER BY post_count DESC;

Advanced SQL Concepts

Advanced database techniques and optimization

  • Stored procedures and functions
  • Triggers and events
  • Indexing for performance
  • Transaction management
Advanced
-- Creating a stored procedure
DELIMITER //
CREATE PROCEDURE GetUserPosts(IN user_id INT)
BEGIN
  SELECT title, content, created_at
  FROM posts
  WHERE user_id = user_id
  ORDER BY created_at DESC;
END //
DELIMITER ;

-- Creating a trigger for data integrity
DELIMITER //
CREATE TRIGGER before_post_update
  BEFORE UPDATE ON posts
  FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- Transaction example
START TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (123, 99.99);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 456, 2);
COMMIT;

PHP & SQL Integration

MySQLi
PDO
Security

MySQLi Connection

<?php
// MySQLi procedural connection
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

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

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

// Close connection (optional, as PHP automatically closes at end of script)
mysqli_close($conn);
?>

MySQLi Query Execution

<?php
// Assuming $conn is established
$sql = "SELECT id, username, email FROM users WHERE active = 1";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
  // Output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "ID: " . $row["id"] . " - Name: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
  }
} else {
  echo "0 results";
}

// Free result set
mysqli_free_result($result);
?>

PDO Connection

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // Set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

PDO Prepared Statements

<?php
// Prepare and execute with named parameters
$stmt = $conn->prepare("INSERT INTO users (username, email, password_hash) VALUES (:username, :email, :password)");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':password', $password_hash);

// Insert a row
$username = "john_doe";
$email = "john@example.com";
$password_hash = password_hash("secure_password", PASSWORD_DEFAULT);
$stmt->execute();

// Insert another row
$username = "jane_smith";
$email = "jane@example.com";
$password_hash = password_hash("another_password", PASSWORD_DEFAULT);
$stmt->execute();

echo "New records created successfully";
?>

SQL Injection Prevention

<?php
// UNSAFE - vulnerable to SQL injection
$user_id = $_GET['id']; // Could be "1; DROP TABLE users;"
$sql = "SELECT * FROM users WHERE id = $user_id";
// Result: SELECT * FROM users WHERE id = 1; DROP TABLE users;

// SAFE - using prepared statements with MySQLi
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id); // "i" for integer
$user_id = $_GET['id'];
$stmt->execute();
$result = $stmt->get_result();

// SAFE - using PDO prepared statements
$stmt = $conn->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindParam(':id', $user_id, PDO::PARAM_INT);
$user_id = $_GET['id'];
$stmt->execute();
?>

Password Security

<?php
// Hashing a password for storage
$password = "user_password_123";
$hash = password_hash($password, PASSWORD_DEFAULT);
// Store $hash in database

// Verifying a password
$user_input = "user_password_123";
$stored_hash = "$2y$10$Bhcy2... (from database)"; // Retrieved from database

if (password_verify($user_input, $stored_hash)) {
  // Password is correct
  echo "Login successful!";
} else {
  // Password is incorrect
  echo "Invalid password!";
}

// Rehashing if necessary (if algorithm cost has changed)
if (password_needs_rehash($stored_hash, PASSWORD_DEFAULT)) {
  $new_hash = password_hash($user_input, PASSWORD_DEFAULT);
  // Update database with $new_hash
}
?>

Real World Projects

User Management System

A complete CRUD application for managing users

  • User registration and login
  • Profile management
  • Admin dashboard
  • Password reset functionality
Intermediate
<?php
// Database configuration
define('DB_HOST', 'localhost');
define('DB_NAME', 'user_management');
define('DB_USER', 'root');
define('DB_PASS', '');

// User class for handling user operations
class User {
  private $db;

  public function __construct() {
    $this->db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
    $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }

  public function register($username, $email, $password) {
    $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
    $stmt = $this->db->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
    return $stmt->execute([$username, $email, $hashedPassword]);
  }
}
?>

Blog System

A complete blogging platform with categories and comments

  • Post creation and editing
  • Category management
  • Comment system
  • Search functionality
Intermediate
<?php
// Blog class for handling blog operations
class Blog {
  private $db;

  public function __construct() {
    $this->db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
    $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }

  public function createPost($title, $content, $author_id, $category_id) {
    $stmt = $this->db->prepare("INSERT INTO posts (title, content, author_id, category_id, created_at) VALUES (?, ?, ?, ?, NOW())");
    return $stmt->execute([$title, $content, $author_id, $category_id]);
  }

  public function getRecentPosts($limit = 10) {
    $stmt = $this->db->prepare("SELECT p.*, u.username, c.name as category_name FROM posts p JOIN users u ON p.author_id = u.id JOIN categories c ON p.category_id = c.id ORDER BY p.created_at DESC LIMIT ?");
    $stmt->bindValue(1, $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
  }
}
?>

E-commerce Shopping Cart

An online store with product catalog and shopping cart

  • Product catalog with categories
  • Shopping cart functionality
  • Order processing system
  • Payment integration (simulated)
Advanced
<?php
// ShoppingCart class for handling cart operations
class ShoppingCart {
  private $db;
  private $cart_id;

  public function __construct() {
    $this->db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
    $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $this->initCart();
  }

  private function initCart() {
    if (!isset($_SESSION['cart_id'])) {
      $stmt = $this->db->prepare("INSERT INTO carts (created_at) VALUES (NOW())");
      $stmt->execute();
      $_SESSION['cart_id'] = $this->db->lastInsertId();
    }
    $this->cart_id = $_SESSION['cart_id'];
  }

  public function addToCart($product_id, $quantity = 1) {
    // Check if product already in cart
    $stmt = $this->db->prepare("SELECT * FROM cart_items WHERE cart_id = ? AND product_id = ?");
    $stmt->execute([$this->cart_id, $product_id]);
    $existing = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($existing) {
      // Update quantity
      $new_quantity = $existing['quantity'] + $quantity;
      $stmt = $this->db->prepare("UPDATE cart_items SET quantity = ? WHERE id = ?");
      $stmt->execute([$new_quantity, $existing['id']]);
    } else {
      // Add new item
      $stmt = $this->db->prepare("INSERT INTO cart_items (cart_id, product_id, quantity) VALUES (?, ?, ?)");
      $stmt->execute([$this->cart_id, $product_id, $quantity]);
    }
    return true;
  }
}
?>