Exporting query results with phpMyAdmin

[damo87]

I can usually export the results of my queries in the format 'CSV for Microsoft Excel' in PHPMyAdmin, but when attempting to do this with the following query:

 

SELECT user, SUM(odds) FROM choices WHERE round=1 GROUP BY user limit 0, 10000;

 

I can only get an xml file to be exported, which doesn't contain the results of the query. I suspect it is the SUM and/or GROUP BY function that is the major difference between the queries I can export successfully, and this one. Any suggestions greatly appreciated. Thanks.

 

--Versions: MySQL - 5.0.90; phpMyAdmin - 2.8.2.4--

[fenway]

Check that the recordset is what you expect.

[damo87]

Yes, the recordset is correct. I have to spend about an hour copying the results and pasting them into excel. I received some advice from the SourceForge.net help forum to upgrade to phpMyAdmin 3.3.1 or at least 2.11, but this isn't an option for me because I am on a shared server. Is there another place (other than phpMyAdmin) I can run the query and copy/export the results?

[fenway]

Sure... SELECT INTO OUTFILE.

[damo87]

Thanks for the 'select into outfile' suggestion. Would have been perfect except my database user doesnt have the permissions to use this. I tried granting permision, ie GRANT FILE ON *.* TO 'dbuser' @ 'localhost'; but permission was always denied.

 

After trying other solutions (query tools, a separate php page) I realised that there was a simple way to sidestep the problem in phpmyadmin. I simply created a temporary table and populated that with the query results:

 

INSERT INTO round_odds_temp (user, sumodds)
SELECT user, SUM(odds) FROM choices WHERE round=1 GROUP BY user limit 0, 10000;

 

I can then export the new table into a csv file.