Backing up a MySQL database by email?

[sametch]

In PHPMyAdmin there is a way you can select all tables in a database and send a text file by email that enables the database and its contents to be reconstructed with SQL.

I would like to do a similar thing using PHP for a database I have designed in MySQL.

I would like a single button which when clicked would send an email to the webmasters email address. The email would contain the SQL code to recreate the database and insert all records.

Is this possible?

Could anyone advise how I could do it?

[pyfsapple]

It must be possible because PHPMyAdmin can do in this way.

Refer to the code of PHPMyAdmin.

[sametch]

I have tried that route, but I am fairly new to php/mysql and I am struggling to make any sense of the code in PHPMyAdmin. I can't even work out what functions I require to make it happen.

🙁

[pyfsapple]

Check
~phpMyAdminDIR/export.php
Be patient, take a look on the comments should benefit.

OR

If you want to program it yourself, the following functions will help you:
mysql_list_dbs( )
mysql_list_tables( )
mysql_list_fields( )
mail( )

I think the 2nd method is a better one as the application you want is not difficult.

[sametch]

Thanks pyfsapple you've given some useful pointers.

If I am following the PHP help correctly, it appears that I need to do the following:

1. List all databases
2. For each database list all tables
3. for each table list all fields
4. for each field list all field properties

Then construct a MySQL make table query from the values i've got.

Have I got the process right?

[sametch]

I am sort of getting there using :

mysql_list_tables
mysql_list_fields
mysql_field_name
mysql_field_len
mysql_field_flags
mysql_field_type

But my output isn't quite right.

PHPMyAdmin produces:

CREATE TABLE tblaccommodation (
ID int(11) NOT NULL auto_increment,
AccName varchar(125) default NULL,
AccAbstract mediumtext,
AccArea varchar(125) default NULL,
AccPriceRange varchar(50) default NULL,
AccImage varchar(75) default NULL,
AccImageH int(11) default NULL,
AccImageW int(11) default NULL,
AccImageAlt varchar(125) default NULL,
AccOrder int(11) NOT NULL default '0',
AccCategory varchar(50) default NULL,
PRIMARY KEY (ID)
) TYPE=MyISAM;

My output is:

CREATE TABLE tblaccommodation (

ID 11 not_null primary_key auto_increment int ,
AccName 125 string ,
AccAbstract 16777215 blob blob ,
AccArea 125 string ,
AccPriceRange 50 string ,
AccImage 75 string ,
AccImageH 11 int ,
AccImageW 11 int ,
AccImageAlt 125 string ,
AccOrder 11 not_null int ,
AccCategory 50 string ,
)

It appears that PHPMyAdmin is getting the type of field much better than I am. For example it determines that a field type needs to be "varchar" wheras I am only getting type string!

Has anyone any ideas what I need to do to get around this?

😕