Here is simple MySQL table backup script. You can use it to easily create backup of your MySQL table, so you can play with that table without fear of losing your valuable data. First line will delete table named “tablename_backup” (if table with that name exists). Second line will create “tablename_backup” table using same structure as tablename is using. And third line will copy data from tablename to tablename_backup.

DROP TABLE IF EXISTS tablename_backup;
CREATE TABLE tablename_backup LIKE tablename;
INSERT tablename_backup SELECT * FROM tablename;

I already hear you asking, what about restoring of that backup. And here it is:

DROP TABLE IF EXISTS tablename_temp;
RENAME TABLE tablename TO tablename_temp,
tablename_backup TO tablename,
tablename_temp TO tablename_backup;

If there is table named “tablename_temp” we will delete it, and then we have to make some table name tumbling. We can use one command (renaming operations are done from left to right) to make 3 rename operations. First we are renaming table “tablename” to temporary table “tablename_temp”, then we are restoring our backup from “tablename_backup” to “tablename”, and at the end we are saving starting table as new backup “tablename_backup”…

Pretty simple and effective?

I showed you how to export your MySQL tables to MS Excel file, but that wasn’t real excel files we were creating. Actually you were getting CSV and HTML files which excel can read. These were textual files, and now I am gonna show you how to create binary excel file: [SOURCE CODE]



/*
Export MySQL to Excel (binary mode)
Author: Vlatko Zdrale, http://blog.zemoon.com

$excelRow = 1;
while($row = mysql_fetch_row($result)) {
$excelCol = 0;
foreach($row as $value) {
if (is_int($value))
excelWriteNumber($excelRow, $excelCol, $value);
else
excelWriteLabel($excelRow, $excelCol, $value);
$excelCol++;
}
$excelRow++;
}
excelFooter();
?>


I have to admit that this issue was killing me few weeks ago. Some folks gave me table with no primary key and with some duplicate rows. When I tried to update data in that table SQL Server didn’t know which record to update and all I was getting was error message. First thing I tried was to add TOP(1) after DELETE:

DELETE TOP(1) FROM MyTable WHERE MyColumn = 'something'

but unfortunatelly that didn’t work.

After almost hour of searching I found this solution:

SET ROWCOUNT 1
DELETE
FROM MyTable
WHERE (MyColumn = 'something')

and that worked for me. I hope it will save you some time.

In last post I showed you how to export from mysql to excel using php but result is plain, there is no style at all. Here I will show you how to add some style to your data. Here is simple PHP script you can use to export your data form MySQL table to HTML file Excel can read.

Download

/*
Export MySQL to Excel using PHP & HTML tables
Author: Vlatko Zdrale, http://blog.zemoon.com

Look but don't touch 
*/
	$dbHost	= 'myserver';			//	database host
	$dbUser	= 'myusername';		//	database user
	$dbPass	= 'mypassword';		//	database password
	$dbName	= 'mydatabase'; 		//	database name
	$dbTable = 'mytable';			// table name

	$connection = @mysql_connect($dbHost, $dbUser, $dbPass) or die("Couldn't connect.");
	$db = mysql_select_db($dbName, $connection) or die("Couldn't select database.");

	$sql = "Select * from $dbTable";
	$result = @mysql_query($sql)	or die("Couldn't execute query:".mysql_error().''.mysql_errno());

	header('Content-Type: application/vnd.ms-excel');	//define header info for browser
	header('Content-Disposition: attachment; filename='.$dbTable.'-'.date('Ymd').'.xls');
	header('Pragma: no-cache');
	header('Expires: 0');

	echo '
';
	for ($i = 0; $i < mysql_num_fields($result); $i++)	 // show column names as names of MySQL fields
		echo '


';
	print('

');

	while($row = mysql_fetch_row($result))
	{
		//set_time_limit(60); // you can enable this if you have lot of data
		$output = '
';
		for($j=0; $j
		{
			if(!isset($row[$j]))
				$output .= '

';
			else
				$output .= "


";
		}
		print(trim($output))."

\n";
	}
	echo('


'.mysql_field_name($result, $i).'

 
$row[$j]


');
?>

Did you ever wanted to export data from your MySQL database to some format nongeek people can work with? Here is simple PHP script you can use to export your data form MySQL table to Excel file. (Actually not real Excel XLS file but CSV file which Excel can read)
Download

/*
Export MySQL to Excel using PHP
Author: Vlatko Zdrale, http://blog.zemoon.com

Look but don't touch 
*/

	$dbHost	= 'myserver';		//	database host
	$dbUser	= 'myusername';		//	database user
	$dbPass	= 'mypassword';		//	database password
	$dbName	= 'mydatabase'; 	//	database name
	$dbTable = 'mytable';

	$connection = @mysql_connect($dbHost, $dbUser, $dbPass) or die("Couldn't connect.");
	$db = mysql_select_db($dbName, $connection) or die("Couldn't select database.");

	$sql = "Select * from $dbTable";
	$result = @mysql_query($sql) or die("Couldn't execute query");

	header('Content-Type: application/vnd.ms-excel');	//define header info for browser
	header('Content-Disposition: attachment; filename='.$dbTable.'-'.date('Ymd').'.xls');
	header('Pragma: no-cache');
	header('Expires: 0');

	ffor ($i = 0; $i < mysql_num_fields($result); $i++) // show column names
		echo mysql_field_name($result, $i)."\t";
	print("\n");

	while($row = mysql_fetch_row($result))
	{
		//set_time_limit(60); // you can enable this if you have lot of data
		$output = '';
		for($j=0; $j