FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Does mySQL support BLOB? How?





kk_loke86
Hello, wanna ask whether mySQL support BLOB storage? How to do it?
nmoutaa
Hi,
Yes, I did it last summer Smile in a project, but I was working in MySQL 5.
Can you check whether you version of My SQL is >= 5.0 Smile
An other thing, I did it by code in C# and by EMS MySQL Manager Wink

So good luck.
Stubru Freak
kk_loke86 wrote:
Hello, wanna ask whether mySQL support BLOB storage? How to do it?


You just have to use Blob as the type of your field.
badai
you also need to configure my.cnf and php.ini.

you need to allow more than 1M transfer in my.cnf, then in php.ini usually the default value is big enough (around 32M i think), but you need to increase max execution time for php in case it take longer than default value to upload file.

the hard part it to list back the file and download it. all the example i found on the web just teach how to upload.

maybe when i'm free i will post the code here. it's 1:35am local time now.
badai
changes in php.ini:

Code:
max_execution_time

time it will take to upload file from slowest connection with file size of maximum allowed

Code:
post_max_size
upload_max_filesize
memory_limit

these 3 is maximum size of upload file. you should double it, then set your file size limit in your html form

changes in my.cnf:

Code:
max_allowed_packet

maximum size of upload file. you should double it, then set your file size limit in your html form

sample mysql table. db name is BLOB if you want to use my sample script:

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 ;


sample html:

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">


the value of MAX_FILE_SIZE should be set to control the, well... MAX_FILE_SIZE

sample script to process upload file (see html above to check the filename)

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 should be able to see the file in phpmyadmin

this script will list all record. you need another file to download the file. see next script:

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);
?> 



this script is the "meta file" to download the file in blob. check above script for its' filename:

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;
?>


maybe once you understand it, you can beautify it to be more easy to understand, and post it back here.
Related topics
Does frihost support jsp web apps?
Does mysql have a limited!
Does the php support utf8?
swf support
[MySQL] Client does not support authentication protocol
Warning: mysql_connect(): Client does not support authentica
PHPNuke installation problem
Error when connecting to MySQL
Does FtiHost Support Cron Jobs?
Does frihost support ffmpeg ???
Does the mysql support GBK coding?
Does Frihost support this CMS Alfresco?
Does frihost support free static ip hosting??
does frihost support ssh?
Reply to topic    Frihost Forum Index -> Scripting -> Others

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.