Export mysql data to excel using php

I wrote ways to export from mysql to CSV little back. You can easily generate SELECT INTO ... OUTFILE query and run it through PHP. I think that 'd be better/easiest way to go.

Thanks theGhost_k8,

I just took a look at it and I have to be honest, I don't know what is going on there and how I would possibly implement that into my MVC structure.

amm to least confuse you...

Following query will export a csv file named db.tablename.csv.

SELECT * INTO OUTFILE '/csv_files/db.tablename.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM db.tablename;

Select allOpen in new window

You can dynamically change outfile name, table or column names and execute this via PHP.

Thanks again. I have tried to implement your code in my model but I have 2 issues at this stage. The code from $results onwards is showing me in my editor that the SQL statement is not closed off?

And secondly, I want the download to go to the users download folder as if they were downloading anything from the internet. Not sure that path will achieve that.

$this->db->query("
		SELECT * INTO OUTFILE '/csv_files/db.tablename.csv'
		FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
		LINES TERMINATED BY '\n'
		FROM `vouchers`
		");
$results = $this->db->resultSet();
return $results;

Select allOpen in new window

Hi,

Couple of points of note here. The CSV Format, by definition is a collection of rows containing Comma Separate Values. When you open a CSV File in Excel, it will automatically put the data into columns for you.

An Excel file is a different format, and is better if you want to add additional formatting / worksheets / headers / formulas etc.

If you genuinely do just need your data output so that you can load it up in Excel, then you can create a CSV file very simply, without having to create an actual Excel Spreadsheet file. Here's a very quick example:

<?php
$members = $db->query("SELECT firstname, lastname FROM members");
$file = fopen("members.csv","w");

while ($member = $members->fetch(PDO::FETCH_ASSOC)):
    fputcsv($file, $member);
endwhile;

fclose($file);

Select allOpen in new window

That will create a CSV file containing all the records from the table. If you open members.csv in a text editor, you'll just rows of comma delimited values. If you open it in Excel, you'll see the data is correctly put into the columns.

Thanks Chris, could all that code be executed in the model?

So, I just tried to change your code a bit in my model like this:

		$this->db->query("SELECT * FROM `vouchers`");
		$results = $this->db->resultSet();
		
		$file = fopen("members.csv","w");

		foreach($results as $result){
			
			fputcsv($file, $result);
		}

		fclose($file);

Select allOpen in new window

And I am getting that confusing error again about arrays and objects.

Warning: fputcsv() expects parameter 2 to be array, object given

Right,

The fputcsv takes an array of values as it's second argument, which are then converted into a row of command separated values. If I remember correctly, your resultSet() method is returning an array of objects, so you will need to convert each object into an array. You should just be able to cast it directly:

foreach($results as $result){
    fputcsv($file, (array)$result);
}

Select allOpen in new window

Not sure if this would help, but in my database class I added FETCH_ASSOC to try prevent this array of objects problem I keep having.

      

public function assocSet() {
		$this->execute();
		return $this->stmt->fetch(PDO::FETCH_ASSOC);
	}
	
	// Get result set as array object
	public function resultSet() {
		$this->execute();
		return $this->stmt->fetchAll(PDO::FETCH_OBJ);
	}

Select allOpen in new window

Also, I tried your code above and I don't get any errors, but nothing downloads either?

Hey,

Your assocSet will only retrieve one record. You still need to use the fetchAll() method:

$this->execute();
return $this->stmt->fetchAll(PDO::FETCH_ASSOC);

It may be that you can wrap that all into one method, and differentiate the options with a method argument (defaulting to FETCH_OBJ)

You say nothing downloads - there's nothing in your question or code that mentions downloading. The code as you have it will just write the CSV file to the server. If you want the user to download it, then you'll likely need to set the headers to foce a download.

If that's what you need, let me know - you'd do things slightly differently if that's the case

Apologies, Chris. In my original question I said 'exporting' which in my mind meant downloading :)  But yes, I want the data to download into a csv file.

Thanks Chris, is this something I can do in the model or should I run the SQL query in the model and then do the rest in the controller?

For me, it makes sense to do it in the Controller. Retrieve your data from the Model, and push the file from your Controller - effectively all you're doing here is pushing a View to the user, so it doesn't really belong in the Model.

Okay, thanks so much. I did as you suggested and ran just the database query in the model and then did the rest in the controller. The file is now downloading and when I open it up all the data is there. The only problem is that all data is just separated by commas per record and not in columns. I know I am pushing my luck here but any info on how to do that would be great. If you don't have the time (or patience) then no problem, I will accept your last answer as the correct one.

Hey,

That's the nature of CSV files - they're just comma separated values. A text file such as CSV has no concept of 'columns'.

If you open that file in Excel, you'll get the data in the columns.

Thanks, Chris. I did open it in excel and it looks like this

Hmmm.

That seems a little odd. Unfortunately, I don't have Excel installed - I use LibreOffice, but the principles should be the same. Excel should have no problem opening a .csv file and correctly splitting the values on the comma. Can you post up a copy of a 'downloaded' csv file and I'll just run a quick open in LibreOffice to see if there's anything slightly off

Can't seem to upload anything other than images here so sent you the csv file in a PM.

Strangely enough, if I import it into Numbers then it displays in columns but if I open up in excel for Mac then it just shows as per my screenshot.

Yep - that opened exactly as I thought it would:


It may be an Excel thing, but as I said, I can't test it. Have a Google around to see what it says about opening CSV in Excel. I know this should work, because I've done it countless times before

Perhaps it is just my excel. It is super old and is for mac and isn't really the same as the windows version. Perfect! This should be good to go then as long as you aren't using a really old version of Office for Mac! :-P