Export table to sql

PHP
1

Good day to you,
I have added an options to my db production site which is to back up the table into csv file, see code below.

Now I would like to be able to export to sql file, how would I do it without using phpMyadmin , I would like to use a function like the following.

<?php
$host = 'localhost';
$user = '...';
$pass = '....';
$db = '....';
$table = $_GET[table];
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field']."; ";
$i++;
}
}
$csv_output .= "\
";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j]."; ";
}
$csv_output .= "\
";
}

$filename = "Backup__".$table."__".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
echo "Exporting...";
?>

Thanks!

2

I usually use something like this, rather than trying to dance around with PHP and SQL queries

	/**
	 * get the current timestamp in UTC
	 * @return DateTime
	 */
	function get_current_timestamp(){
		return new DateTime("now", new DateTimeZone("UTC"));
	}
	
	/**
	 * backup the database
	 * @param string $dir subdirectory to place the backup
	 * @return bool operation successful?
	 */
	function backup_db($dir=""){
		include "_config.inc.php";  //file for db login info. you'll probably do this differently
		include "inc/dbconn.inc.php"; //file for db connection info, creates the linkID.  You'll probably do this differently

		$path=false;  //the path for MySQL
		$result=false; //did the dump complete?
			
		$backup_file = $db_name.'_'.get_current_timestamp()->format('Y-m-d--H-i-s').'.sql';  //create the filename of the sql dump file
			
		if(substr(strlen($dir)-1, strlen($dir))!='/'){ $dir.='/'; } //insert a slash if there isn't one
		
		
		//get the MySQL program directory
		$result_path = mysql_query("SELECT @@basedir",$dbconn); //$dbconn is my database linkID.  Yours may be named different, or you might not use it at all.
		if($result_path){
			if($row = mysql_fetch_array($result_path, MYSQL_ASSOC)){
				$path=$row['@@basedir'];
			}
		}

			
		if($path){
			$path=str_replace("\\\\","/", $path); //use the correct slashes for the file path

			(strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') ? $path.="/bin/mysqldump.exe" : $path.="/bin/mysqldump"; //this checks for windows vs linux, and determines what path to the mysqldump executable to use
			
			$command = "$path --opt --host=$db_host --user=$db_user --password=$db_pass --no-create-db --skip-add-locks --skip-set-charset --disable-keys --databases $db_name > $dir$backup_file"; //this is the command to create the dump file.  The PHP variables are either from above or from the file with db info.  You can look at MySQL's site for sytax for the mysqldump syntax
			system($command, &$result); //this executes the SQL dump
			($result===0) ? $result=true : $result=false; //was the dump successful?
		}

		return $result;
	}

I made some changes for using this code outside of the framework I use, but it should work.