Get everything from a Database - MySQL

Question

I was wondering if there is a way to get everything (all records) from a database? Then the user will have the option to save that file as an excel spreadsheet.

I was looking at DTS (data transformation service) is this the same thing?

Is there a specific query that can be sent through PHP to the database, would that be too much load on it?

I did some volume analysis and figured that the largest the database will ever get will be no more than 40mb.

So Ideally what I want to achieve is this.

Query "get everything from database"
My PHP "recieves query result"
My PHP "transforms it into an excel file"
Prompt user to save excel file

Is this possible?

Thanks

'SELECT * FROM [tablename]' will get you number 1.

Josh Darnell
– Josh Darnell

2011-08-05 13:15:54 +00:00
Commented Aug 5, 2011 at 13:15 — Josh Darnell
– Josh Darnell, Commented Aug 5, 2011 at 13:15
but what about all my tables, I have 20 tables?

sqlmole
– sqlmole

2011-08-05 13:17:03 +00:00
Commented Aug 5, 2011 at 13:17 — sqlmole
– sqlmole, Commented Aug 5, 2011 at 13:17

wonk0 · Accepted Answer · 2011-08-05 13:15:45Z

3

You can only SELECT everything from a table; iterate a list of all tables (use SHOW TABLES of information_schema.tables) and run SELECT * FROM ... for each.

answered Aug 5, 2011 at 13:15

wonk0

14.1k1 gold badge23 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Grossman Over a year ago

Why is this the "only right answer"? MySQL can write a CSV file from a table directly, you don't have to select everything from each and write your own generator.

Dan Grossman · Accepted Answer · 2011-08-05 13:15:41Z

2

Databases are not spreadsheets. You can get a CSV representation of a single table containing no binary data (which Excel will open) using a SELECT ... INTO OUTFILE query.

http://dev.mysql.com/doc/refman/5.1/en/select.html

answered Aug 5, 2011 at 13:15

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

Comments

Chris · Accepted Answer · 2011-08-05 13:17:16Z

0

<?php
    $output = array();
    $tables = array('table1','table2','table3');
    foreach($tables as $table) {
        $sql = "SELECT * from " . $table;
        $result = mysql_query($sql);
        array_push($output, $result);
    }
    // You now have an array of database objects for each table 
    // to do with as you will.
?>

answered Aug 5, 2011 at 13:17

Chris

2012 silver badges13 bronze badges

1 Comment

Dan Grossman Over a year ago

What does an array of result set resource pointers do for you?

Collectives™ on Stack Overflow

Get everything from a Database - MySQL

3 Answers 3

1 Comment

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Related