PHP+MySQL环境下record.js录制音频的路径存储与检索求助

Hey there! Let's get your audio recording upload and storage working properly. I'll break down the issues in your current code and walk you through a complete, robust solution.

First: Fix the Issues in Your Existing Code #

Your current action.php has a few key problems that are blocking successful uploads:

• The move_uploaded_file() function takes two arguments: first the temporary file path (tmp_name), then the target path. Your code has them reversed.
• Using $_FILES['file']['tmp_name'] as the final filename is wrong—this is just the temporary name the server uses, and it'll get overwritten or deleted. You need a unique, permanent filename.
• You're not checking if the veepos directory exists (if it doesn't, the upload will fail).
• There's no code to save the audio path to your MySQL database.
• Your response logic is incomplete—you should return meaningful data to the frontend instead of just a success/fail message.

Complete Step-by-Step Solution #

1. Prepare the Storage Directory #

First, make sure you have a veepos folder in your project root. Set its permissions to 755 (or 777 if your server requires it) so PHP can write files to it. If the folder doesn't exist, create it manually or add code to create it automatically (we'll include that in the PHP code).

2. Updated Frontend JavaScript #

Your JS code is mostly good—we'll just tweak the success callback to handle a more useful response:

mediaRecorder.onstop = (ev)=> { 
  let blob = new Blob(veepoData, { 'type' : 'audio/ogg; codecs=opus' }); 
  let audioURL = window.URL.createObjectURL(blob); 
  audioSave.src = audioURL; 
  VeepoRec(blob); 
}
mediaRecorder.ondataavailable = function(ev) { 
  veepoData.push(ev.data); 
}
function VeepoRec(blob) { 
  var fd = new FormData(); 
  fd.append('file', blob, 'recording_' + Date.now() + '.ogg'); // Add a filename hint (optional but helpful)
  $.ajax({ 
    url: 'action.php', 
    data: fd, 
    type: 'post', 
    contentType: false, 
    processData: false, 
    success: function(response){ 
      const res = JSON.parse(response);
      if(res.success){ 
        console.log('Upload success! Audio ID:', res.audio_id);
        // You can use res.audio_id to fetch the audio later, or update the UI immediately
      } else{ 
        console.log('Upload failed:', res.error); 
      } 
    }, 
    error: function(){
      console.log('Server error during upload');
    }
  }); 
};

3. Rewritten action.php (File Storage + Database Save) #

This code handles file upload validation, stores the audio file, and saves the path to your MySQL database. Make sure to update the database credentials to match your setup:

<?php
// Database configuration (update these to your details)
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_user';
$password = 'your_db_password';

// Response array to send back to frontend
$response = ['success' => false, 'error' => '', 'audio_id' => null];

if(isset($_FILES['file']) && $_FILES['file']['error'] === UPLOAD_ERR_OK) {
    // Get file details
    $tmpFilePath = $_FILES['file']['tmp_name'];
    $fileType = $_FILES['file']['type'];
    $fileSize = $_FILES['file']['size'];

    // Validate file type (only allow audio/ogg)
    if($fileType !== 'audio/ogg') {
        $response['error'] = 'Only OGG audio files are allowed';
        echo json_encode($response);
        exit;
    }

    // Define storage path
    $uploadDir = 'veepos/';
    // Create directory if it doesn't exist
    if(!is_dir($uploadDir)) {
        mkdir($uploadDir, 0755, true);
    }

    // Generate a unique filename to avoid overwrites
    $fileName = uniqid('audio_') . '.ogg';
    $targetFilePath = $uploadDir . $fileName;

    // Move the temporary file to the target directory
    if(move_uploaded_file($tmpFilePath, $targetFilePath)) {
        // Connect to MySQL database
        try {
            $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            // Insert audio path into database (adjust table/column names to match your schema)
            $stmt = $pdo->prepare("INSERT INTO audio_records (file_path, upload_date) VALUES (:file_path, NOW())");
            $stmt->bindParam(':file_path', $targetFilePath);
            $stmt->execute();

            // Get the ID of the newly inserted record
            $audioId = $pdo->lastInsertId();

            // Update response for success
            $response['success'] = true;
            $response['audio_id'] = $audioId;
        } catch(PDOException $e) {
            $response['error'] = 'Database error: ' . $e->getMessage();
            // Optional: Delete the uploaded file if database save fails
            unlink($targetFilePath);
        }
    } else {
        $response['error'] = 'Failed to move uploaded file';
    }
} else {
    $response['error'] = 'No file uploaded or upload error occurred';
}

// Send JSON response to frontend
echo json_encode($response);
?>

4. Retrieve and Display Audio #

Create a PHP file (e.g., get_audio.php) to fetch audio from the database and display it. You can pass the audio ID via a query parameter:

<?php
// Database configuration (same as action.php)
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_user';
$password = 'your_db_password';

$audioId = isset($_GET['id']) ? (int)$_GET['id'] : 0;

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

        // Fetch the audio file path from database
        $stmt = $pdo->prepare("SELECT file_path FROM audio_records WHERE id = :id");
        $stmt->bindParam(':id', $audioId);
        $stmt->execute();
        $audio = $stmt->fetch(PDO::FETCH_ASSOC);

        if($audio) {
            // Display the audio player
            echo '<audio class="veepoRec" controls="controls" src="' . htmlspecialchars($audio['file_path']) . '"></audio>';
        } else {
            echo 'Audio not found';
        }
    } catch(PDOException $e) {
        echo 'Error retrieving audio: ' . $e->getMessage();
    }
} else {
    echo 'Invalid audio ID';
}
?>

To use this, you can link to it like: get_audio.php?id=123 (replace 123 with the actual audio ID from your database).

Notes for Production #

• File Validation: Add more checks (like max file size) to prevent abuse.
• Security: Use htmlspecialchars() when outputting file paths to prevent XSS attacks.
• Database Schema: Make sure your audio_records table exists (create it with columns like id (INT, AUTO_INCREMENT, PRIMARY KEY), file_path (VARCHAR), upload_date (DATETIME)).
• HTTPS: Always use HTTPS for audio uploads/retrievals to protect user data.

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