50
  1. I've got a 30K row table
  2. When I run a long, 50-line query on that table, a GROUP function reduces the number of rows to 7K
  3. I want to export the grouped 7K rows as a new table, or save them as a CSV

When I attempt to export, instead of getting the grouped 7K rows, I get the old, pre-query 30K rows. What am I doing wrong, and what should I be doing?

NOTE: I'm not a coder, so I'd really appreciate a solution that just used the phpMyAdmin GUI.

1
  • It would be very helpful to supply the SQL queries that phpMyAdmin created and used.
    – Bob Ortiz
    Jun 24, 2016 at 13:18

5 Answers 5

77
  1. Execute your sql query in the SQL tab of phpMyAdmin.

  2. After execution, scroll down the page and look for “Query results operations”

  3. Click “Export” link from the above and you will get the page to export all the results of the queries to desired format.

3
  • Very simple, don't know why I haven't seen it there in the past! Mar 31, 2014 at 20:38
  • 9
    Chosen "Export" from “Query results operations” and it shows all records from the table rather than the query result. Is there anything else to be selected ? Sep 15, 2018 at 8:35
  • 4
    @Web_Developer I agree this is misleading. In the Rows section under the "Dump all rows(s)" option, it shows all the rows in the DB rather than just the rows from the query result. However, if you click "Go" you only get the rows from the query result. Feb 4, 2020 at 16:16
22

Instead of the export button at the top of the GUI, use the one at the bottom, within the "Query results operations" box. That is the one you want.

Export button

2
  • I had uploaded a picture here as well - wonder where it went. P.S. - if this solved your problem, please mark it as the correct answer. Aug 12, 2011 at 17:31
  • 2
    Thanks! In the current interface there's yet another incorrect "export" button, right about the "query results operations" box. The screenshot is helpful.
    – octern
    Feb 7, 2013 at 19:43
7

On some query it's not possible in a direct way.

You must write the query result to a table through the create table as syntax And then you can follow the normal export instructions: https://serverfault.com/a/300342/256884

1
  • 1
    If your query returns a large number of records (many pages), this is the only solution. Aug 10, 2016 at 18:19
4

You've already got the query? You can insert into a new table using another query's results

Looks like the steps you need:

  1. Create the new table with all the columns.
  2. INSERT INTO newTable (field1, field2, field3) SELECT field1, field2, field3 FROM otherTable GROUP BY field1

Adjust for your 50-line query.

2
  • Thanks!Let me see if I understand... (1) create newtable (2) oldtable has about 60 columns, and all are being used, so... INSERT INTO newtable * SELECT * from oldtable (3) do i place your INSERT INTO code at the end of my existing query?
    – grape
    Aug 11, 2011 at 23:18
  • You put the INSERT INTO before your query. It works just like a normal INSERT except instead of providing "VALUES (rowColumn1, rowColumn2, rowColumn3)", you're providing the results of the SELECT instead.
    – Reece45
    Aug 12, 2011 at 15:19
1

I still needed an answer to this old question for a query from multiple tables and I came up with a good one. As others have stated, you can try the Export option under "Query results operations". As another other people noted, that does not always work and it may just give you all the rows from one table. That option did not work in my case. I also wanted something simpler than writing code to insert the results into a new table and then exporting that.

I found two easy ways to do this.

Method 1: Copy and paste

Check the "Show all" box above the results, highlight all the results, copy, and paste into a spreadsheet. That worked for me in Excel. However, the results preview truncates cell values that are too long. Therefore, this may not work for you if your cell values are long and you need the complete values.

Method 2: Create view

Under "Query results operations", there is an option to "Create view". Created views will appear below the database Tables in a new "Views" category. Views are basically persistent tables that show custom query results. Click "Create view", enter a name for the view and Go. Now you successfully use the Export option under "Query results operations" while in the View.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .