We help IT Professionals succeed at work.

MySQL: Convert BLOB to mp3 audio file

digitron
digitron used Ask the Experts™
on
This should be easy, but after searching for answers, it looks confusing:

I have an on line MySQL database with many small audio files stored as BLOB fields. What I want to do is display a list of the files and when the user clicks  a record in the grid, the file stored in the same record as the viewed record plays. I think I can handle the playing of the audio--I've got that working.

What I DON'T know how to do is convert the BLOB data field to an MP3 or WAV file to play. Right now, the stored file is MP3, but I could change it to WAV or anything else.

I thought that there was an SQL command that could extract the data from the BLOB and in effect place it in a file or variable of the original file type. That is, if a jpg file was stored, it would be converted back to jpg. Or in my case, the stored MP3 file will be converted back so that it can be played.

QUESTION: How do I extract the mp3 file data from the BLOB record? Any additional comments about how to play it would be welcome. Especially regarding how to define a variable to store the converted data. Also, once the file is played, the temp data can be deleted, so if the extracted mp3 data is stored as a physical file, I would like to know how to delete it. Hoever, it does seem that the extracted data would be a memory variable, since that is what will be played.

These are all small sound files (there are a few thousand of them), and the point is that the user can select the file to hear by clicking on the record in a data grid, or repeater, or whatever.

Thanks
JS
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016

Commented:
To a certain extent it will matter how you stored the files in the data base.  Have you properly escaped them or base64-encoded the binary data? If not, be aware that some of them will probably not come out the same way they went it :-(

In future designs of this sort, you might want to use the server file system to hold the large "blob" things. and just keep a pointer to the URL in the data base.  You will see MUCH better overall performance from that design pattern.

Here is how I would try to start development of your app.  SELECT the row with the audio-blob you want and use file_put_contents() to write the audio-blob onto the server with the MP3 file extension, then produce a link to the blob file.  Click the link and see if the audio plays.  Once you have that part working we can go on to the other parts.

Author

Commented:
Thank you--I will try this. My weakness is not knowing the commands (SQL and otherwise) that are available for this.

I'm actually trying to duplicate for on line use, an application that I developed with uniPaaS (4GL - type language), which does very much what you suggest. In my program, the BLOB from the selected row is extracted to an MP3 file and then my program points to the extracted file and plays it. This 4GL language has  "File to BLOB" and " and "BLOB to file" functions which do pretty much what they sound like, and it works fine using uniPaaS.

The other part of your suggestion is practical--if what you mean is to dump all the MP3 files onto the server and just point to them with a link stored in the data file. I just want to make sure that users can find and download all the audio files using browser tools like Download Them All.

I will try the file_put_contents() suggestion. Here I must show my lack of knowledge. This function (file_put....): is it a Java function? PHP? VB?

Thanks,
JS
Most Valuable Expert 2011
Top Expert 2016
Commented:
file_put_contents() is a PHP function.  All of the PHP functions are documented in the PHP online manual - it is very helpful.  Example here:
http://us.php.net/manual/en/function.file-put-contents.php

This is untested and kind of "pidgin" code, but hopefully it will illustrate the moving parts of an app that will help you do the blob-to-file stuff.  The name of the file you wanted would come to this script in the URL like this:

http://this_script.php?a=my_audio_file_name
<?php // RAY_mysql_example.php
error_reporting(E_ALL);

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$safe_a = mysql_real_escape_string($_GET["a"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
$sql = "SELECT filename, audioblob FROM my_table WHERE filename='$safe_a' LIMIT 1";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE AND SHOW HOW MANY ROWS OF RESULTS WE GOT (HINT - LIMIT 1)
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
   echo "<br/>QUERY FOUND NO DATA: ";
   echo "<br/>$sql <br/>";
}
else
{
   echo "<br/>QUERY FOUND $num ROWS OF DATA ";
   echo "<br/>$sql <br/>";
}




// IF WE GOT DATA
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-fetch-assoc.php
while ($row = mysql_fetch_assoc($res))
{
   if (file_put_contents($row["filename"], $row["audioblob"]))
   {
       echo "<a target=\"_blank\" title=\"{$row["filename"]}\" href=\"{$row["filename"]}\">Audio</a>" . PHP_EOL;
   }
   else
   {
       echo "ERROR: CANNOT WRITE TO {$row["filename"]}" . PHP_EOL;
   }
}

Open in new window

Author

Commented:
This helps somewhat. Thanks.

Author

Commented:
Hello. While I very much appreciate the help that was given in the solution, it did not resulve my issue. Please understand that I'm not sure that there is an answer of the type I'm looking for. However, if there is not, then an answer to my question that would inform me of that fact would be an answer deserving a grade of A, because I would then be able to close my research into the problem and take a different approach.

Also, the code provided--which was very generous--is much more than I require or can use. I tried to make it clear that my data is on line and working properly, and that I can connect to it. So all the code relating to setting up and accessing a database does not apply.

All I have been trying to find is if there is a way to extract a BLOB data field out to its original form (file format) or to extract data as a certain file type.

Thanks again,
JS