sql query to download BLOB Data from mysql database-CodePudding

I am looking for a way to load blob data from the table faq_attachment into a directory of my choice using a sql command in the MYSQL Workbench. If necessary, the name can be composed of file_id filename Example: 61_image.png

I am unfortunately not a mysql expert…

thanks a lot

CodePudding user response：

This is a very crude example but it should work. There is no graceful error handling or safety checks. It will overwrite existing files so make sure $file_path is writable and empty.

<?php

// Report all PHP errors
error_reporting(E_ALL);

$db_name = '';
$db_host = 'localhost';
$dsn = "mysql:dbname=$db_name;host=$db_host";
$user = '';
$password = '';

// the file path must be writable by the current user
$file_path = 'D:\\tmp\\';

// Creates a PDO instance representing a connection to a database
$PDO = new PDO($dsn, $user, $password);
$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepares and executes an SQL statement and returns a PDOStatement object or false on failure.
$stmt = $PDO->query('SELECT `id`, `filename`, `content_type`, `content` FROM `faq_attachment`', PDO::FETCH_OBJ);

// iterates through the returned result set
foreach ($stmt as $file) {

    // prepare filename with full path to write to
    $filename = $file_path . str_replace(' ', '-', "{$file->id}_{$file->filename}");

    // writes the content from the BLOB to the given file
    file_put_contents($filename, $file->content);

}