?
Solved

Extracting all binary files from MySQL at one stroke!

Posted on 2003-03-28
5
Medium Priority
?
270 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
  • 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article discusses how to implement server side field validation and display customized error messages to the client.
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

621 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