XAMPP中图片存入test数据库无法显示，代码报错求解决

Hey there! Let's work through your image storage and display issues step by step—sounds like you've got a couple of kinks to iron out, but we'll get it sorted.

First, Fix the Obvious PHP Code Errors #

Looking at your code snippet, there are clear syntax and logic gaps causing those errors:

1. Undefined variables & invalid array syntax
   Your line $pictures = array($rows=>id, $rows=>filena... is broken. You haven't fetched any rows from your query result yet, and the array key-value format is wrong (keys need to be valid identifiers, and you’re using undefined variables like $rows and id).

2. Missing result-fetching step
   After running mysqli_query($conn, $sql), you need to loop through the result set to pull out each row of image data.

Here’s a corrected base version of your PHP code to fetch images properly:

<?php
// Enable error reporting to see detailed issues (remove in production)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$server = "localhost";
$username = "daiori";
$password = "";
$db = "test";

// Connect to database and check for connection errors
$conn = mysqli_connect($server, $username, $password, $db);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Fetch image data (added `id` for unique identification)
$sql = "SELECT `id`, `pictures`, `filename` FROM `pictures`";
$run = mysqli_query($conn, $sql);

// Check if query executed successfully
if (!$run) {
    die("Query failed: " . mysqli_error($conn));
}

// Store all image rows in an array
$pictures = [];
while ($row = mysqli_fetch_assoc($run)) {
    $pictures[] = $row;
}

mysqli_close($conn);
?>

Why Your Images Didn’t Display When Stored #

If storing images in the database never worked correctly, it’s almost certainly one of these two issues:

• Wrong database field type: Your pictures column needs to be MEDIUMBLOB (supports images up to 16MB) or LONGBLOB (for larger files). The basic BLOB type only holds 64KB—way too small for most images.
• Improper insertion of binary data: When adding images to the database, you need to read the file’s binary content and escape it safely. Here’s a working insertion script example:

<?php
// Connection code same as above
$target_image = "path/to/your/local/image.jpg";
$image_binary = file_get_contents($target_image);
$image_binary = mysqli_real_escape_string($conn, $image_binary);
$filename = basename($target_image);

$sql = "INSERT INTO `pictures` (`filename`, `pictures`) VALUES ('$filename', '$image_binary')";
if (mysqli_query($conn, $sql)) {
    echo "Image stored successfully!";
} else {
    echo "Insert error: " . mysqli_error($conn);
}
?>

Implement Click-to-Show Image Functionality #

To make a <div> clickable and toggle image display, we’ll combine PHP with HTML/JavaScript. Here are two reliable approaches:

Approach 1: Data URIs (Good for Small Images) #

This embeds the image directly in the page source:

<!DOCTYPE html>
<html>
<head>
    <style>
        .image-trigger {
            padding: 15px;
            border: 1px solid #ddd;
            cursor: pointer;
            margin: 10px 0;
            background: #f8f8f8;
        }
        .hidden-image {
            display: none;
            margin-top: 10px;
            max-width: 400px;
        }
    </style>
</head>
<body>
    <?php foreach ($pictures as $pic): ?>
        <div class="image-trigger" onclick="toggleImage('img-<?= $pic['id'] ?>')">
            Click to view: <?= $pic['filename'] ?>
            <img id="img-<?= $pic['id'] ?>" class="hidden-image" 
                 src="data:image/jpeg;base64,<?= base64_encode($pic['pictures']) ?>" 
                 alt="<?= $pic['filename'] ?>">
        </div>
    <?php endforeach; ?>

    <script>
        function toggleImage(imgId) {
            const img = document.getElementById(imgId);
            img.style.display = img.style.display === 'none' ? 'block' : 'none';
        }
    </script>
</body>
</html>

Approach 2: Separate Image Serving Script (Better for Large Images) #

This keeps your page source clean and handles bigger files better. Create a file named get_image.php:

<?php
$server = "localhost";
$username = "daiori";
$password = "";
$db = "test";

$conn = mysqli_connect($server, $username, $password, $db);
if (!$conn) die("Connection failed");

// Validate image ID to avoid SQL injection
$image_id = $_GET['id'] ?? '';
if (!is_numeric($image_id)) die("Invalid image ID");

$sql = "SELECT `pictures`, `filename` FROM `pictures` WHERE id = $image_id";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);

// Set correct content type based on image file extension
$file_ext = pathinfo($row['filename'], PATHINFO_EXTENSION);
header("Content-Type: image/$file_ext");
echo $row['pictures'];

mysqli_close($conn);
?>

Then use this in your main page:

<!DOCTYPE html>
<html>
<head>
    <style>
        .image-trigger {
            padding: 15px;
            border: 1px solid #ddd;
            cursor: pointer;
            margin: 10px 0;
            background: #f8f8f8;
        }
        .hidden-image {
            display: none;
            margin-top: 10px;
            max-width: 400px;
        }
    </style>
</head>
<body>
    <?php foreach ($pictures as $pic): ?>
        <div class="image-trigger" onclick="toggleImage('img-<?= $pic['id'] ?>')">
            Click to view: <?= $pic['filename'] ?>
            <img id="img-<?= $pic['id'] ?>" class="hidden-image" 
                 src="get_image.php?id=<?= $pic['id'] ?>" 
                 alt="<?= $pic['filename'] ?>">
        </div>
    <?php endforeach; ?>

    <script>
        function toggleImage(imgId) {
            const img = document.getElementById(imgId);
            img.style.display = img.style.display === 'none' ? 'block' : 'none';
        }
    </script>
</body>
</html>

Quick Final Checks #

• Double-check that your daiori database user has full access to the test database.
• If you switch to storing image paths instead of binary data (often more efficient), ensure your web server has read permissions for the image folder.

内容的提问来源于stack exchange，提问作者Damian Mitchell