# Blog Page Database Integration Guide

## Quick Reference

**Page:** blog-grid-4-masonary.php  
**Database Table:** `blog_posts`  
**Records:** 6 most recent published posts  
**Display Type:** Masonry grid (4 cols XL, 3 cols desktop, 2 cols tablet, 1 col mobile)  
**Sorting:** Newest first (by created_date DESC)

---

## Database Table Schema

```sql
CREATE TABLE blog_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    author VARCHAR(100),
    category VARCHAR(50),
    featured_image VARCHAR(255),
    status ENUM('published', 'draft', 'archived') DEFAULT 'draft',
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## Adding a New Blog Post

### Method 1: Direct SQL Insert

```sql
INSERT INTO blog_posts (
    title, 
    content, 
    author, 
    category, 
    featured_image, 
    status
) VALUES (
    'Effective Home Workouts for Busy Professionals',
    'In todays busy world... [full blog content here]',
    'Sarah Wilson',
    'Fitness Tips',
    'assets/images/blog/blog-grid/pic7.jpg',
    'published'
);
```

### Method 2: Using phpMyAdmin
1. Open phpMyAdmin
2. Navigate to `powerzone_db` → `blog_posts`
3. Click "Insert"
4. Fill in all fields
5. Set status = "published" (to make visible on website)
6. Click "Save"

---

## Field Descriptions

| Field | Type | Example | Required | Notes |
|-------|------|---------|----------|-------|
| title | VARCHAR(255) | "5 Best Yoga Practices" | Yes | Blog post headline |
| content | LONGTEXT | Full blog post text | Yes | Main article content |
| author | VARCHAR(100) | "John Doe" | No | Writer's name |
| category | VARCHAR(50) | "Fitness", "Yoga" | No | Post category/topic |
| featured_image | VARCHAR(255) | "assets/images/blog/pic1.jpg" | Yes | Cover image path |
| status | ENUM | "published", "draft", "archived" | Yes | Only "published" shows on site |

---

## Post Status Explained

| Status | Visible on Website | Use Case |
|--------|-------------------|----------|
| **published** | ✅ Yes (Default) | Live articles visible to all users |
| **draft** | ❌ No | Work in progress, not ready yet |
| **archived** | ❌ No | Old articles to hide but keep |

**Important:** Only posts with status = "published" appear on the blog page.

---

## Default Sample Data

If the `blog_posts` table is empty, these 6 posts are auto-populated:

1. **Personal Journeys to a Healthier Lifestyle**
   - Author: John Doe
   - Category: Fitness
   - Image: assets/images/blog/blog-grid/pic1.jpg

2. **Unleash Your Inner Beast at the Gym**
   - Author: Jane Smith
   - Category: Gym Training
   - Image: assets/images/blog/blog-grid/pic2.jpg

3. **Achieving Mental Clarity at the Gym**
   - Author: John Doe
   - Category: Mental Health
   - Image: assets/images/blog/blog-grid/pic3.jpg

4. **How Exercise Boosts Your Wellbeing**
   - Author: Sarah Wilson
   - Category: Wellness
   - Image: assets/images/blog/blog-grid/pic4.jpg

5. **The Ultimate Cardio Workout Exercise**
   - Author: Mike Johnson
   - Category: Cardio
   - Image: assets/images/blog/blog-grid/pic5.jpg

6. **Advanced Strength Training Techniques**
   - Author: John Doe
   - Category: Strength
   - Image: assets/images/blog/blog-grid/pic6.jpg

---

## How It Works

### 1. Database Query
```php
$query = "SELECT * FROM blog_posts 
          WHERE status = 'published' 
          ORDER BY created_date DESC 
          LIMIT 6";
```

**Explanation:**
- **WHERE status = 'published'** → Only shows published posts
- **ORDER BY created_date DESC** → Newest posts first
- **LIMIT 6** → Shows maximum 6 posts

### 2. Loop Display
```php
<?php foreach ($blogPosts as $index => $post): 
    $postDate = date('d M Y', strtotime($post['created_date']));
?>
    <h4><?php echo htmlspecialchars($post['title']); ?></h4>
    <p><?php echo htmlspecialchars(substr($post['content'], 0, 100)) . '...'; ?></p>
    <span><?php echo $postDate; ?></span>
<?php endforeach; ?>
```

### 3. Auto-Population
If no blog posts exist, 6 sample posts are automatically inserted using prepared statements.

---

## Publishing a Blog Post

### Step 1: Write in Database
Insert blog post with status = "draft" (hidden from public)

### Step 2: Edit & Review
Update content and check formatting in phpMyAdmin

### Step 3: Publish
Update status from "draft" to "published"

```sql
UPDATE blog_posts SET status = 'published' WHERE id = 1;
```

### Step 4: Verify
Refresh blog page - new post appears in grid

---

## Scheduling Blog Posts

### Create Post for Future
```sql
INSERT INTO blog_posts (
    title, 
    content, 
    author, 
    category, 
    featured_image, 
    status,
    created_date
) VALUES (
    'Post Title',
    'Content...',
    'Author Name',
    'Category',
    'image.jpg',
    'draft',
    '2025-02-14 00:00:00'  -- Future date
);
```

Then update status to "published" when ready.

---

## Unpublishing a Post

### Make Draft (Hide but Keep)
```sql
UPDATE blog_posts SET status = 'draft' WHERE id = 1;
```
Post disappears from blog page but data remains.

### Archive (Hide Permanently)
```sql
UPDATE blog_posts SET status = 'archived' WHERE id = 1;
```
Post hidden and marked as old/archived.

---

## Deleting a Post

### Permanent Delete
```sql
DELETE FROM blog_posts WHERE id = 1;
```

### Via phpMyAdmin
1. Select the post's row
2. Click "Delete" button
3. Confirm deletion
4. Data is permanently removed

---

## Display Format

### Blog Post Card Layout
```html
<div class="dz-card style-2 overlay-shine">
    <div class="dz-media">
        <img src="{featured_image}" alt="{title}">
    </div>
    <div class="dz-info">
        <h4 class="dz-title">
            <a href="blog-detail.php?id={id}">{title}</a>
        </h4>
        <p>{content truncated to 100 chars}...</p>
        <span class="dz-date">{created_date formatted as "DD Mon YYYY"}</span>
    </div>
</div>
```

---

## Masonry Grid Layout

| Screen Size | Columns | Class |
|-------------|---------|-------|
| Extra Large (≥1400px) | 4 | col-xl-3 |
| Desktop (≥992px) | 3 | col-lg-4 |
| Tablet (≥768px) | 2 | col-md-6 |
| Mobile (<768px) | 1 | col-sm-6 |

---

## Content Formatting

### Display Rules
- **Title:** Full text displayed
- **Content:** First 100 characters shown, truncated with "..."
- **Date:** Formatted as "15 Dec 2024"
- **Author:** Displayed in metadata
- **Category:** Shown in card

### Example Output
```
Title: "Achieving Mental Clarity at the Gym"
Content: "Regular gym sessions boost your mental health and clarity..."
Date: "12 Dec 2024"
```

---

## Common Customizations

### Change Number of Posts Shown
```php
// Change LIMIT from 6 to different number
$query = "SELECT * FROM blog_posts 
          WHERE status = 'published' 
          ORDER BY created_date DESC 
          LIMIT 9";  // Shows 9 posts instead of 6
```

### Show Only Specific Category
```php
$query = "SELECT * FROM blog_posts 
          WHERE status = 'published' 
          AND category = 'Fitness' 
          ORDER BY created_date DESC 
          LIMIT 6";
```

### Show Posts by Specific Author
```php
$query = "SELECT * FROM blog_posts 
          WHERE status = 'published' 
          AND author = 'John Doe' 
          ORDER BY created_date DESC 
          LIMIT 6";
```

### Oldest Posts First (Reverse Order)
```php
// Change DESC to ASC
$query = "SELECT * FROM blog_posts 
          WHERE status = 'published' 
          ORDER BY created_date ASC 
          LIMIT 6";
```

---

## Linking to Full Article

### Blog Detail Link
Each post links to individual blog detail page:

```html
<a href="blog-detail.php?id={id}">Read More</a>
```

**Current Status:** Links are ready but blog-detail.php needs to be created

### View Full Post SQL
```php
// Get single post by ID (for blog-detail.php)
$query = "SELECT * FROM blog_posts WHERE id = ?";
```

---

## Security Features

- ✅ **XSS Prevention:** All titles/content escaped with `htmlspecialchars()`
- ✅ **Content Truncation:** First 100 chars shown, preventing overly long displays
- ✅ **Status Filtering:** Only published posts visible
- ✅ **SQL Injection Prevention:** Prepared statements for all inserts
- ✅ **Date Formatting:** Safe date conversion

---

## Content Tips

### Effective Blog Titles
- Keep between 40-60 characters
- Include keywords (Fitness, Training, Health, etc.)
- Make it descriptive and clickable

### Featured Images
- Size: Ideally 400x300px minimum
- Format: JPG or PNG
- Path: Must start with "assets/images/blog/"

### Content Writing
- Use paragraph breaks for readability
- Include headers and subheaders
- Target 500-2000 words for full article
- Add relevant keywords

### Categories
- Keep consistent (e.g., "Fitness", "Yoga", "Cardio")
- Use 5-10 categories max
- Makes content easy to filter

---

## Database Queries Reference

### View All Published Posts
```sql
SELECT * FROM blog_posts WHERE status = 'published' ORDER BY created_date DESC;
```

### View All Draft Posts
```sql
SELECT * FROM blog_posts WHERE status = 'draft' ORDER BY created_date DESC;
```

### Count Published Posts
```sql
SELECT COUNT(*) as total FROM blog_posts WHERE status = 'published';
```

### Get Posts by Date Range
```sql
SELECT * FROM blog_posts 
WHERE status = 'published' 
AND created_date BETWEEN '2024-01-01' AND '2024-12-31';
```

### Get Popular Categories
```sql
SELECT category, COUNT(*) as count 
FROM blog_posts 
WHERE status = 'published' 
GROUP BY category 
ORDER BY count DESC;
```

---

## Troubleshooting

| Problem | Solution |
|---------|----------|
| New posts not showing | Check status = "published" in database |
| Images not displaying | Verify featured_image path starts with "assets/images/blog/" |
| Wrong date showing | Check created_date field format (YYYY-MM-DD HH:MM:SS) |
| Content cuts off | Check content text length, ensure no special characters breaking HTML |
| Links broken | Verify blog-detail.php exists (currently needs to be created) |
| Grid looks wrong | Clear browser cache (Ctrl+F5), check Bootstrap CSS loaded |

---

## Related Files

- **Main Page:** `/blog-grid-4-masonary.php`
- **Detail Page:** `/blog-detail.php` (to be created)
- **Database Connection:** `/includes/db.php`
- **Database Schema:** `/setup-database.php`
- **Navigation:** `/header.html`
- **Footer:** `/footer.html`
- **Styles:** `/assets/css/style.css`

---

## Animation Effects

- **Fade-in:** Posts fade in on page load
- **Delay:** Staggered appearance (0.4s + 0.2s per index)
- **Hover:** Card lifts up on mouse hover
- **Overlay:** Image overlay effect on hover

---

## Version Info

- **Created:** 2024
- **Database Version:** PowerZone DB v1.0
- **PHP Version:** 7.x+
- **Layout:** Masonry Grid (CSS Grid)
- **Status:** Production Ready (Database integration ✅, Detail page pending)
