?
Solved

MySQL: Convert BLOB to mp3 audio file

Posted on 2010-03-27
6
Medium Priority
?
4,576 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:digitron
  • 3
  • 2
5 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 28878309
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
 

Author Comment

by:digitron
ID: 28935594
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 28974308
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
 

Author Closing Comment

by:digitron
ID: 31707965
This helps somewhat. Thanks.
0
 

Author Comment

by:digitron
ID: 30847318
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Super Bowl is just days away. Millions of advertising dollars will be spent in just a few hours to drive people to websites around the globe. Optimizing your site in anticipation of a big event like this (and the traffic surges that follow) will…
This article is about the challenges faced by Android app developers.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question