asked on

Need PHP script help

I want to export out an entire MySQL db to a file through a browser (instead of saving to the server). I don't have access to MyPHPAdmin or anything, all I have is the database username and password. I essentially want to move this db to another server. I found the following code that does this for a single table, but how do I do it for the whole database? Is there an easier way to accomplish what I am trying to accomplish?

Thanks

<?php
function export_excel_csv()
{
    $conn = mysql_connect("localhost","root","");
    $db = mysql_select_db("database",$conn);
   
    $sql = "SELECT * FROM table";
    $rec = mysql_query($sql) or die (mysql_error());
   
    $num_fields = mysql_num_fields($rec);
   
    for($i = 0; $i < $num_fields; $i++ )
    {
        $header .= mysql_field_name($rec,$i)."t";
    }
   
    while($row = mysql_fetch_row($rec))
    {
        $line = '';
        foreach($row as $value)
        {                                           
            if((!isset($value)) || ($value == ""))
            {
                $value = "t";
            }
            else
            {
                $value = str_replace( '"' , '""' , $value );
                $value = '"' . $value . '"' . "t";
            }
            $line .= $value;
        }
        $data .= trim( $line ) . "n";
    }
   
    $data = str_replace("r" , "" , $data);
   
    if ($data == "")
    {
        $data = "n No Record Found!n";                       
    }
   
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=reports.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    print "$headern$data";
}
?>

Open in new window

ASKER CERTIFIED SOLUTION

hielo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

warbaby67

Do you want sql, csv, or and xls format for the file? Security is an issue to consider when you are dumping the contents of a db as a stream to a web browser.

rptut

ASKER

A .sql file would be best, but either of the other formats would work.

Regarding secuirty, I just need to dump it once then I'll take the script down. I'm essentially moving an old site to a new server and I don't have any information on the old server other than an FTP login and the username/pass to the database.

I tried the David Walsh script but I'm either doing something wrong or don't have sufficient permission for the script to write the file to the server.

rptut

ASKER

Nevermind... as I was typing the last post I realized I should just try the Walsh script on a different server where I know I have full access. It worked.

nasirbest

try following code, for more option see mysqldump help

<?php
    $user = 'myuser';
    $pass = 'mypass';
    $db   = 'mydb';

    $database_script = shell_exec("/usr/bin/mysqldump --user=$user --password=$pass $db");

    header("Cache-Control: public");
    header("Content-Description: File Transfer");
    header("Content-Disposition: attachment; filename=$db.sql");
    header("Content-Type: text/plain");
    header("Content-Transfer-Encoding: binary");

    echo $database_script;
?>

Open in new window