Blobs and audio

 
Post new topic   Reply to topic    Aprelium Forum Index -> Databases
View previous topic :: View next topic  
Author Message
phirez
-


Joined: 22 Apr 2006
Posts: 57

PostPosted: Fri Jul 20, 2007 6:51 pm    Post subject: Blobs and audio Reply with quote

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)
Back to top View user's profile Send private message
TRUSTAbyss
-


Joined: 29 Oct 2003
Posts: 3752
Location: USA, GA

PostPosted: Fri Jul 20, 2007 7:31 pm    Post subject: Reply with quote

I think you have to use FILE for that but I'm not sure. I'm still learning that stuff.
Back to top View user's profile Send private message Visit poster's website
aprelium
-


Joined: 22 Mar 2002
Posts: 6800

PostPosted: Sun Jul 22, 2007 1:47 am    Post subject: Re: Blobs and audio Reply with quote

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?
_________________
Support Team
Aprelium - http://www.aprelium.com
Back to top View user's profile Send private message Send e-mail
phirez
-


Joined: 22 Apr 2006
Posts: 57

PostPosted: Tue Jul 24, 2007 4:15 pm    Post subject: Reply with quote

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.
Back to top View user's profile Send private message
rrinc
-


Joined: 24 Feb 2006
Posts: 725
Location: Arkansas, USA

PostPosted: Tue Jul 24, 2007 5:01 pm    Post subject: Reply with quote

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?
_________________
-Blake | New Server :D
SaveTheInternet
Soy hispanohablante. Puedes contactarme por mensajes privados.
Back to top View user's profile Send private message Visit poster's website Yahoo Messenger MSN Messenger
phirez
-


Joined: 22 Apr 2006
Posts: 57

PostPosted: Tue Jul 24, 2007 5:16 pm    Post subject: Reply with quote

So would this work:

Code:

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

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

Code:

<?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.
Back to top View user's profile Send private message
aprelium
-


Joined: 22 Mar 2002
Posts: 6800

PostPosted: Wed Jul 25, 2007 2:32 pm    Post subject: Reply with quote

phirez,

The code should look like:

Code:

<?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.
_________________
Support Team
Aprelium - http://www.aprelium.com
Back to top View user's profile Send private message Send e-mail
phirez
-


Joined: 22 Apr 2006
Posts: 57

PostPosted: Wed Jul 25, 2007 2:56 pm    Post subject: Reply with quote

Thanks, for the help!
Back to top View user's profile Send private message
badai
-


Joined: 24 Apr 2003
Posts: 82

PostPosted: Thu May 01, 2008 8:08 am    Post subject: Reply with quote

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

Code:
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:
Code:
<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:
Code:
<?
// 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:
Code:
<?
// 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":

Code:
<?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)
Back to top View user's profile Send private message
badai
-


Joined: 24 Apr 2003
Posts: 82

PostPosted: Thu May 01, 2008 8:14 am    Post subject: Reply with quote

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.
Back to top View user's profile Send private message
stevensaheb
-


Joined: 14 Nov 2008
Posts: 1

PostPosted: Wed Nov 26, 2008 11:40 am    Post subject: Reply with quote

You can simply upload it and then can play that song by using the query string.
Back to top View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Aprelium Forum Index -> Databases All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB phpBB Group