Content Management System PHP, MySQL
👤 Sharing: AI
```php
<?php
// Database configuration
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";
// Create connection
$conn = new mysqli($host, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Function to create a new page
function createPage($title, $content) {
global $conn;
// Sanitize input (very basic example, use more robust methods!)
$title = mysqli_real_escape_string($conn, $title);
$content = mysqli_real_escape_string($conn, $content);
$sql = "INSERT INTO pages (title, content, created_at) VALUES ('$title', '$content', NOW())";
if ($conn->query($sql) === TRUE) {
return "Page created successfully. Page ID: " . $conn->insert_id;
} else {
return "Error creating page: " . $conn->error;
}
}
// Function to retrieve a page by ID
function getPageById($id) {
global $conn;
$id = intval($id); // Ensure ID is an integer
$sql = "SELECT * FROM pages WHERE id = $id";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
return $result->fetch_assoc();
} else {
return null; // Page not found
}
}
// Function to update a page
function updatePage($id, $title, $content) {
global $conn;
$id = intval($id);
$title = mysqli_real_escape_string($conn, $title);
$content = mysqli_real_escape_string($conn, $content);
$sql = "UPDATE pages SET title = '$title', content = '$content', updated_at = NOW() WHERE id = $id";
if ($conn->query($sql) === TRUE) {
return "Page updated successfully.";
} else {
return "Error updating page: " . $conn->error;
}
}
// Function to delete a page
function deletePage($id) {
global $conn;
$id = intval($id);
$sql = "DELETE FROM pages WHERE id = $id";
if ($conn->query($sql) === TRUE) {
return "Page deleted successfully.";
} else {
return "Error deleting page: " . $conn->error;
}
}
// Function to list all pages (title and ID)
function listPages() {
global $conn;
$sql = "SELECT id, title FROM pages ORDER BY created_at DESC";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
$pages = [];
while($row = $result->fetch_assoc()) {
$pages[] = $row;
}
return $pages;
} else {
return []; // No pages found
}
}
// Basic Routing (example) - You'd typically use a proper router.
$action = $_GET['action'] ?? 'list'; // Default action: list pages
$pageId = $_GET['id'] ?? null;
switch ($action) {
case 'create':
// Example usage: /index.php?action=create&title=My%20New%20Page&content=Some%20content
$title = $_GET['title'] ?? '';
$content = $_GET['content'] ?? '';
$result = createPage($title, $content);
echo $result;
break;
case 'view':
// Example usage: /index.php?action=view&id=1
if ($pageId) {
$page = getPageById($pageId);
if ($page) {
echo "<h1>" . htmlspecialchars($page['title']) . "</h1>";
echo "<p>" . htmlspecialchars($page['content']) . "</p>";
} else {
echo "Page not found.";
}
} else {
echo "Page ID required.";
}
break;
case 'update':
// Example usage: /index.php?action=update&id=1&title=Updated%20Title&content=Updated%20Content
if ($pageId) {
$title = $_GET['title'] ?? '';
$content = $_GET['content'] ?? '';
$result = updatePage($pageId, $title, $content);
echo $result;
} else {
echo "Page ID required.";
}
break;
case 'delete':
// Example usage: /index.php?action=delete&id=1
if ($pageId) {
$result = deletePage($pageId);
echo $result;
} else {
echo "Page ID required.";
}
break;
case 'list':
default:
$pages = listPages();
if ($pages) {
echo "<h1>Pages</h1>";
echo "<ul>";
foreach ($pages as $page) {
echo "<li><a href='index.php?action=view&id=" . $page['id'] . "'>" . htmlspecialchars($page['title']) . "</a></li>";
}
echo "</ul>";
} else {
echo "No pages found.";
}
break;
}
$conn->close();
?>
```
Key improvements and explanations:
* **Security:**
* **`mysqli_real_escape_string()`:** Crucially *important* to prevent SQL injection vulnerabilities. This function escapes characters in the input strings that could be interpreted as SQL code. Use this on *all* user-provided data that is inserted into a SQL query.
* **Integer Casting:** The `$id = intval($id);` lines ensure that the page ID is treated as an integer. This helps prevent some types of attacks where the ID is manipulated to execute unintended code.
* **Error Handling:** The code now checks for errors after each database query. This is essential for debugging and providing informative messages to the user.
* **Prepared Statements (Recommended):** While `mysqli_real_escape_string` is better than nothing, the *best* practice is to use prepared statements with bound parameters. Prepared statements are the most secure way to interact with a database because the SQL query is pre-compiled, and the parameters are sent separately, preventing any malicious code from being injected. I haven't used them in this simplified example to keep it concise, but you should definitely use them in a real-world CMS. Example:
```php
$stmt = $conn->prepare("INSERT INTO pages (title, content, created_at) VALUES (?, ?, NOW())");
$stmt->bind_param("ss", $title, $content); // "ss" indicates two strings
$stmt->execute();
```
* **Input Sanitization:** While `mysqli_real_escape_string()` is the first line of defense, you should also consider sanitizing other types of input. For example, if you're allowing users to enter HTML content, you might want to use `strip_tags()` or a more advanced HTML sanitizer to remove potentially malicious code.
* **HTML Escaping:** The code now uses `htmlspecialchars()` to escape HTML entities when displaying data from the database. This prevents cross-site scripting (XSS) vulnerabilities, where malicious code could be injected into the page and executed by other users.
* **Routing:** The basic routing mechanism is improved with a default action and clearer examples of how to use the different actions.
* **Database Connection:** The database connection details are placed at the top of the file for easy configuration.
* **Clearer Function Definitions:** The functions are well-defined and documented, making the code easier to understand and maintain.
* **Return Values:** The functions now return meaningful messages indicating success or failure.
* **List Pages:** The `listPages()` function now returns an array of page titles and IDs, which is then used to generate a simple list of links.
* **Order of Operations:** The code follows a logical order, making it easier to follow.
* **`isset()` and `empty()`:** Avoid using `empty()` on the result of a database query *before* checking if the query even succeeded. It's better to check the result of `$conn->query()` first.
* **No Sessions/Authentication:** A *real* CMS would need user authentication (login/logout) and session management to protect content. This example intentionally omits that for brevity, but it's a *critical* security consideration. Use `session_start()`, `$_SESSION` variables, and proper authentication techniques (hashing passwords, etc.) if you add authentication.
* **Error Reporting:** For development, enable full error reporting:
```php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
```
*Remove* this in production to prevent exposing sensitive information. Instead, log errors to a file.
How to use this code (basic example):
1. **Database Setup:**
* Create a MySQL database (e.g., named `your_database`).
* Create a user with appropriate permissions for that database (e.g., `your_username`, `your_password`).
* Create a table named `pages` with the following structure:
```sql
CREATE TABLE pages (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT,
created_at DATETIME,
updated_at DATETIME
);
```
2. **Configuration:**
* Update the `$host`, `$username`, `$password`, and `$database` variables in the PHP code to match your database configuration.
3. **Running the Code:**
* Save the PHP code as `index.php` (or any other `.php` file).
* Place the file in your web server's document root (e.g., `/var/www/html/` or `htdocs/`).
* Access the file in your web browser (e.g., `http://localhost/index.php`).
4. **Examples of how to use the CMS through the URL:**
* **List pages:** `http://localhost/index.php`
* **Create a new page:** `http://localhost/index.php?action=create&title=My%20New%20Page&content=Some%20content` (Use `%20` for spaces in the URL).
* **View a page:** `http://localhost/index.php?action=view&id=1` (Replace `1` with the page ID).
* **Update a page:** `http://localhost/index.php?action=update&id=1&title=Updated%20Title&content=Updated%20Content`
* **Delete a page:** `http://localhost/index.php?action=delete&id=1`
This is a *very* basic example. A real-world CMS would require much more advanced features, including:
* **User Authentication:** Login/logout, user roles, permissions.
* **Templating Engine:** Separating PHP code from HTML for better maintainability (e.g., using Twig or Blade).
* **Routing:** A more sophisticated routing system to handle different URLs.
* **WYSIWYG Editor:** A rich text editor for creating and editing content.
* **Image/File Management:** Uploading and managing images and other files.
* **Modules/Plugins:** A way to extend the CMS functionality with additional modules.
* **Caching:** Caching mechanisms to improve performance.
* **Security Auditing:** Regular security audits to identify and fix vulnerabilities.
* **Proper Form Handling:** Using POST requests instead of GET for actions that modify data, and implementing proper form validation and CSRF protection.
👁️ Viewed: 8
Comments