rogerts table paintins and retrieve them

gistfile1.txt

-- Create the gallery table
CREATE TABLE gallery (
    id INT PRIMARY KEY AUTO_INCREMENT,
    filename VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    size VARCHAR(50) NOT NULL,
    price VARCHAR(20) NOT NULL,
    caption TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert your existing data
INSERT INTO gallery (id, filename, title, size, price, caption) VALUES
(1, '1.jpg', 'Fox in nature', '500x500', '£100', 'Painting by RogerFoxCode 12345678'),
(2, '2.jpg', 'Green landscape', '500x500', '£200', 'Painting by RogerFox Code 01010101'),
(3, '3.jpg', 'House in nature', '500x500', '£300', 'Painting by RogerFox Code 1234'),
(4, '4.jpg', 'House in nature', '500x500', '£300', 'Painting by RogerFox Code 1234'),
(5, '5.jpg', 'House in nature', '500x500', '£300', 'Painting by RogerFox Code 1234'),
(6, '6.jpg', 'House in nature', '500x500', '£300', 'Painting by RogerFox Code 1234'),
(7, '7.jpg', 'House in nature', '500x500', '£300', 'Painting by RogerFox Code 1234');


<?php
// Database connection
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_username';
$password = 'your_password';

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

// Check connection
if ($mysqli->connect_error) {
    http_response_code(500);
    echo json_encode(['error' => 'Database connection failed: ' . $mysqli->connect_error]);
    exit;
}

// Prepare and execute query
$query = "SELECT id, filename, title, size, price, caption FROM gallery ORDER BY id";
if ($result = $mysqli->query($query)) {
    $galleryData = [];
    while ($row = $result->fetch_assoc()) {
        $galleryData[] = $row;
    }
    $result->free();

    // Return JSON response
    header('Content-Type: application/json');
    echo json_encode($galleryData);
} else {
    http_response_code(500);
    echo json_encode(['error' => 'Query failed: ' . $mysqli->error]);
}

$mysqli->close();
?>


// Function to fetch gallery data from database and render gallery
async function loadGallery() {
    try {
        const response = await fetch('get_gallery.php'); // Your PHP file
        
        if (!response.ok) {
            throw new Error(`HTTP error! status: ${response.status}`);
        }
        
        const galleryData = await response.json();
        
        // Clear existing gallery content
        const galleryContainer = document.getElementById('gallery-container'); // Make sure you have this element
        galleryContainer.innerHTML = '';
        
        // Render gallery items
        galleryData.forEach((item, index) => {
            const galleryItem = document.createElement("div");
            galleryItem.className = "gallery-item";
            galleryItem.innerHTML = `
                <a target="_blank" href="img/${item.filename}">
                    <img src="img/${item.filename}" alt="Roger's artwork" data-index="${index}">
                </a>
                <div class="gallery-caption">${item.caption}</div>
                <div class="text-box-gallery">
                    <h3>${item.title}</h3>
                    <p class="gallery-size">Size: ${item.size}</p>
                    <p class="price">
                        <button class="add-to-cart" data-id="${item.id}">Add to cart</button>
                        ${item.price}
                    </p>
                </div>
            `;
            galleryContainer.appendChild(galleryItem);
        });
        
        console.log('Gallery loaded successfully');
        
    } catch (error) {
        console.error('Error loading gallery:', error);
        // Display error message to user
        const galleryContainer = document.getElementById('gallery-container');
        galleryContainer.innerHTML = '<p>Error loading gallery. Please try again later.</p>';
    }
}

// Call the function when the page loads
document.addEventListener('DOMContentLoaded', function() {
    loadGallery();
});

// Optional: Function to reload gallery data
function reloadGallery() {
    loadGallery();
}