MySQL: Convert BLOB to mp3 audio file

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
digitronAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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.
0
digitronAuthor 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
0
Ray PaseurCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
digitronAuthor Commented:
This helps somewhat. Thanks.
0
digitronAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.