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

rptutAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
warbaby67Commented:
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.
0
 
rptutAuthor Commented:
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.
0
 
rptutAuthor Commented:
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.
0
 
nasirbestCommented:
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

0
All Courses

From novice to tech pro — start learning today.