Backup MySql table in a text file.
Some time we want to back up our website for security region or we want to change our hosting. So we are writing a code which back up MySql table to a text file. We use PHP to connect Mysql server and create and write data to a text file. You can use this file to restore your data into MySql table whenever you want to restore database.Steps to backup MySql table in a file.
1. First of all we have to connect MySql database server using PHP.
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PW', 'uyw487hks');
define('DB_DB', 'databaseName');
$conn=mysql_connect (DB_SERVER,DB_USERNAME,DB_PW)
or die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db (DB_DB);
2. We create to variable $output, $col_name, $i and $tabel. $table variable assigned value of table name and $output variable is used to hold data of MySql table write it to text file. Variable $col_name only used to find how many column in table and there name to use these accurately when we want to restore database table next time, So there should not be any confusion . Variable $i is a counter.
$table="data_table"; $output =""; $col_name=""; $i = 0;
3. This code is used to find columns and there names. We can use these name in future during restore database table.
$result = mysql_query("SHOW COLUMNS FROM ".$table."");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$col_name .= $row['Field']."; ";
$i++;
}
}
4. Next code read MySql table and store it into variable $output.
$values = mysql_query("SELECT * FROM ".$table.""); while ($rowr = mysql_fetch_row($values)) { for ($j=0;$j<$i;$j++) { $output .= $rowr[$j].";"; $show_output .= $rowr[$j].""; } $output .= "\n"; }
5. Now we create textFile.txt and open it to write data stored in variable $output.
$filename = "textFile.txt";
$fh = fopen($filename, 'w') or die("can't open file");
fwrite($fh, $output);
fclose($fh);
