View previous topic :: View next topic |
Author |
Message |
phirez -
Joined: 22 Apr 2006 Posts: 57
|
Posted: Fri Jul 20, 2007 6:51 pm Post subject: Blobs and audio |
|
|
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 |
|
|
TRUSTAbyss -
Joined: 29 Oct 2003 Posts: 3752 Location: USA, GA
|
Posted: Fri Jul 20, 2007 7:31 pm Post subject: |
|
|
I think you have to use FILE for that but I'm not sure. I'm still learning that stuff. |
|
Back to top |
|
|
aprelium -
Joined: 22 Mar 2002 Posts: 6800
|
Posted: Sun Jul 22, 2007 1:47 am Post subject: Re: Blobs and audio |
|
|
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 |
|
|
phirez -
Joined: 22 Apr 2006 Posts: 57
|
Posted: Tue Jul 24, 2007 4:15 pm Post subject: |
|
|
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 |
|
|
rrinc -
Joined: 24 Feb 2006 Posts: 725 Location: Arkansas, USA
|
Posted: Tue Jul 24, 2007 5:01 pm Post subject: |
|
|
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 |
|
|
phirez -
Joined: 22 Apr 2006 Posts: 57
|
Posted: Tue Jul 24, 2007 5:16 pm Post subject: |
|
|
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 |
|
|
aprelium -
Joined: 22 Mar 2002 Posts: 6800
|
Posted: Wed Jul 25, 2007 2:32 pm Post subject: |
|
|
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 |
|
|
phirez -
Joined: 22 Apr 2006 Posts: 57
|
Posted: Wed Jul 25, 2007 2:56 pm Post subject: |
|
|
Thanks, for the help! |
|
Back to top |
|
|
badai -
Joined: 24 Apr 2003 Posts: 82
|
Posted: Thu May 01, 2008 8:08 am Post subject: |
|
|
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 |
|
|
badai -
Joined: 24 Apr 2003 Posts: 82
|
Posted: Thu May 01, 2008 8:14 am Post subject: |
|
|
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 |
|
|
stevensaheb -
Joined: 14 Nov 2008 Posts: 1
|
Posted: Wed Nov 26, 2008 11:40 am Post subject: |
|
|
You can simply upload it and then can play that song by using the query string. |
|
Back to top |
|
|
|
|
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
|
|