Mysql data backup converted to string

Question

Note: The main thing which I want to know is how to stop converting integers and bits to string using php. I dont want to use mysqldump as many Servers are not giving access to shells and I had tested it. Thats why, I am using PHP

I am taking the backup of my datbases that are on live server with reference to link https://davidwalsh.name/backup-mysql-database-php the problem is when i open the file which i get as backup, i saw the whole data is converted in string. So, lets say i have null in date field, it gets converted to making date as 0000-00-00, bit value 0 converts to 1.

I used mysqldump and it also has issues which i put on other question: mysqldump working on local but not on godaddy server

The function i am using is as follows:

function Export_Database($host,$user,$pass,$name,  $tables, $backup_name=false )
    {
        $mysqli = new mysqli($host,$user,$pass,$name); 
        $mysqli->select_db($name); 
        $mysqli->query("SET NAMES 'utf8'");
        foreach($tables as $table)
        {
            $result         =   $mysqli->query('SELECT * FROM '.$table);  
            $fields_amount  =   $result->field_count;  
            $rows_num=$mysqli->affected_rows;     
            $res            =   $mysqli->query('SHOW CREATE TABLE '.$table); 
            $TableMLine     =   $res->fetch_row();
            $content        = (!isset($content) ?  '' : $content) . "\n\n".$TableMLine[1].";\n\n";

            for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0) 
            {
                while($row = $result->fetch_row())  
                { //when started (and every after 100 command cycle):
                    if ($st_counter%100 == 0 || $st_counter == 0 )  
                    {
                            $content .= "\nINSERT INTO ".$table." VALUES";
                    }
                    $content .= "\n(";
                    for($j=0; $j<$fields_amount; $j++)  
                    { 
                        $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) ); 
                        if (isset($row[$j]))
                        {
                            $content .= '"'.$row[$j].'"' ; 
                        }
                        else 
                        {   
                            $content .= '""';
                        }     
                        if ($j<($fields_amount-1))
                        {
                                $content.= ',';
                        }      
                    }
                    $content .=")";
                    //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
                    if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) 
                    {   
                        $content .= ";";
                    } 
                    else 
                    {
                        $content .= ",";
                    } 
                    $st_counter=$st_counter+1;
                }
            } $content .="\n\n\n";
        }
        $folder = 'DB_Backup/';
        if (!is_dir($folder))
        mkdir($folder, 0777, true);
        chmod($folder, 0777);

        $date = date('m-d-Y-H-i-s', time()); 
        $filename = $folder."db-backup-".$date; 

        $handle = fopen($filename.'.sql','w+');
        fwrite($handle,serialize($content));
        fclose($handle);
    }

I used mysqldump but its working fine on local only. Servers are not giving access to shells @e4c5 — Amanjot
– Amanjot, Commented Aug 8, 2016 at 10:58
I am working on a project where i need to give the user the access to whenever he wants to take database backup, he can take it, on his website by clicking some button @e4c5 — Amanjot
– Amanjot, Commented Aug 8, 2016 at 11:26
Please check my question again and i have highlighted my problem. I dont want to use mysqldump. @e4c5 — Amanjot
– Amanjot, Commented Aug 17, 2016 at 5:45

Amanjot · Accepted Answer · 2016-08-17 06:03:37Z

I solved using the following way. Here, $tables is and array of tables of your database.I used the link for reference http://php.net/manual/en/mysqli-result.fetch-field-direct.php

function Export_Database($host,$user,$pass,$name,  $tables, $backup_name=false )
        {
            //value greater than 250 are strings
            $mysql_data_type_hash = array(
                1=>'tinyint',
                2=>'smallint',
                3=>'int',
                4=>'float',
                5=>'double',
                7=>'timestamp',
                8=>'bigint',
                9=>'mediumint',
                10=>'date',
                11=>'time',
                12=>'datetime',
                13=>'year',
                16=>'bit',
                //252 is currently mapped to all text and blob types (MySQL 5.0.51a)
                253=>'varchar',
                254=>'char',
                246=>'decimal'
            );


            $mysqli = new mysqli($host,$user,$pass,$name); 
            $mysqli->select_db($name); 
            $mysqli->query("SET NAMES 'utf8'");
            foreach($tables as $table)
            {
                $result         =   $mysqli->query('SELECT * FROM '.$table);  
                $fields_amount  =   $result->field_count;  
                $rows_num=$mysqli->affected_rows;     
                $res            =   $mysqli->query('SHOW CREATE TABLE '.$table); 
                $TableMLine     =   $res->fetch_row();
                $content        = (!isset($content) ?  '' : $content) . "\n\n".$TableMLine[1].";\n\n";

                for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0) 
                {
                    while($row = $result->fetch_row())  
                    { 

                //when started (and every after 100 command cycle):
                        if ($st_counter%100 == 0 || $st_counter == 0 )  
                        {
                                $content .= "\nINSERT INTO ".$table." VALUES";
                        }
                        $content .= "\n(";
                        for($j=0; $j<$fields_amount; $j++)  
                        { 
                            $datatype=$result->fetch_field_direct($j)->type;
                            //echo $mysql_data_type_hash[$datatype]."<br/>";
                            $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) ); 
                            if (isset($row[$j]) && trim($row[$j])!=null)
                            {
                                if(($datatype>=10 && $datatype<=13) || $datatype>250)
                                {
                                    $content .= '"'.$row[$j].'"' ; 
                                }
                                else
                                {
                                    $content .= $row[$j] ; 
                                }
                            }
                            else 
                            {   
                                $content .= 'NULL';
                            }     
                            if ($j<($fields_amount-1))
                            {
                                    $content.= ',';
                            }      
                        }
                        $content .=")";
                        //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
                        if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) 
                        {   
                            $content .= ";";
                        } 
                        else 
                        {
                            $content .= ",";
                        } 
                        $st_counter=$st_counter+1;
                    }
                } $content .="\n\n\n";
            }

            $folder = 'DB_Backup/';
            if (!is_dir($folder))
            mkdir($folder, 0777, true);
            chmod($folder, 0777);

            $date = date('m-d-Y-H-i-s', time()); 
            $filename = $folder."db-backup-".$date; 

            //Commenting The Warning Given By Mysql When Taking Database Backup
            $content=str_replace("Warning","-- Warning",$content);

            $handle = fopen($filename.'.sql','w+');
            fwrite($handle,$content);
            fclose($handle);
        }

Collectives™ on Stack Overflow

Mysql data backup converted to string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related