Hi

Can anyone put me in the right direction?

I would like to export data in a table in a MySQL database into a downloadable csv file.

Any Ideas?

What I would love to do is to not only be able to download the whole table contents, but have the ability to select certain entries based on a search and download the entries into a csv file.

I have no idea where to start!

Cheers

Lulubell

    Check out the mySQL.com documentation on exporting....it is not difficult to export to a .csv file.....just add a download link which will execute the sql statement (that you will find at mysql.com) to export as a .csv file....

    r2

      Lulubell,

      You sound like a real sweetheart, so I'm going to help you right out.

      Download and install phpMyAdmin; it's the best MySQL front-end tool I've seen to date. It will allow you to select data with SQL queries and, of course, export that data to CSV.

      You can get the latest version here: http://www.phpmyadmin.net. Just scroll down a little and depending on which operating system you're using, download the appropriate release of version 2.5.2-pl1. If you're on Windows, it'll be the zip file, Mac will be the .tar, .gz, etc.

      phpMyAdmin also makes working with your database very easy, you don't have to worry about long query strings and SQL syntax mistakes.

      Anyway, I'll let you get to it. Once you've extracted the phpmyadmin directory, put it in the root folder of your site. Then, open the phpmyadmin directory and open config.inc.php (you MUST do this part). Scroll down to line 39 to

      $cfg['PmaAbsoluteUri'] = 'blah blah'

      and set it equal to 'http://localhost/root_of_your_site/phpmyadmin'

      Basically, you just need to point this directive to wherever you just put the phpmyadmin folder. Also, I recommend putting a .htaccess file inside the phpmyadmin folder and putting your .htpasswd file outside the root directory of your site.

      Just open up a web browser, go to http://localhost/root_of_your_site_/phpmyadmin/index.php and you are all set. You should see all your SQL database info. Select a database from the dropdown on the left, then click a table just below that. Click Browse to view the contents and Structure to edit the structure, indices, etc. To export to CSV, go to the Export tab and it's self-explanitory.

      Hope that helps!

      -Benny

        I'm still trying to find the documentation on mysql.com

        and

        Thanks for the phpmyadmin info, I actually do use msqlfront to view/create stuff and so on, but I want to be able to select the tables and certain entries to download from a web page.

        For example, either I can click on a link to download the whole table, or if I have searched for entries with the colour blue in a "colour" field, I want to be able to download the table but just the entries with blue in the colour field.

          Hi

          I found mysqldump on mysql.com is that what I need to export data?

          If so, do you know anywhere which has the code laid out so that I can try to understand it better?

          Cheers

            Sorry for the lack-luster post earlier....I know it was very vauge....I am at work but still trying to help....I have got up on some stuff so let me look around and I will get back to you....and by the way...phpmyadmin is the best front end, but I didn't mention it to you b/c I thought you wanted to be able to just click a link from a webpage to export....I am checking now on that for you though...

            r2

              Hi, Lulu,

              I think I need more explanation of what you're trying to do. Is this functionality intended for internal use (i.e. namely, by you or someone in your organization) or for public website visitors? When you say 'website', I'm not sure if you're talking about the sql front end page, or the actual PHP page you're serving to the public.

              I was thinking that you wanted to download and backup certain areas of your SQL database for archival purposes or some other application (like Excel). Is that correct? If so, I highly recommend, again, using phpMyAdmin. It has a Select screen where you can run a SQL query, for example:

              "SELECT * WHERE color = 'blue'"

              and it will show all the results, with a link just below the result table that says "Export" and takes you to another page with export options, such as format, what to dump, etc. You don't have to type any of the code or syntax yourself; it really couldn't be any simpler. phpMyAdmin is a single folder, it's tiny in size, and it isn't going to screw things up and you can still use your current front-end if you like it better than phpMyAdmin, without deleting or moving either one. They can coexist completely peacefully :p

              Oh, and you can also go to the Search tab in phpMyAdmin and search every table in your database using SQL criteria that is very nicely put in drop-down menus for you, so you don't even have to type which fields, the search criteria, etc. It has all the operators, such as LIKE, <, =, etc. in menus.

              Are we making progress yet?

              -Benny

                http://www.phpbuilder.com/mail/php-general/2002081/2166.php

                I found this link on PHPBuilder (well, here 🙂) but I didn't write it....This should show you how to export to a .csv file....as far as selecting data and then just exporting the selected data here is what I would do....

                1. Query the database and get your "selected" data
                2. Create a temp database
                3. Move the "selected" data to the temp database
                4. Export the temp database

                Hope this makes sense....I believe it is fairly efficient also....someone correct if I am wrong...

                  I'm sure that'd work fine, r27, but why not go with my last suggestion, since phpMyAdmin takes all of one minute to install and configure, and will create that temp database, move the selected data to it, and export it (not to mention provide querying menus and operators to save you time and the possibility of making mistakes)?

                    I agree with the phpmyadmin suggestion.....that is all I use....but the way I interpreted her post is that she wants anyone to be able to run a search (query) from a webpage and then basically download their search results when they are done....

                    If that is the case then you cannot protect the integrity of your data by allowing people access to your databases via phpmyadmin or any front-end....as I am sure you know.

                    Anyways either way will work and if you are just wanting to do it yourself then I would definately go w/ phpmyadmin lulu...but if you want others to have access to the "search and download" phpmyadmin is not the way to go.

                    r2

                      Hi

                      Thanks for your help above, I am trying to let users access an admin area on the web, do a search and be able to download the results of the search into a csv file on their computer.

                      However, after reading up and testing the theory from the link above it looks like I can't do this anyway, as it can only be downloaded to the server running the MySQL database.

                      I can't actually get to work on MySQLFront either - keeps stating Access denied for User, so I guess my priviledges need to be changed, but if I can't do it, how are my users going to be able to do it?

                      Thanks fo your help...

                      Lulubell🙁

                        Write a Reply...