CT214H – Web Programming
Practical Session 5 – MySQL & PHP
Content
❖ PHP - MySQL: continue add features
❖ Prerequisites: XAMPP installed. Your starting point should be from the
Techblog web page in the previous practical session.
❖ Objective:
• Connect to database
• Query data and display on your page
❖ Requirement:
• Step-by-step do the tasks below and observe the results
1. Display popular articles
In your [Link], display most viewed articles in sidebar
- In the popular articles section (inside your side bar): query the 5 most popular articles
ordered by views
$popular_sql = "SELECT id, title, views FROM articles ORDER BY views DESC LIMIT 5";
$popular_result = $conn->query($popular_sql);
- Change the html to display the query results
<div class="widget popular">
<h3>Popular Articles</h3>
<ul class="popular-list">
<?php while($pop = $popular_result->fetch_assoc()): ?>
<li>
<a href="[Link]?id=<?php echo $pop['id']; ?>">
<?php echo $pop['title']; ?> </a>
</li>
<?php endwhile; ?>
</ul>
</div>
2. Pagination
a. Adding more sample data into your articles table: [Link]
b. In your [Link], display 6 articles per page with next/previous buttons
- Inside of your article grid section, changing the SQL to query only articles for a page
// Include database connection
include '[Link]';
// Pagination settings
$articles_per_page = 6;
1
// Get current page number from URL, default to 1
$current_page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$current_page = max(1, $current_page); //Ensure page is at least 1
// Calculate offset for SQL query
$offset = ($current_page - 1) * $articles_per_page;
// Count total articles
$count_sql = "SELECT COUNT(*) as total FROM articles";
$count_result = mysqli_query($conn, $count_sql);
$count_row = mysqli_fetch_assoc($count_result);
$total_articles = $count_row['total'];
// Calculate total pages
$total_pages = ceil($total_articles / $articles_per_page);
// Query to get all articles
$sql ="SELECT * FROM articles LIMIT $articles_per_page OFFSET $offset";
$result = mysqli_query($conn, $sql);
- Keep the loop to display the article grids unchanged.
- Below the article grid section, add another section to control pagination
<!-- Pagination Controls -->
<div class="pagination">
<!-- Previous Button -->
<?php
if ($total_pages > 1){
if ($current_page > 1){ ?>
<a href="?page=<?php echo $current_page - 1; ?>" class="pagination-btn prev-btn">← Previous</a>
<?php }else{ ?>
<span class="pagination-btn prev-btn disabled">← Previous</span>
<?php } ?>
<!-- Page Numbers -->
<div class="page-numbers">
<?php
// Show page numbers
for ($i = 1; $i <= $total_pages; $i++) {
if ($i == $current_page) {
echo "<span class='page-number active'>$i</span>";
} else {
echo "<a href='?page=$i' class='page-number'>$i</a>";
}
}?>
</div>
2
<!-- Next Button -->
<?php
if ($current_page < $total_pages){ ?>
<a href="?page=<?php echo $current_page + 1; ?>" class="pagination-btn next-btn">Next →</a>
<?php }else{ ?>
<span class="pagination-btn next-btn disabled">
Next →</span>
<?php
}
}?>
</div>
c. Styling the pagination section: [Link]
d. Changing your [Link] to enable pagination (Optional)
- Inside of your [Link] page, add the pagination setting as above to calculate
variables such as: current_page, total_pages offset, etc.
- Change the SQL to query only articles for the current page
$sql = "SELECT * FROM articles WHERE category = '{$category['name']}' LIMIT $articles_per_page
OFFSET $offset";
- Below the article grid section, add the pagination controls as previous example
3. Related articles
Show 3 related articles (same category) in your [Link]
- Before the closing main tag in your page, add a section for related articles
<section class="related-articles-section">
<h3> Related Articles</h3>
<div class="related-articles-grid">
<?php
// Get 3 related articles from same category, excluding current article
$related_sql = "SELECT id, title, image_url, excerpt, author, created_at, views FROM articles WHERE
category = '{$article['category']}'
AND id != $article_id ORDER BY created_at DESC LIMIT 3";
$related_result = mysqli_query($conn, $related_sql);
if (mysqli_num_rows($related_result) > 0) {
while($related = mysqli_fetch_assoc($related_result)) { ?>
<article class="related-card">
<img src="<?php echo htmlspecialchars($related['image_url']); ?>"
alt="<?php echo htmlspecialchars($related['title']); ?>"
class="related-image">
<div class="related-content">
<h4 class="related-title">
<?php echo htmlspecialchars($related['title']); ?>
</h4>
3
<p class="related-excerpt">
<?php echo substr(htmlspecialchars($related['excerpt']), 0, 100) . '...'; ?>
</p>
<div class="related-meta">
<span>By <?php echo htmlspecialchars($related['author']); ?></span>
<span class="date"><?php echo $related['created_at']; ?> </span>
</div>
<a href="[Link]?id=<?php echo $related['id']; ?>" class="read-more">Read More →</a>
<p class="view-count" style="color: #999; font-size: 0.9rem; margin-top: 10px;">
<span class="views"> <?php echo $related['views']; ?> </span> views
</p>
</div>
</article>
<?php
}
} else {
echo '<p class="no-related">No related articles found.</p>';
}
?>
</div>
</section>
- Update your [Link] file, add these styles at the end of the file: [Link]
4. Add new article form
Create add article form page
a. Make sure you have a folder name ‘images’ in your project directory
On Linux/Mac:
- mkdir images
- chmod 755 images
On Windows: Just create the folder normally.
Inside of the navigation bar in [Link], add an item to add new article
<li><a href="[Link]">Add New Article</a></li>
b. Create [Link]
- Copy the [Link], name as [Link]
- Change the main container (inside main tag) to contain uploading form
<main class="upload-form-container"> </main>
4
- Inside the main container create a div upload card
<div class="upload-form-card"></div>
- Inside the div, add the following content:
+ An h1 for adding new article
<h1 class="upload-form-title"> Add New Article</h1>
+ Announcement for sucess message
<?php if ($success): ?>
<div class="upload-message upload-message-success">
✓ Article published successfully!
<a href="[Link]?id=<?php echo $new_article_id; ?>">View Article</a>
or
<a href="[Link]">Go to Home</a>
</div>
<?php endif; ?>
+ Announcement for error message
<?php if ($error): ?>
<div class="upload-message upload-message-error">
✗ <?php echo htmlspecialchars($error); ?>
</div>
<?php endif; ?>
+ An form for article submission
<form method="POST" action="[Link]" enctype="multipart/form-data" id="articleForm">
</form>
+ Inside the form submission, add the following content
→ A section for upload image
<div class="upload-form-group">
<label>Article Image <span class="required">*</span></label>
<div class="image-upload-area" id="uploadArea">
<div class="upload-icon">🖼️</div>
<p class="upload-text">Click to upload or drag and drop</p>
<p class="upload-hint">JPG, PNG, GIF, WEBP (Max 5MB)</p>
<input type="file"
name="article_image"
id="articleImage"
class="file-input-hidden"
accept="image/jpeg,image/png,image/gif,image/webp"
required>
</div>
5
<!-- Image Preview -->
<div class="image-preview-container" id="imagePreviewContainer">
<img src="" alt="Preview" class="image-preview" id="imagePreview">
<div class="image-info" id="imageInfo"></div>
<button type="button" class="remove-image-btn" id="removeImageBtn">Remove Image</button>
</div>
</div>
→ A section for adding title
<div class="upload-form-group">
<label for="title">
Article Title <span class="required">*</span>
</label>
<input type="text" id="title" name="title" class="upload-form-input" placeholder="e.g., Getting
Started with Machine Learning"
required value="<?php echo isset($_POST['title']) ? htmlspecialchars($_POST['title']) : ''; ?>">
<span class="upload-input-hint">Choose a clear, descriptive title</span>
</div>
→ A section for selecting category loaded from database
<div class="upload-form-group">
<label for="category">
Category <span class="required">*</span>
</label>
<select id="category" name="category" class="upload-form-select" required>
<option value="">-- Select Category --</option>
<?php
// Fetch categories from database
$cat_sql = "SELECT id, name FROM categories ORDER BY name ASC";
$cat_result = mysqli_query($conn, $cat_sql);
// Check if categories exist
if (mysqli_num_rows($cat_result) > 0) {
while ($cat = mysqli_fetch_assoc($cat_result)) {
$selected = (isset($_POST['category']) && $_POST['category'] == $cat['name']) ? 'selected' : '';
echo '<option value="' . htmlspecialchars($cat['name']) . '" ' . $selected . '>'
. htmlspecialchars($cat['name']) . '</option>';
}
6
} else {
echo '<option value="" disabled>No categories available</option>';
}
?>
</select>
<span class="upload-input-hint">Select the category that best fits your article</span>
</div>
→ A section for adding author
<div class="upload-form-group">
<label for="author">
Author Name <span class="required">*</span>
</label>
<input type="text" id="author" name="author" class="upload-form-input" placeholder="e.g., John
Doe"required
value="<?php echo isset($_POST['author']) ? htmlspecialchars($_POST['author']) : ''; ?>">
</div>
→ A section for adding published date
→ A section for adding excerpt
<div class="upload-form-group">
<label for="created_at">
Published Date <span class="required">*</span>
</label>
<input type="date" id="created_at" name="created_at"
class="upload-form-input" required
value="<?php echo isset($_POST['created_at']) ? $_POST['created_at'] : date('Y-m-d'); ?>">
</div>
<div class="upload-form-group">
<label for="excerpt">
Article Excerpt <span class="required">*</span>
</label>
<textarea id="excerpt" name="excerpt" class="upload-form-textarea"
placeholder="Write a brief summary (2-3 sentences)"
required><?php echo isset($_POST['excerpt']) ? htmlspecialchars($_POST['excerpt']) : '';
?></textarea>
<span class="upload-input-hint">This will appear on the article cards</span>
</div>
7
→ A section for adding article content
<div class="upload-form-group">
<label for="content">
Full Article Content <span class="required">*</span>
</label>
<textarea id="content" name="content" class="upload-form-textarea"
style="min-height: 300px;"
placeholder="Write your full article content here..."
required><?php echo isset($_POST['content']) ? htmlspecialchars($_POST['content']) : '';
?></textarea>
<span class="upload-input-hint">Write your complete article here</span>
</div>
→ A section for submission button
<div class="upload-form-buttons">
<button type="submit" class="upload-btn-submit" id="submitBtn">
Publish Article
</button>
<a href="[Link]" class="upload-btn-cancel">
Cancel
</a>
</div>
- In the beginning of your page, connect to database to store new article
require_once '[Link]';
// Initialize variables
$success = false;
$error = '';
$new_article_id = 0;
// Process form submission
if ($_SERVER['REQUEST_METHOD'] == 'POST') {…}
- Inside of the form submission processing, get form data, validate, and insert into the
articles table
+ Get form data
// Get and sanitize form data
$title = mysqli_real_escape_string($conn, trim($_POST['title']));
$excerpt = mysqli_real_escape_string($conn, trim($_POST['excerpt']));
$content = mysqli_real_escape_string($conn, trim($_POST['content']));
$category = mysqli_real_escape_string($conn, trim($_POST['category']));
$author = mysqli_real_escape_string($conn, trim($_POST['author']));
$created_at = mysqli_real_escape_string($conn, $_POST['created_at']);
8
+ Handle image
$image_url = '';
$upload_success = false;
if (isset($_FILES['article_image']) && $_FILES['article_image']['error'] == 0) {
$file = $_FILES['article_image'];
// Get file info
$file_name = $file['name'];
$file_tmp = $file['tmp_name'];
$file_size = $file['size'];
$file_error = $file['error'];
// Get file extension
$file_ext = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
// Allowed extensions
$allowed_extensions = array('jpg', 'jpeg', 'png', 'gif', 'webp');
// Check if extension is allowed
if (in_array($file_ext, $allowed_extensions)) {
// Check file size (max 5MB)
if ($file_size <= 5242880) {
// Generate unique filename
$new_filename = 'article_' . uniqid() . '.' . $file_ext;
// Upload directory
$upload_dir = 'images/';
$upload_path = $upload_dir . $new_filename;
// Move uploaded file
if (move_uploaded_file($file_tmp, $upload_path)) {
$image_url = $upload_path;
$upload_success = true;
} else {
$error = "Failed to upload image. Please check folder permissions.";
}
} else {
$error = "File size too large. Maximum size is 5MB.";
}
} else {
$error = "Invalid type. Allowed: JPG, JPEG, PNG, GIF, WEBP.";
}
} else {
$error = "Please select an image to upload.";
}
+ Validate required fields
if (empty($title) || empty($excerpt) || empty($content) || empty($category) || empty($author) ||
empty($created_at)) {
$error = "Please fill in all required fields.";
} elseif ($upload_success) {
+ Insert into articles table
9
// Insert into database
$sql = "INSERT INTO articles (title, excerpt, content, category, author, created_at, image_url)
VALUES ('$title', '$excerpt', '$content', '$category', '$author', '$created_at', '$image_url')";
if (mysqli_query($conn, $sql)) {
$success = true;
$new_article_id = mysqli_insert_id($conn);
} else {
$error = "Database Error: " . mysqli_error($conn);
// Delete uploaded image if database insert fails
if (file_exists($image_url)) {
unlink($image_url);
}
}
}
c. Add a JavaScript file to handle image upload in your php: [Link]
d. Link to style sheet to style your page: [Link]
5. Update an article
a. Inside of the [Link] adding a link to edit the article
Add this near the end of article content, before closing </div>, using the same style as
the Back to articles button has.
<a href="[Link]?id=<?php echo $article_id; ?>" class="back-to-articles">✏️ Edit
Article</a>
b. Create [Link]
Same as previous exercise, you only need to change few things to edit an article:
- Copy the [Link], name as [Link]
- Change the main container (inside main tag) to contain edit form: main-edit-form
- In the beginning of your page, connect to database to update article: [Link]
c. Add a JavaScript file to handle image upload in your php: [Link]
d. Link to style sheet to style your page: [Link]
6. Delete an article
a. Inside of the [Link] adding a link to delete the article
Add this near the end of article content, before closing </div>, using the same style as
the Back to articles button has.
10
<a href="[Link]?id=<?php echo $article_id; ?>" class="back-to-articles"
onclick="return confirm('Are you sure you want to delete this article? This action cannot be
undone!');">
Delete Article
</a>
b. Create a [Link] to handle the database deletion
This need to delete the article in database and also delete the image in your file system.
c. Showing success message in your [Link]
Add this right after <body> or before articles
if (isset($_GET['deleted']) && $_GET['deleted'] == 'success') {
echo "<script>alert('Article deleted successfully!');</script>";
}
At this moment, you should have a basic development of your website!
11