Export MySql table to CSV?

Question

So, I have searched most of the answers here on stack and google and anywhere I could think of. There are plenty of answers indeed, but none have the structure I need.

Al my code looks like this:

$sql   = "SELECT * FROM users WHERE user_level = '$level' ORDER BY user_username DESC";
    if ($stmt = $this->connect->prepare($sql)) {
        $stmt->bind_result($id);
        $stmt->execute();
        while ($row = $stmt->fetch()) {
            $stmt->bind_result($id);
            $users[] = $id;
        }
        $stmt->close();
        $length[] = sizeof($users);
    } else {
        $error              = true;
        $message['error']   = true;
        $message['message'] = CANNOT_PREPARE_DATABASE_CONNECTION_MESSAGE;
        return json_encode($message);
    }

I'm using mysqli and stmt in all of my code, so I would like to keep it like this all the way.

So, I understand that I cannot have the CSV file where I have my action button to download it. But the thing is that my action button is part of a form, so I guess that instead of $_GET on the page I have the CSV I will have $_POST.

And my question, how do I loop through all database ( this needs to be depending on a column, a level ) and take all that data organized in a CSV file and than download it ? But this needs to be as the structure I have for my functions, I don't want to use db_query("SELECT * FROM {loreal_salons}"); per say or other like that.

Manse · Accepted Answer · 2012-01-27 22:06:46Z

1

Use the following :

// Setup the headers to force download of CSV
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=file.csv");
header("Pragma: no-cache");
header("Expires: 0");


// loop your records here and output as comma separated
echo $row['col1'].",".$row['col3'].",".$row['col3']."\n";

answered Jan 27, 2012 at 22:06

Manse

38.1k11 gold badges88 silver badges112 bronze badges

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

4 Comments

Roland Over a year ago

OK, but the headers will go into the same file that process the CSV ? Or a separate file ? And the CSV must be named as filename=myfilename.csv ?

Manse Over a year ago

You can output the headers anywhere as long as they are the first output and the filename can be anything you like - this will cause the browser to download the file

Roland Over a year ago

OK. And how would I sort all the data from the table by one column. For example I have a user_level column which can be 0, 1 or 2, and I want to output the data depending on that, if the level is 0 output all the users with the 0 level and none of the rest and so on, is there a way to do that by following my code ?

Manse Over a year ago

use order by to sort data in - you could use an if statement to determine the sort column and order (desc or asc) depending on the user_level

FMaz008 · Accepted Answer · 2012-01-27 22:01:58Z

0

If you want to loop over all the database, I think the key for what you want to do is :

SHOW TABLES - http://dev.mysql.com/doc/refman/4.1/en/show-tables.html .. to loop in all the tables

SHOW COLUMN - http://dev.mysql.com/doc/refman/5.0/en/show-columns.html .. to loop in all the column of a specific table

edited Jan 27, 2012 at 22:01

answered Jan 27, 2012 at 21:53

FMaz008

11.3k19 gold badges73 silver badges106 bronze badges

Collectives™ on Stack Overflow

Export MySql table to CSV?

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related