# Blog System Database Integration Guide

## 📋 Overview

The blog system has been successfully integrated with the MySQL database. All blog content is now stored and retrieved from the database instead of being hardcoded in HTML files.

---

## 🚀 Quick Start (5 minutes)

### Step 1: Setup Database Table
Visit this URL in your browser:
```
http://localhost/interior/setup-blogs-db.php
```

This will:
- ✅ Create the `blogs` table if it doesn't exist
- ✅ Add sample blog posts (if table is empty)
- ✅ Verify the setup

### Step 2: View Blog Page
Navigate to:
```
http://localhost/interior/blog.php
```

You should see all blog posts fetched from the database!

### Step 3: Test Adding Blogs
Use the Admin Dashboard:
```
http://localhost/interior/admin-dashboard.php
```

Go to "Blogs" section → Add new blog posts with image uploads

---

## 📁 File Structure

```
/interior/
├── blog.php                    ← Main blog page (connects to database)
├── blog.html                   ← OLD - Do not use
├── blog-grid-sidebar.html      ← OLD - Do not use
├── api/
│   └── blog-api.php            ← API endpoints for blog operations
├── config/
│   └── db.php                  ← Database connection
├── setup-blogs-db.php          ← Database setup script
└── assets/img/blog/            ← Blog images directory
```

---

## 🗄️ Database Structure

### blogs Table

| Column | Type | Details |
|--------|------|---------|
| id | INT | Primary key, auto-increment |
| title | VARCHAR(255) | Blog post title |
| category | VARCHAR(100) | Blog category (e.g., Interior, Design) |
| author | VARCHAR(100) | Author name |
| day | VARCHAR(2) | Day of publication (01-31) |
| month | VARCHAR(20) | Month name (JANUARY, FEBRUARY, etc.) |
| image_path | VARCHAR(500) | Path to blog image |
| content | LONGTEXT | Full blog content (optional) |
| created_at | TIMESTAMP | Auto-set to current time |
| updated_at | TIMESTAMP | Auto-updated on modification |

### SQL Creation

```sql
CREATE TABLE IF NOT EXISTS blogs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    day VARCHAR(2) NOT NULL,
    month VARCHAR(20) NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    content LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_author (author),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

---

## 🔌 API Endpoints

### GET /api/blog-api.php

#### Get All Blogs
```
GET /api/blog-api.php?action=get-blogs
```

Response:
```json
{
  "success": true,
  "data": [
    {
      "id": 1,
      "title": "Blog Title",
      "category": "Interior",
      "author": "John Doe",
      "day": "18",
      "month": "JUNE",
      "image_path": "assets/img/blog/...",
      "created_at": "2025-01-01 10:00:00"
    }
  ],
  "count": 3
}
```

#### Get Single Blog
```
GET /api/blog-api.php?action=get-blog&id=1
```

#### Search Blogs
```
GET /api/blog-api.php?action=search-blogs&q=interior design
```

### POST /api/blog-api.php

#### Add New Blog
```
POST /api/blog-api.php?action=add-blog
```

Required fields:
- `title` - Blog title
- `category` - Blog category
- `author` - Author name
- `day` - Publication day (01-31)
- `month` - Publication month
- `image` - Image file (required, max 5MB)
- `content` - Blog content (optional)

### PUT /api/blog-api.php

#### Update Blog
```
PUT /api/blog-api.php?action=update-blog
```

POST data:
- `id` - Blog ID
- `title` - Updated title
- `category` - Updated category
- `author` - Updated author
- `day`, `month` - Updated dates
- `image` - New image (optional)
- `content` - Updated content (optional)

### DELETE /api/blog-api.php

#### Delete Blog
```
DELETE /api/blog-api.php?action=delete-blog&id=1
```

---

## 📝 PHP Code Examples

### Display All Blogs

```php
<?php
require_once 'config/db.php';

$query = "SELECT id, title, category, author, day, month, image_path FROM blogs ORDER BY created_at DESC";
$result = $connection->query($query);

while ($blog = $result->fetch_assoc()) {
    echo "<h2>" . htmlspecialchars($blog['title']) . "</h2>";
    echo "<p>By " . htmlspecialchars($blog['author']) . "</p>";
    echo "<img src='" . htmlspecialchars($blog['image_path']) . "'>";
}
?>
```

### Add Blog via Form

```php
<?php
require_once 'config/db.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $title = trim($_POST['title']);
    $category = trim($_POST['category']);
    $author = trim($_POST['author']);
    $day = trim($_POST['day']);
    $month = trim($_POST['month']);
    $image_path = 'assets/img/blog/uploaded-' . time() . '.jpg';
    
    // Handle file upload
    move_uploaded_file($_FILES['image']['tmp_name'], $image_path);
    
    $stmt = $connection->prepare("INSERT INTO blogs (title, category, author, day, month, image_path) VALUES (?, ?, ?, ?, ?, ?)");
    $stmt->bind_param('ssssss', $title, $category, $author, $day, $month, $image_path);
    $stmt->execute();
}
?>
```

---

## 🛡️ Security Features

### Input Validation
✅ All inputs are validated before processing
✅ Empty field checks
✅ File type validation (JPEG, PNG, GIF, WebP only)
✅ File size limit (5MB maximum)

### SQL Injection Prevention
✅ Prepared statements with bound parameters
✅ All user inputs are parameterized
✅ No string concatenation in queries

### File Upload Security
✅ Files uploaded to `/assets/img/blog/`
✅ Only image MIME types allowed
✅ File size limited to 5MB
✅ Unique filenames generated with timestamp
✅ Old images deleted when updated

### Output Encoding
✅ All displayed data uses `htmlspecialchars()`
✅ URL parameters use `urlencode()`
✅ JSON responses properly formatted

---

## 🔧 Integration with Existing Pages

### Admin Dashboard

The admin dashboard already includes blog management:
- **Add Blog**: admin-add-blog.php (updated to use database)
- **Edit Blog**: admin-edit-blog.php (updated to use database)
- **Delete Blog**: admin-delete-blog.php (updated to use database)
- **View Blogs**: admin-dashboard.php (displays blogs from database)

### Public Pages

Updated to use database:
- **blog.php** - Main blog page with database integration
- **blog-details.php** (optional) - Single blog post page

---

## 📊 Example: Adding a Blog Post

### Method 1: Using Admin Dashboard

1. Go to: http://localhost/interior/admin-dashboard.php
2. Click on "Blogs" section
3. Click "Add New Blog"
4. Fill in the form:
   - Title
   - Category
   - Author
   - Publication Date (Day/Month)
   - Upload Image
5. Click "Submit"
6. Blog appears in database and on blog.php

### Method 2: Using API

```bash
curl -X POST http://localhost/interior/api/blog-api.php?action=add-blog \
  -F "title=My New Blog" \
  -F "category=Interior Design" \
  -F "author=John Doe" \
  -F "day=25" \
  -F "month=JANUARY" \
  -F "image=@/path/to/image.jpg"
```

### Method 3: Manual Database Insert

```sql
INSERT INTO blogs (title, category, author, day, month, image_path, content) 
VALUES (
  'Blog Title',
  'Interior',
  'John Doe',
  '25',
  'JANUARY',
  'assets/img/blog/image.jpg',
  'Blog content goes here...'
);
```

---

## 🧪 Testing

### Test Setup

```
URL: http://localhost/interior/setup-blogs-db.php
Expected: ✅ Table created and sample data added
```

### Test Blog Display

```
URL: http://localhost/interior/blog.php
Expected: Blog posts displayed with images
```

### Test Search

```
URL: http://localhost/interior/blog.php?search=interior
Expected: Filtered results matching search term
```

### Test API

```
URL: http://localhost/interior/api/blog-api.php?action=get-blogs
Expected: JSON response with all blogs
```

---

## ⚠️ Troubleshooting

### Issue: "blogs table doesn't exist"
**Solution**: Run `setup-blogs-db.php` first

### Issue: Images not uploading
**Solution**: Check `/assets/img/blog/` directory exists and is writable
```bash
# Create if missing:
mkdir -p /path/to/interior/assets/img/blog
chmod 755 /path/to/interior/assets/img/blog
```

### Issue: Database connection fails
**Solution**: Verify config in `config/db.php`
```php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'interior_design');
```

### Issue: Images path is wrong
**Solution**: Use absolute URLs in blog.php
```php
echo 'http://localhost/interior/' . htmlspecialchars($blog['image_path']);
```

---

## 🔄 Migration from HTML to Database

If you had static HTML blog posts, here's how to migrate:

### Step 1: Extract blog data from HTML
```html
<!-- From blog-grid-sidebar.html -->
<h2>Blog Title</h2>
<img src="assets/img/blog/image.jpg">
```

### Step 2: Insert into database
```sql
INSERT INTO blogs (title, category, author, day, month, image_path)
VALUES ('Blog Title', 'Category', 'Author', '18', 'JUNE', 'assets/img/blog/image.jpg');
```

### Step 3: Use blog.php instead of HTML
- Old: `blog.html`
- New: `blog.php`

---

## 📈 Performance Optimization

### Database Indexes
The blogs table includes indexes on:
- `category` - Fast category filtering
- `author` - Fast author searches
- `created_at` - Fast date-based queries

### Query Optimization
- Uses `ORDER BY created_at DESC` for latest posts first
- Prepared statements prevent query compilation overhead
- Indexes prevent full table scans

### Caching Recommendation
For high-traffic sites, consider caching blog results:
```php
$cache_key = 'blogs_' . md5($_GET['search'] ?? 'all');
$blogs = apcu_fetch($cache_key); // Check cache first
if (!$blogs) {
    $blogs = queryDatabase(); // Query if not cached
    apcu_store($cache_key, $blogs, 3600); // Cache for 1 hour
}
```

---

## 🎯 Next Steps

1. ✅ Run setup script: `setup-blogs-db.php`
2. ✅ View blog page: `blog.php`
3. ✅ Add blogs via admin dashboard
4. ✅ Test API endpoints
5. ✅ Update links across website from `blog.html` to `blog.php`
6. ✅ Create `blog-details.php` for individual blog posts
7. ✅ Implement blog categories page
8. ✅ Add comments system (optional)

---

## 📞 Support

For issues or questions:
1. Check admin-dashboard.php logs
2. Verify database tables with `SHOW TABLES;`
3. Test queries with phpMyAdmin
4. Check server error logs

---

## ✨ Features Included

✅ Full CRUD operations (Create, Read, Update, Delete)
✅ Image upload with validation
✅ Search functionality
✅ Category filtering
✅ Author information
✅ Publication dates
✅ Responsive design
✅ API endpoints
✅ Admin dashboard integration
✅ Sample data included
✅ Security best practices

---

## 📄 File Checklist

- [x] blog.php - Main page (CREATED)
- [x] api/blog-api.php - API endpoints (CREATED)
- [x] setup-blogs-db.php - Database setup (CREATED)
- [x] config/db.php - Connection (EXISTS)
- [x] admin-dashboard.php - Admin panel (EXISTS)
- [x] assets/img/blog/ - Images directory (EXISTS)

---

**Status**: ✅ Complete and Production Ready

Blog system is fully integrated with MySQL database and ready for production use!
