Coders4Fun

After a lot of work, I released the new version (2.0) of the MySQLDump class, already introduced by this article.
MySQLDump is a class that allows to do a complete MySQL database backup with php pages.

After the inandrea’s good work there wasn’t too much space for improvements, but I think that these addition can be usefull: now it’s possible to export structures and/or data not only for the selected database but also for a single table.

The following example will explain how to export the structure and the data for the table mytable in the db mydb (the interface to the class is slightly changed from the previous version).

//Include the library
@include_once('lib_dump.php');
//Db connection
$connection = @mysql_connect('127.0.0.1','username','password');
//Create the MySQLDump class instance
//1° parameter: db name
//2° parameter: the exported file that will contain the dump
//3° parameter: create zipped file (true = zipped, false = normal)
//4° parameter: data encode (true = hexadecimal, false = plain text)
$dumper = new MySQLDump('mydb','dumpfile.sql',false,false);
//Structure export of the table 'mytable'
$dumper->getTableStructure('mytable');
//Data export of the table 'mytable'
$dumper->getTableData('mytable');

Continue Reading »

After the success of the article PHP backup of a mysql database, I decided to write a short post about a file that can be usefull to download the backup of your db site/wordpress blog directly from your browser.
To use the script, you can simply download it and edit the first lines with the basic configuration:

// DB Configuration
$server = "IP of your server (localhost will work in most cases)";
$username = "Valid username for your mysql db";
$password = "Password";
$dbToDump = "Db to dump";
// password to access the php page. PLEASE change the default password!!!
$backupPassword = "UltraSecretKeyThatYouMUSTInsertHere";
// filename to save
$filename = "backup.sql.gz";

After that you can upload the file to your server, in the same folder where is located the class MySQLDump.
To access the script you have to specify two parameters: the password to access the script (pass), and another parameter (t) that if it’s 1 will separate the SQL inserts every 100 rows.
For example, if you upload the file to the root directory of yoursite, you have to write in the address bar of the browser:

http://www.yoursite.com/backup.php?pass=UltraSecretKeyThatYouMUSTInsertHere&t=1

…and your favorite browser will open the dialog to save the file with the db backup!

For more info about the MySQLDump class, read the original post, or download the source from here:

I searched a way to do a daily backup of my wordpress blog in my home pc but I didn’t find nothing that completely satisfy me, so I decided to code it.
I separated the problem in two scripts:
The first, that is written in php and runs on the web server, makes the dump of mysql databases from my hosting provider and leaves the dump on the web server.
The other one (written in bash, and running locally) downloads the site and the mysql dump from the web server, via FTP. Unfortunately I discovered that my hosting provider (Aruba.it) didn’t supply with mysqldump its server.

First of all, I searched for a php script that makes the direct dump of the database.

Continue Reading »

If we want to insert a date in a SQL string in C#, the solution seems obvious:

DateTime date = DateTime.Now;
string sql = "INSERT INTO tableWithDate VALUES(1, "+ date.ToString() + ")";

Unfortunately this code can’t work! The function DateTime.ToString() returns a string like 23/02/2007 19.00.49, while MySQL wants a string like 2007-02-23 19:00:49. The following static function converts a DateTime object in a valid date field for MySQL:

public static string GetSQLDate(DateTime date)
{
	string sql;
	sql = date.Year.ToString() + "-" + date.Month.ToString() + "-" + date.Day.ToString() + " "
	+ date.Hour.ToString() + ":" + date.Minute.ToString() + ":" + date.Second.ToString();
	return sql;
} 

I inserted the method in a utility class, that i called Utils, so the working code is:

string sql = "INSERT INTO tableWithDate VALUES(1, "+ Utils.GetSQLDate(date) + ")";

Today at work i had my daily dilemma: how can i save a blob field in a MySQL DB with c#??? The solution it’s pretty simple, but it can be hard to find.

The Blob is a binary field where data can be saved: the problem is that we must write these binary data in a textual SQL string. To resolve this problem we can convert the binary data in an hexadecimal representation in order to write the data in a textual format.

The resulting SQL will look like that:

INSERT INTO tableWithBlob VALUES(1,0xab425fa52d3e13);

Where 0x… are our binary data.

To start, we must first convert the data structure that we want to put in the database in a byte array (byte[]), or in a format in which we can access all structure’s bytes.
Continue Reading »

Stored Procedures are a way to re-use and parametrize long SQL queries.
To create a Stored Procedure you need at least MySql 5.0. Using MySQL Query Browser the stored procedure creation is very quick:

Right click on the database where do you want to create the procedure (from version 5.0.1 procedures are assigned to a specific database, instead in the 5.0 version the procedures are globals)

Continue Reading »

Every time that we need to create SQL queries in a programming language, often we do something like:

int id = GetTableID();
string place = GetPlace();
DateTime timestamp = GetLastActionDate();
string strTimestamp = Utils.GetSQLDate(DateTime.Now);
string sql = "INSERT INTO table VALUES(" + id + ",'" + place + "'," + timestamp + "')";

During the string creation we must add , ‘ and ” with attention.
In this article we will create a function that takes as input a c# Object and gives as output the formatted SQL String:

public static string GetSQLObject(object value)

Continue Reading »