how to download mysql table with php?

what is the mysql query for dumping and saving as zip

[Jaunty Edward]

Hi,

can anyone tell me how can I download a mysql table from a DB. I hope all of you have used phpmyadmin where you can click on a save as file thing and your intire table or DB is saved as a zip/ gz or sql file.

If anyone can tell me what will the sql query for that I will be greatful.

Thanks
BYe

[Jaunty Edward]

Hi,

I found this query that works on a local machine:

at

[php-mysql-tutorial.com...]

SELECT * INTO OUTFILE mysqlfile.sql FROM table

but for some reason I am not able to do this on the cpanel server with mysql version 4.025 I have even given the permission as 777 to that php file.

please help

thanks

[dmmh]

my cpanel allows me to do this by default?

there's a section for it 'download/ upload backup file' I think

[Jaunty Edward]

Hi,

thanks for the help. as I had mentioned before that I want to do this only with php....reason being I don't have cpanel access for the domain.

Thanks
Bye

[designaweb]

Try:

exec("mysqldump -uUSERNAME -pPASSWORD DATABASE > file.sql");

Worked just fine for me. Replace USERNAME, PASSWORD and DATABASE with the appropriate values. Make sure you dont add a space inbetween the u and USERNAME, the p and PASSWORD, like I did.

Importing this file again using exec:

exec("mysql -hHOSTNAME uUSERNAME -pPASSWORD -DDATABASE < file.sql");

(hostname is most likely localhost, and note the extra -D infront of the databasename this time).

Hope this helps.

[Jaunty Edward]

thanks for your help...

since I have been trying this from a long time I will let everyone know I am learning...

1. this can be done using system() in php but almost all hosting companies block that option for security.

2. the same story for exec.

3. This can also be done using the following query..

select * into outfile 'filename.sql' from tablename

however the file is saved within the mysql data folder where all the database are kept(different on linux and windows)

we can also specify the folder where the filename.sql should be kept and thats exactly what I am trying to do which is not working on the production server(RHE 3) while works fine on the local server(winXP) with same mysql server versions (4.025).

thanks for reading...I will post the solution here if I can get it somehow.

Thanks
Bye

[designaweb]

In PHP, try this one...

select * into outfile $_SERVER['DOCUMENT_ROOT']."DIRECTORY_TO_HTTP/data/filename.sql" from tablename

and replace DIRECTORY_TO_HTTP with the directory to your http dir. In my case this has been "public_html" and just "http" in some cases. Make sure the "data" directory is writable though.