Hi All!

 

Hope your all well!

 

Is theyre a way to export information from a mysql database to an excel document? Without using phpmyadmin ?

 

Thanks Ben

Not unless you write a script to do it. There are scripts available at Hotscripts that let you do this, but in all honesty you might as well use phpmyadmin.

Thanks, I would usually use phpmyadmin but I dont want the client to have access to the database structure etc, will take a look at hotscripts later. Can you reccomend any?

MySQL can do a dump to CSV, and you can import that into Excel. Excel can save to CSV and there is a MySQL command which imports a CSV file.

 

This only works on a per-table basis.

 

You could also try doing:

select * from mytable

and dumping the result into XML, which I think can do multiple sheets at once.

I've never had need to do it, either for myself or a customer. Have a look through Hotscripts

thanks will keep looking on hotscripts...

 

Just wondered how you export as a csv file using mysql, I found a snippet from mysql site but with no explanation just wondered how it works?

 

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM test_table;

 

I how I would download the file from a webpage without going onto the server?

Hi bennyboy,

 

That query selects columns "a", "b", and the sum of a and b, from test_table, and writes them into /tmp/result.text

 

For most of it, you will need to change 'tmp/result.text' to the absolute directory of your servers web root (for example /srv/htdocs or /var/www, can be found with phpinfo() command i think). The rest of the stuff is basically just an average comma-seperated value format, with each row on a new line (\n is a newline char)

 

so...

select columns into outfile dumpfile location fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from table

 

simple enough, but not neccesarily obvious if you haven't seen it before

Thanks pp, thats clear that up, many thanks!