Blobs and audio

phirez

How do I store audio .mp3 files as MySQL BLOBs?
How would I access the .mp3 files from a server-side language? (Preferably Php or Ruby w/wo rails)

TRUSTAbyss

I think you have to use FILE for that but I'm not sure. I'm still learning that stuff.

aprelium

phirez,
Could you please explain us what you want to do? How are these .mp3 files going to be put in the database (who/what will upload them there)?
Next, do you want them to be played back from the database?

phirez

A BLOB column is similar to a TEXT column except a TEXT column holds text (non-binary data) and a blob holds binary data. Since mp3 files are binary data, I thought I could store the raw bytecode (binary data) of these files in a BLOB column ( I've seen the BLOB column mostly demostarted with images ).
I was wondering whether I could write/read the mp3 data from php. Possibly from a "fake" directory structure (Like xpath for databases) or another method that allows me to play/upload the mp3 data directly from/to the database.
If this should be placed in "PHP issues" feel free to move it.
(This is not urgent. This problem is more or less a learning experience)
Thanks.

rrinc

I don't work with databases much, but shouldn't you be able to just put the data inside the file into the database, then read it back out, output it, and send the appropriate headers?

phirez

So would this work:


INSERT INTO table( BLOBcolumn) VALUES( test.mp3)

Or do I have to use so sorta of function that reterives the binary data, Like This:


<?php 
$thedata =  SomeClass.toBinary(test.mp3)

$db->query( 'INSERT INTO table( BLOBcolumn) VALUES( $thedata)' )
?>

Someclass meaning a class (that I don't know about) that handles bytecode.
sorry, I'm still learning php.

aprelium

phirez,
The code should look like:


<?php 
// read the file contents and put them inside a string $thedata
$thedata =  file_get_contents("test.mp3");

// We use mysql_real_escape_string to escape the file contents string
// which may contain non printable characters or special signs
mysql_query("INSERT INTO table(BLOBcolumn) VALUES ('" + mysql_real_escape_string($thedata) + "')");
?>

The code is missing error checks (on mysql_query) but should give you the main idea.

phirez

Thanks, for the help!

badai

database (mysql):
the db name is BLOB (see sample php below)

CREATE TABLE IF NOT EXISTS `test_blob` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL,
  `description` varchar(128) NOT NULL,
  `type` varchar(32) NOT NULL,
  `size` bigint(20) unsigned NOT NULL,
  `data` longblob NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

id is the ID, you can also use auto inc seq.
name is the file name
description is the file description
type is the mime type, will be detected by web server when uploading
size is the file size, will be detected by php / web server (i'm not sure)
data is the ... , well the data as longblob
sample upload page:

<form enctype="multipart/form-data" action="insert.php" method="post" name="changer">
<input name="MAX_FILE_SIZE" value="33000000" type="hidden">
File to upload: <input name="upload_file" accept="*" type="file"> (max size is 32M)<br>
File Description: <input type="text" name="desc"><br>
<input value="Submit" type="submit">

sample php file that process the uploaded file:

<?
// Create MySQL login values and 
// set them to your login information.
$username = "root";
$password = "";
$host = "localhost";
$database = "BLOB";

// Make the connect to MySQL or die
// and display an error.
$link = mysql_connect($host, $username, $password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

// Select your database
mysql_select_db ($database);  

// Make sure the user actually 
// selected and uploaded a file
//if (isset($_FILES['upload_file']) && $_FILES['upload_file']['size'] > 0 && isset($_POST['desc'])) { 
if (isset($_FILES['upload_file']) && $_FILES['upload_file']['size'] > 0) { 

$name = $_FILES['upload_file']['name'];
	  $desc = $_POST['desc'];
	  $type = $_FILES['upload_file']['type'];
	  $size = $_FILES['upload_file']['size'];

// Temporary file name stored on the server
      $tmpName  = $_FILES['upload_file']['tmp_name'];  

// Read the file 
      $fp      = fopen($tmpName, 'rb');
      $data = fread($fp, filesize($tmpName));
      $data = addslashes($data);
      fclose($fp);

// Create the query and insert
      // into our database.
      $query = "INSERT INTO TEST_BLOB ";
      $query .= "(NAME, DESCRIPTION, TYPE, SIZE, DATA) VALUES ('$name', '$desc', '$type', '$size', '$data')";
//	  $query .= "(data) VALUES ('$data')";
      if ($results = mysql_query($query, $link))
      {
       // Print results
       print "Thank you, your file has been uploaded.<br>";
	   print_r($_FILES);
	  }
	  else
	  {
	   die('Invalid query: ' . mysql_error());
	  }
}
else {
   print "No image selected/uploaded: ";
   switch($_FILES['image']['error']){
    case 1:
	 echo "The uploaded file exceeds the upload_max_filesize directive in php.ini.";
	 break;
	case 2: 
	 echo "The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form.";
	 break;
	case 3: 
	 echo "The uploaded file was only partially uploaded. ";
	 break;
	case 4: 
	 echo "No file was uploaded.";
	 break;
	case 6: 
	 echo "Missing a temporary folder.";
	 break;
	case 7:
	 echo "Failed to write file to disk.";
	 break;
	case 8:
     echo "File upload stopped by extension.";
	 break;
	default:
	 echo "Unknown error. Error code: ";
	 echo $_FILES['image']['error'];
   }
}

// Close our MySQL Link
mysql_close($link);
?>

now you can check your mysql if it sucessfully upload the file
to browse/downoad from blob:

<?
// Create MySQL login values and 
// set them to your login information.
$username = "root";
$password = "";
$host = "localhost";
$database = "BLOB";

// Make the connect to MySQL or die
// and display an error.
$link = mysql_connect($host, $username, $password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

// Select your database
mysql_select_db ($database);  

$query = "SELECT * FROM TEST_BLOB";
if ($results = mysql_query($query, $link))
{
 $num=mysql_numrows($results);
 if ($num > 0)
 {
  $file_id = array();
  $file_name = array();
  $file_desc = array();
  $file_type = array();
  $file_size = array();
  echo "<table><tr><th>name</th><th>decription</th<th>type</th><th>size</th></tr>";
  for ($i = 0; $i < $num; $i++)
  {
   $file_id[$i] = mysql_result($results,$i,"ID");
   $file_name[$i] = mysql_result($results,$i,"NAME");
   $file_desc[$i] = mysql_result($results,$i,"DESCRIPTION");
   $file_type[$i] = mysql_result($results,$i,"TYPE");
   $file_size[$i] = mysql_result($results,$i,"SIZE");
   echo "<tr><td><a href=\"get_file.php?id=";
   echo $file_id[$i];
   echo "&type=";
   echo $file_type[$i];
   echo "&name=";
   echo $file_name[$i];
   echo "&size=";
   echo $file_size[$i];
   echo "\">";
   echo $file_name[$i];
   echo "</a></td><td>";
   echo $file_desc[$i];
   echo "</td><td>";
   echo $file_type[$i];
   echo "</td><td>";
   echo $file_size[$i];
   echo "</td></tr>";
  }
  echo "</table>";
 }
 else
  echo "No data found";
}
else
{
 die('Invalid query: ' . mysql_error());
}
mysql_close($link);
?>

finally, the "meta file":

<?php
$file_id = $_GET['id'];
$file_type = $_GET['type'];
$file_name = $_GET['name'];
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: public");
header("Content-Description: File Transfer");
header("Content-Type: ".$file_type);
header("Content-Disposition: attachment; filename=\"$file_name\"");
//header("Content-Disposition: filename=\"$file_name\"");
header("Content-Transfer-Encoding: binary");
//header("Content-Length: ".$file_size);

$oDatabase = mysql_connect("localhost", "root", ""); 
mysql_select_db("BLOB", $oDatabase); 
$sQuery = "SELECT DATA FROM TEST_BLOB WHERE id = ".$file_id; 
$oResult = mysql_query($sQuery, $oDatabase); 
$oRow = mysql_fetch_array($oResult); 
$sJpg = $oRow["DATA"]; 
echo $sJpg; 
?>

this meta file will force download. you can modify it to display the file in brwoser instead, but when the file cannot be display, browser will ask you to download, with the filename same as the meta file (whatever.php).
i still figuring out how to solve this (can download with proper file name, or display in browser if possible)

badai

some modification to php.ini and my.cnf
max_execution_time 300 --> in case it take too long to upload
post_max_size 64M -> max file to upload
upload_max_filesize 64M -> max file to upload
memory_limit 64M -> -> max file to upload
my.cnf
max_allowed_packet = 64M - -> max file to upload
of course, 64M is big. you should set the limit around 16M, the in the HTML set the limit to 8M.

stevensaheb

You can simply upload it and then can play that song by using the query string.