如何用PHP将上传的.doc文件存入MySQL并供Android应用调用

Hey there! I see you've got the upload flow working in XAMPP, but the files are only sticking around in the temp folder before getting deleted. Let's fix that by saving them directly into your MySQL database, and also set things up so your Android app can retrieve and open them later.

1. First, Set Up Your MySQL Table #

You'll need a table built to store binary file data. We'll use LONGBLOB for the file content since .doc files can grow larger than the limit of a regular BLOB. Here's the SQL to create the table:

CREATE TABLE document_storage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(100) NOT NULL,
    file_content LONGBLOB NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This table tracks the file name, type, the actual binary content, and when it was uploaded.

2. PHP Script to Upload and Save .doc to MySQL #

Now let's write a PHP script that captures the uploaded .doc file, reads its content, and inserts it into the database. This will prevent the temp file from being deleted after the script runs, since we're grabbing its content before that happens.

<?php
// Database configuration (update these to match your XAMPP setup)
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'root';
$password = ''; // default XAMPP MySQL password is empty

try {
    // Connect to database using PDO (safer than mysqli for prepared statements)
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Check if a file was uploaded without errors
    if (isset($_FILES['doc_file']) && $_FILES['doc_file']['error'] === UPLOAD_ERR_OK) {
        $file_name = $_FILES['doc_file']['name'];
        $file_type = $_FILES['doc_file']['type'];
        $file_temp_path = $_FILES['doc_file']['tmp_name'];

        // Read the temp file content into a variable
        $file_content = file_get_contents($file_temp_path);

        // Prepare and execute the INSERT statement (prevents SQL injection)
        $stmt = $pdo->prepare("INSERT INTO document_storage (file_name, file_type, file_content) VALUES (?, ?, ?)");
        $stmt->bindParam(1, $file_name);
        $stmt->bindParam(2, $file_type);
        $stmt->bindParam(3, $file_content, PDO::PARAM_LOB); // PARAM_LOB handles binary data

        if ($stmt->execute()) {
            echo "File uploaded and saved to database successfully!";
        } else {
            echo "Failed to save file to database.";
        }
    } else {
        echo "Error uploading file: " . $_FILES['doc_file']['error'];
    }
} catch(PDOException $e) {
    echo "Database error: " . $e->getMessage();
}
?>

How to use this script: #

Create an HTML form (or adapt your existing upload form) that points to this PHP script, with a file input named doc_file and enctype set to multipart/form-data:

<form action="upload_to_db.php" method="post" enctype="multipart/form-data">
    Select .doc file: <input type="file" name="doc_file" accept=".doc,.docx">
    <input type="submit" value="Upload File">
</form>

3. PHP Script to Retrieve the .doc File for Your Android App #

Later, when your Android app needs to open the file, you'll need a way to fetch it from the database. Here's a script that takes a document ID (you can modify it to use file name instead) and outputs the file content with the correct headers:

<?php
// Database configuration (same as before)
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Get document ID from request (e.g., your Android app sends ?id=1)
    if (isset($_GET['id'])) {
        $doc_id = $_GET['id'];

        // Fetch the file from database
        $stmt = $pdo->prepare("SELECT file_name, file_type, file_content FROM document_storage WHERE id = ?");
        $stmt->bindParam(1, $doc_id);
        $stmt->execute();
        $file = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($file) {
            // Set headers to tell the client this is a .doc file
            header("Content-Type: " . $file['file_type']);
            header("Content-Disposition: inline; filename=\"" . $file['file_name'] . "\"");

            // Output the file content
            echo $file['file_content'];
            exit;
        } else {
            echo "Document not found.";
        }
    } else {
        echo "No document ID provided.";
    }
} catch(PDOException $e) {
    echo "Database error: " . $e->getMessage();
}
?>

Important Notes #

• File Size Limits: Make sure your php.ini file has appropriate limits set for uploads. Look for these values and adjust them if needed:
  • upload_max_filesize = 10M (increase if you need to handle large .doc files)
  • post_max_size = 10M
• Android Integration: In your Android app, use an HTTP client (like OkHttp) to send a GET request to the retrieval script (e.g., http://your-local-ip/get_doc.php?id=1), save the response body to a local .doc file, then use an Intent to open it with a compatible app on the device.
• Security: For production, don't use the default root user with an empty password. Create a dedicated database user with limited permissions. Also, validate file types to ensure only .doc/.docx files are uploaded.

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