Aug
30

Export MySQL to Excel with style using PHP & HTML tables

PHP,SQL          Trackback

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]
'); ?>

Aug
30

Export from MySQL to Excel using PHP

PHP,SQL          Trackback

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


".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'); for ($i = 0; $i < mysql_num_fields($result); $i++) // show column names as names of MySQL fields 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 < mysql_num_fields($result); $j++) { if(!isset($row[$j])) $output .= "NULL\t"; else $output .= "$row[$j]\t"; } $output = preg_replace("/\r\n|\n\r|\n|\r/", ' ', $output); print(trim($output))."\t\n"; } ?>


top