Displaying MySQL Database Posts in a Custom Pattern

In this guide, we will walk you through the process of displaying MySQL database posts in a custom pattern. This will include fetching posts from your MySQL database, displaying them in a unique layout, fetching and displaying featured images and categories, and tracking and displaying post views.

Step 1: Fetching MySQL Database Posts

First, you need to connect to your MySQL database and fetch the posts. Here’s how you can do it:

<?php
$servername = "localhost";
$username = "database_username";
$password = "database_password";
$dbname = "database_name";

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

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch the posts
$sql = "SELECT * FROM posts LIMIT 50"; // Adjust your query as needed
$result = $conn->query($sql);

$posts = array();
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $posts[] = $row;
    }
} else {
    echo "No posts found.";
}
$conn->close();
?>
        

Step 2: Displaying Posts in a Custom Layout

Next, we display the posts in a custom layout. The goal is to show 4 medium-sized posts, 4 small-sized posts, and 1 large post, repeating this pattern:

<?php if (!empty($posts)): ?>
    <?php $total_posts = count($posts); ?>
    <?php for ($i = 0; $i < $total_posts; $i += 9): ?>
        <div class="row">
            <!-- Medium posts -->
            <?php for ($j = 0; $j < 4; $j++): ?>
                <?php if (isset($posts[$i + $j])): ?>
                    <div class="medium-post">
                        <a href="<?php echo $posts[$i + $j]['link']; ?>">
                            <img src="<?php echo $posts[$i + $j]['medium']; ?>" alt="<?php echo $posts[$i + $j]['title']; ?>">
                            <h2><?php echo $posts[$i + $j]['title']; ?></h2>
                        </a>
                        <p><?php echo $posts[$i + $j]['excerpt']; ?></p>
                        <p>Categories: <?php echo $posts[$i + $j]['category_links']; ?></p>
                        <p>Views: <?php echo $posts[$i + $j]['views']; ?></p>
                    </div>
                <?php endif; ?>
            <?php endfor; ?>
            
            <!-- Small posts -->
            <?php for ($j = 4; $j < 8; $j++): ?>
                <?php if (isset($posts[$i + $j])): ?>
                    <div class="small-post">
                        <a href="<?php echo $posts[$i + $j]['link']; ?>">
                            <img src="<?php echo $posts[$i + $j]['thumbnail']; ?>" alt="<?php echo $posts[$i + $j]['title']; ?>">
                            <h2><?php echo $posts[$i + $j]['title']; ?></h2></a>
                        <p><?php echo $posts[$i + $j]['excerpt']; ?></p>
                        <p>Categories: <?php echo $posts[$i + $j]['category_links']; ?></p>
                        <p>Views: <?php echo $posts[$i + $j]['views']; ?></p>
                    </div>
                <?php endif; ?>
            <?php endfor; ?>
            
            <!-- Large post -->
            <?php if (isset($posts[$i + 8])): ?>
                <div class="large-post">
                    <a href="<?php echo $posts[$i + 8]['link']; ?>">
                        <img src="<?php echo $posts[$i + 8]['large']; ?>" alt="<?php echo $posts[$i + 8]['title']; ?>">
                        <h2><?php echo $posts[$i + 8]['title']; ?></h2>
                    </a>
                    <p><?php echo $posts[$i + 8]['excerpt']; ?></p>
                    <p>Categories: <?php echo $posts[$i + 8]['category_links']; ?></p>
                    <p>Views: <?php echo $posts[$i + 8]['views']; ?></p>
                </div>
            <?php endif; ?>
        </div>
    <?php endfor; ?>
<?php endif; ?>
        

Step 3: Fetching and Displaying Featured Images and Categories

Ensure your $posts array includes the necessary data for each post, such as title, excerpt, link, and image URLs:

<?php
// Example structure for each post
$posts[] = array(
    'title' => $row['title'],
    'excerpt' => $row['excerpt'],
    'link' => $row['link'],
    'thumbnail' => $row['thumbnail'],
    'medium' => $row['medium'],
    'large' => $row['large'],
    'category_links' => $row['category_links'],
    'views' => $row['views']
);
?>
        

Step 4: Tracking and Displaying Post Views

Create custom functions to set and get the view count:

<?php
function set_post_views($postID) {
    global $conn;
    $sql = "UPDATE posts SET views = views + 1 WHERE id = $postID";
    $conn->query($sql);
}

function get_post_views($postID) {
    global $conn;
    $sql = "SELECT views FROM posts WHERE id = $postID";
    $result = $conn->query($sql);
    $row = $result->fetch_assoc();
    return $row['views'] . ' Views';
}
?>
        

Increment the view count when a post is viewed:

<?php
if (isset($_GET['post_id'])) {
    $postID = $_GET['post_id'];
    set_post_views($postID);
}
?>
        

By following these steps, you can display MySQL database posts in a custom layout with featured images, categories, and view counts. This approach allows you to provide a unique and engaging user experience on your website.

Feel free to adjust and customise the code snippets to better fit your theme and design requirements.