?
Solved

Extracting all binary files from MySQL at one stroke!

Posted on 2003-03-28
5
Medium Priority
?
258 Views
Last Modified: 2006-11-17
Hi...All

I have been trying so long to extract all the binary files from MySQL database at one shot. I have this MySQL database that contains binary files inside BLOB data field. What I want to do is I want to retrieve all the files into my harddisk at one shot.

It can be in zip format or I specify a folder path then all the files are extracted to that folder. Can anybody tell me how to do this? I have been trying to figure out so long but still can't get the answer yet.

I tried to do looping after selecting all the files from database. the example is below. But after appearing the first download dialog box. The loop stops. Please suggest me how to slove the problem.

<?

     //Establish connection with database
     require_once("includes/db_connect.php");    
     
      $conn = db_connect();
 
     if (!$conn){
             echo "Could not connect to database server - please try later.";    
          exit;
     }
     
     $sql = "SELECT File,FileName,FileSize,FileType FROM File";
     
     $result = mysql_query($sql);
     
     if(!$result){
          echo "Error in retrieving files";
          exit;
     }
     
     //echo mysql_num_rows($result);
     //exit;
     
     for($i=0;$i < mysql_num_rows($result);$i++) {
     
     $data = mysql_result($result,0,"File");
     $name = mysql_result($result,0,"FileName");
     $size = mysql_result($result,0,"FileSize");
     $type = mysql_result($result,0,"FileType");


     header("Content-type: $type");
     header("Content-length: $size");
     header("Content-Disposition: attachment; filename= $name");
     header("Content-Description: PHP Generated Data");
     echo $data;
     
     }


?>

Thanks all.
0
Comment
Question by:InspireTech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
VGR earned 200 total points
ID: 8229714
no. It's almost correct but you can't loop while sending headers... One set of headers per page only...

Do you really want to user prompted on EACH file ? :)

As you've the filetype (like "image/jpeg" I suppose), just check the extension in the name, and write it to disk in "wb" format (binary).

Don't prompt the user, and don't use headers. They are for ONLINE browsing of a given BLOB contents, not for looping through them all.

So basically I suggest this (not tested, I never have BLOBs)

(please create beforehand a "fromblobs" directory in the current working directory )

<?

    //Establish connection with database
    require_once("includes/db_connect.php");    
   
     $conn = db_connect();
 
    if (!$conn){
            echo "Could not connect to database server - please try later.";    
         exit;
    }
   
    $sql = "SELECT File,FileName,FileSize,FileType FROM File";
   
    $result = mysql_query($sql);
   
    if(!$result){
         echo "Error in retrieving files";
         exit;
    }
   
    //echo mysql_num_rows($result);
    //exit;
   
    for($i=0;$i < mysql_num_rows($result);$i++) {
   
    $data = mysql_result($result,0,"File");
    $name = mysql_result($result,0,"FileName");
    $size = mysql_result($result,0,"FileSize"); // not used
    $type = mysql_result($result,0,"FileType"); // not used


    // now write the file to disk
    $fd=@fopen("./fromblobs/.$name",'wb');
    if ($fd) { // ok, push the data
      if (!fwrite($fd, $data)) {
        echo "Cannot write to file '$name'";
        exit;
      } // if written
      echo "Success, wrote file '$name'";
      fclose($fd);
    } else die("unable to create file '$name' !!!");
   
    } // for results (a while($res=mysql_fetch_array())  loop is more natural ;-)


?>
0
 

Author Comment

by:InspireTech
ID: 8232701
Hi VGR,

I am passionately thank you for giving me such an excellent answer to my quesion. Now I have finally solved the problem that I have been trying so long.Now,I do understand the usefulness of using forum.

Your answer did terrific help to me. I really thank you from the bottom of my heart. I am sure that your proficieny in PHP is far deep beyond more than others do. I am just a novice to PHP.

Thanks a lot
InspireTech
0
 
LVL 15

Expert Comment

by:VGR
ID: 8232993
i've no merit
I've no extensive knowledge of PHP either
I only try to keep things simple and as efficient to run as they're efficient to write down 8-)
0
 

Author Comment

by:InspireTech
ID: 8425970
Hi..

I have another problem when I test this on server. What I want to do is that at first I will ask the user where he wants to write all the binary files to. For example: he will say C:/Test/

Then at the next page I wish to write all the binary files to previously specified directory (C:/Test/). But the server doesn't seems to know where is C:/Test/. So I could not any file.

Do you have any idea what should I do to solve this?
0
 
LVL 15

Expert Comment

by:VGR
ID: 8426017
ok

the most probable trick is the slashes problem on your windows platform. Try either with forward slashes ('/Test') if the directory is under your DOCUMENT_ROOT (recommended) or with double-backwards slashes ('C:\\Test') - this should work.

For the user intput, you can use two techniques :
-either INPUT TYPE=text (I suppose that's what you have, thus the slashes problem)
-or a INPUT TYPE=file where people will have to select at least a file in the target directory. Then your script will take out the basename() to have the directory part. That's your target directory
0

Featured Post

WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

771 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