php - Mysql data backup converted to string -
note: main thing want know how stop converting integers , bits string using php. dont want use mysqldump many servers not giving access shells , had tested it. thats why, using php
i taking backup of datbases on live server reference link https://davidwalsh.name/backup-mysql-database-php problem when open file backup, saw whole data converted in string. so, lets have null in date field, gets converted making date 0000-00-00, bit value 0 converts 1.
i used mysqldump , has issues put on other question: mysqldump working on local not on godaddy server
the function using 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 $table) { $result = $mysqli->query('select * '.$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"; ($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 ".$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 @ last line] ....p.s. should 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 solved using following way. here, $tables , array of tables of database.i used link 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 250 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 mapped text , 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 $table) { $result = $mysqli->query('select * '.$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"; ($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 ".$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 @ last line] ....p.s. should 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 warning given mysql when taking database backup $content=str_replace("warning","-- warning",$content); $handle = fopen($filename.'.sql','w+'); fwrite($handle,$content); fclose($handle); }
Comments
Post a Comment