MySQL creates a picture table to implement picture upload and management functions

MySQL creates a picture table to implement picture upload and management functions

In many Web applications, picture upload and management functions are one of the very common requirements. The MySQL database is a commonly used relational database that can be used to store and manage various types of data, including images.

This article will introduce how to use MySQL to create an image table and use this table to implement image upload and management functions. At the same time, some sample codes will also be provided so that readers can better understand and practice.

1. Create an image table

First, log in to the MySQL database and create a database named images. Next, create a table named images with the following columns:

• id: A unique identifier for the image, using an auto-incrementing integer type.
• name: The name of the image, using string type.
• file: The file name of the image, using string type.
• size: The file size of the image, using integer type.
• type: The file type of the image, using string type.
• created_at: The creation time of the image, using date and time type.

The following is the specific SQL statement to create a table:

CREATE TABLE `images` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `file` VARCHAR(100) NOT NULL,
  `size` INT NOT NULL,
  `type` VARCHAR(100) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. Image upload function

Next, write a PHP for image upload Script used to save images uploaded by users to the server and insert related information into the database.

First, create an HTML form to accept the image file selected by the user:

<form action="upload.php" method="post" enctype="multipart/form-data">
  <input type="file" name="image" />
  <input type="submit" value="Upload" />
</form>

Then, write the upload.php script to handle the logic of image upload:

<?php
// 检查是否有文件上传
if(isset($_FILES['image'])) {
  // 获取上传的文件信息
  $file = $_FILES['image'];
  $name = $file['name'];
  $tmp_name = $file['tmp_name'];
  $size = $file['size'];
  $type = $file['type'];
  
  // 将文件保存到服务器的指定目录
  $upload_path = 'uploads/';
  $new_name = uniqid() . '_' . $name;
  $destination = $upload_path . $new_name;
  move_uploaded_file($tmp_name, $destination);
  
  // 将文件信息插入到数据库
  $db = new mysqli('localhost', 'root', 'password', 'images');
  $sql = "INSERT INTO images (name, file, size, type) VALUES ('$name', '$new_name', $size, '$type')";
  $db->query($sql);
  $db->close();
  
  echo 'File uploaded successfully!';
}
?>

The above code will save the images uploaded by the user to the uploads/ directory, and at the same time insert the relevant information of the images into the images table of the database.

3. Picture Management Function

Finally, we can also provide some simple picture management functions, such as displaying all uploaded pictures, deleting specific pictures, etc.

The following is a simple image display scriptimages.php, used to display all uploaded images:

<?php
$db = new mysqli('localhost', 'root', 'password', 'images');
$sql = "SELECT * FROM images";
$result = $db->query($sql);

while($row = $result->fetch_assoc()) {
  echo '<img src="uploads/' . $row['file'] . '" alt="' . $row['name'] . '" />';
}

$db->close();
?>

The above code will output each uploaded image in sequence <img alt="MySQL creates a picture table to implement picture upload and management functions" > tags of images to display all images on the web page.

As for the function of deleting pictures, you can design the corresponding logic according to actual needs and write the corresponding PHP script to implement it.

Summary

This article introduces how to use MySQL to create a picture table, and use this table to implement picture upload and management functions. At the same time, some sample codes are provided to help readers better understand and practice.

By storing image-related information in the database, we can manage and operate image data more conveniently, not just limited to the file system. Of course, specific application scenarios and requirements may vary, and readers can flexibly adjust and expand according to actual conditions.

The above is the detailed content of MySQL creates a picture table to implement picture upload and management functions. For more information, please follow other related articles on the PHP Chinese website!