10.3 PHP with MySQL

sudo service php-fpm start
sudo systemctl enable php-fpm
sudo service nginx restart        # or: sudo service httpd restart
php -v

sudo mkdir -p /var/www/phpapp
sudo tee /etc/nginx/conf.d/phpapp.conf > /dev/null <<'EOF'
server {
    listen 80;
    server_name YOUR_SERVER_NAME;
    root /var/www/phpapp;
    index index.php index.html;

    location / {
        try_files $uri $uri/ /index.php?$args;
    }
    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_pass unix:/run/php-fpm/www.sock;
        fastcgi_index index.php;
        include fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
    }
}
EOF
sudo sed -i "s/YOUR_SERVER_NAME/$(curl -s https://checkip.amazonaws.com)/" /etc/nginx/conf.d/phpapp.conf
sudo nginx -t && sudo service nginx reload

ls /run/php/                      # note the socket name, e.g. php8.3-fpm.sock (24.04) or php8.1-fpm.sock (22.04)
sudo mkdir -p /var/www/phpapp
sudo tee /etc/nginx/sites-available/phpapp > /dev/null <<'EOF'
server {
    listen 80 default_server;
    listen [::]:80 default_server;
    server_name _;
    root /var/www/phpapp;
    index index.php index.html;

    location / {
        try_files $uri $uri/ /index.php?$args;
    }
    location ~ \.php$ {
        include snippets/fastcgi-php.conf;
        fastcgi_pass unix:/run/php/php-fpm.sock;
    }
}
EOF
sudo ln -sf /etc/nginx/sites-available/phpapp /etc/nginx/sites-enabled/phpapp
sudo rm -f /etc/nginx/sites-enabled/default /etc/nginx/sites-enabled/mysite
sudo nginx -t && sudo service nginx reload

sudo tee /var/www/phpapp/db.php > /dev/null <<'EOF'
<?php
$dsn  = "mysql:host=127.0.0.1;dbname=appdb;charset=utf8mb4";
$user = "appuser";
$pass = "App@Pass123";
try {
    $pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
} catch (PDOException $e) {
    http_response_code(500);
    die("Database connection failed: " . htmlspecialchars($e->getMessage()));
}
EOF

sudo tee /var/www/phpapp/index.php > /dev/null <<'EOF'
<?php
require __DIR__ . "/db.php";
if ($_SERVER["REQUEST_METHOD"] === "POST" && !empty($_POST["name"]) && !empty($_POST["city"])) {
    $stmt = $pdo->prepare("INSERT INTO students (name, city) VALUES (?, ?)");   // prepared = safe from SQL injection
    $stmt->execute([$_POST["name"], $_POST["city"]]);
    header("Location: /");
    exit;
}
$rows = $pdo->query("SELECT id, name, city FROM students ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>PHP + MySQL on EC2</title></head>
<body style="font-family:Arial;max-width:700px;margin:30px auto">
<h1>Students (PHP + MySQL)</h1>
<table border="1" cellpadding="6">
<tr><th>ID</th><th>Name</th><th>City</th></tr>
<?php foreach ($rows as $r): ?>
<tr><td><?= $r["id"] ?></td><td><?= htmlspecialchars($r["name"]) ?></td><td><?= htmlspecialchars($r["city"]) ?></td></tr>
<?php endforeach; ?>
</table>
<h3>Add student</h3>
<form method="post">
  <input name="name" placeholder="Name" required>
  <input name="city" placeholder="City" required>
  <button type="submit">Add</button>
</form>
<p>Served by PHP <?= phpversion() ?> on <?= gethostname() ?></p>
</body></html>
EOF

sudo chmod 640 /var/www/phpapp/db.php
# let the PHP process user read db.php:  apache (AL2023/CentOS php-fpm & httpd) or www-data (Ubuntu)
sudo chown root:apache /var/www/phpapp/db.php      # Ubuntu: sudo chown root:www-data /var/www/phpapp/db.php
sudo restorecon -Rv /var/www/phpapp 2>/dev/null   # CentOS only (harmless elsewhere)
curl -s http://localhost/ -H "Host: $(curl -s https://checkip.amazonaws.com)" | head -20