PHP Forms & Database Integration

Basic Database Connection:
<?php
// Database configuration
$host = "localhost";
$username = "root";
$password = "";
$database = "ecommerce";
// Create connection
try {
  $pdo = new PDO("mysql:host=$host;dbname=$database", $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

Secure Database Queries:
// User registration with prepared statement
$stmt = $pdo->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
$username = $_POST['username'];
$email = $_POST['email'];
$password = password_hash($_POST['password'], PASSWORD_DEFAULT);
try {
  $stmt->execute([$username, $email, $password]);
  echo "User registered successfully";
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}

Login Verification:
function authenticateUser($email, $password) {
  global $pdo;
  $stmt = $pdo->prepare("SELECT id, username, password FROM users WHERE email = ?");
  $stmt->execute([$email]);
  $user = $stmt->fetch();
  if ($user && password_verify($password, $user['password'])) {
    $_SESSION['user_id'] = $user['id'];
    $_SESSION['username'] = $user['username'];
    return true;
  }
  return false;
}

Complete Registration Process:
if ($_SERVER["REQUEST_METHOD"] == "POST") {
  $errors = validateRegistration($_POST);
  if (empty($errors)) {
    try {
      $stmt = $pdo->prepare("INSERT INTO users (username, email, password, created_at) VALUES (?, ?, ?, NOW())");
      $hashedPassword = password_hash($_POST['password'], PASSWORD_DEFAULT);
      $stmt->execute([$_POST['username'], $_POST['email'], $hashedPassword]);
      echo "Registration successful!";
    } catch(PDOException $e) {
      $errors[] = "Registration failed: " . $e->getMessage();
    }
  }
}

Create, Read, Update, Delete:
// CREATE - Insert new product
$stmt = $pdo->prepare("INSERT INTO products (name, price, description) VALUES (?, ?, ?)");
$stmt->execute([$name, $price, $description]);
// READ - Get all products
$stmt = $pdo->query("SELECT * FROM products ORDER BY name");
$products = $stmt->fetchAll();
// UPDATE - Modify product
$stmt = $pdo->prepare("UPDATE products SET price = ? WHERE id = ?");
$stmt->execute([$newPrice, $productId]);
// DELETE - Remove product
$stmt = $pdo->prepare("DELETE FROM products WHERE id = ?");
$stmt->execute([$productId]);

Database-Level Validation:
function checkEmailExists($email) {
  global $pdo;
  $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email = ?");
  $stmt->execute([$email]);
  return $stmt->fetchColumn() > 0;
}
function validateUniqueUsername($username) {
  global $pdo;
  $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE username = ?");
  $stmt->execute([$username]);
  return $stmt->fetchColumn() == 0;
}

User Session Handling:
session_start();
// Check if user is logged in
function isLoggedIn() {
  return isset($_SESSION['user_id']);
}
// Logout function
function logout() {
  session_destroy();
  header("Location: login.php");
  exit();
}
// Protect pages
if (!isLoggedIn()) {
  header("Location: login.php");
  exit();
}

Database Error Management:
try {
  $stmt = $pdo->prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)");
  $stmt->execute([$userId, $total]);
  $orderId = $pdo->lastInsertId();
  // Insert order items
  foreach ($items as $item) {
    $stmt = $pdo->prepare("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)");
    $stmt->execute([$orderId, $item['id'], $item['quantity']]);
  }
  $pdo->commit();
  echo "Order placed successfully";
} catch(PDOException $e) {
  $pdo->rollback();
  error_log("Database error: " . $e->getMessage());
  echo "Order failed. Please try again.";
}

Users Table:
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Products Table:
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock INT DEFAULT 0,
  image VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Product Listing with Search:
<?php
// Pagination
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 12;
$offset = ($page - 1) * $limit;
// Search functionality
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
// Build query
$where = "WHERE 1=1";
$params = [];
if (!empty($search)) {
  $where .= " AND (name LIKE ? OR description LIKE ?)";
  $params[] = "%$search%";
  $params[] = "%$search%";
}
// Get products
$stmt = $pdo->prepare("SELECT * FROM products $where ORDER BY created_at DESC LIMIT ? OFFSET ?");
$params[] = $limit;
$params[] = $offset;
$stmt->execute($params);
$products = $stmt->fetchAll();
?>

Image Upload Handling:
function handleImageUpload($file) {
  $uploadDir = "uploads/";
  $errors = [];
  // Check if file was uploaded
  if ($file["error"] !== UPLOAD_ERR_OK) {
    $errors[] = "File upload failed";
    return $errors;
  }
  // Validate file type
  $allowedTypes = ["image/jpeg", "image/png", "image/gif"];
  if (!in_array($file["type"], $allowedTypes)) {
    $errors[] = "Invalid file type";
  }
  // Validate file size (2MB max)
  if ($file["size"] > 2 * 1024 * 1024) {
    $errors[] = "File too large (max 2MB)";
  }
  // Generate unique filename
  $extension = pathinfo($file["name"], PATHINFO_EXTENSION);
  $filename = uniqid() . "." . $extension;
  $filepath = $uploadDir . $filename;
  // Move uploaded file
  if (!move_uploaded_file($file["tmp_name"], $filepath)) {
    $errors[] = "Failed to save file";
  }
  return $errors;
}

Cart Database Tables:
CREATE TABLE cart (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  product_id INT,
  quantity INT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  total DECIMAL(10,2),
  status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Database Indexes:
// Add indexes for better performance
CREATE INDEX idx_products_name ON products(name);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_users_email ON users(email);
// Optimized query with joins
$stmt = $pdo->prepare("SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE p.name LIKE ? ORDER BY p.created_at DESC LIMIT ? OFFSET ?");

Security Implementation:
// Secure password hashing
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);
// Input sanitization
$username = htmlspecialchars(trim($_POST['username']));
$email = filter_var(trim($_POST['email']), FILTER_SANITIZE_EMAIL);
// CSRF protection
if (!isset($_POST['csrf_token']) || $_POST['csrf_token'] !== $_SESSION['csrf_token']) {
  die('CSRF token validation failed');
}

Custom Error Handler:
function customErrorHandler($errno, $errstr, $errfile, $errline) {
  $errorMessage = date('Y-m-d H:i:s') . " Error: [$errno] $errstr in $errfile on line $errline\n";
  error_log($errorMessage, 3, "logs/error.log");
  return true;
}
set_error_handler("customErrorHandler");
// Database error handling
try {
  $stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (?, ?)");
  $stmt->execute([$name, $price]);
} catch(PDOException $e) {
  error_log("Database error: " . $e->getMessage());
  echo "An error occurred. Please try again.";
}

Debug Functions:
// Debug function
function debug($data) {
  echo "<pre>";
  print_r($data);
  echo "</pre>";
}
// Test database connection
function testConnection() {
  global $pdo;
  try {
    $stmt = $pdo->query("SELECT 1");
    echo "Database connection successful";
  } catch(PDOException $e) {
    echo "Database connection failed: " . $e->getMessage();
  }
}
// Validate form data
function validateProductData($data) {
  $errors = [];
  if (empty($data['name'])) $errors[] = "Product name is required";
  if (!is_numeric($data['price']) || $data['price'] <= 0) $errors[] = "Valid price is required";
  return $errors;
}