Mar
31

Convert Excel date format to MySQL date format

SQL          Trackback

If you are trying to upload CSV file into your MySQL database, and that file contains date field, you’ll have to convert it first because MySQL will not recognise Excel date format (for English(US) it is m/d/yyyy h:m).
All You have to do is:

  1. Open your xls (or csv) file in MS Excel,
  2. Highlight cells containg date,
  3. Right click and select “Format Cells…”,
  4. Select “Custom” category,
  5. Enter “yyyy-mm-dd hh:mm:ss” in Type filed

After you save file as CSV, you should have your date fields formatted for MySQL use.


Mar
12

Simple MySQL table backup & restore

SQL          Trackback

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?


Oct
15

Export MySQL To Excel better way

PHP,SQL          Trackback

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]






Sep
10

How to delete duplicate rows when there is no primary key in SQL Server

SQL          Trackback

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.


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

top