Exporting Data to a Downloadable CSV File with CakePHP

Generating a downloadable CSV file in CakePHP seems to be a bit of a stumbling block for many relatively new to using Cake. It doesn’t need to be challenging. There’s just a few basic steps you need to put in place.

As an example we’ll consider a User model that contains contact details of people who have register to our app. We want to create a CSV export of all the User data.

The Controller

Let’s start with the controller. We need to create an action in the User’s Controller for generating the CSV export:-

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
// app/Controllers/UsersController.php

class UsersController extends AppController {

    public function export() {

        $this->response->download("export.csv");

        $data = $this->User->find('all');
        $this->set(compact('data'));

        $this->layout = 'ajax';

        return;

    }

}

We’re calling

1
$this->response->download("export.csv")

to set the relevant headers. When you trigger the controller’s action from the browser it will start to download a file named ‘export.csv’ rather than attempt to display the view in the browser.

We then grab the data from the database and set it ready for the View.

Finally we set the View layout to use the AJAX layout, $this->layout = ‘ajax’. This might seem quirky, but it will ensure the View isn’t wrapped in any HTML markup. We just want to output the content of the View we are shortly about to put together.

The Route

As it currently stands the URL for our CSV export will be ‘users/export’; it would be nice to append that with the CSV extension. To do that we’re going to tell Cake to correctly parse CSV file requests. In our routes.php file (in app/Config/) add the following:-

1
Router::parseExtensions('csv');

This will now allow us to access our CSV export from the URL ‘users/export.csv’.

The View

Now we need to put together our View, making sure we correctly escape values being output:-

1
2
3
4
5
6
7
8
9
10
11
<?php

// app/Views/Users/export.ctp

foreach ($data as $row):
    foreach ($row['User'] as &$cell):
        // Escape double quotation marks
        $cell = '"' . preg_replace('/"/','""',$cell) . '"';
    endforeach;
    echo implode(',', $row['User']) . "\n";
endforeach;

This should be pretty self explanatory. We’re looping over all the rows in our

1
$data

array; escaping any double quotation marks in the values and then wrapping the value in quotation marks; and finally outputting the row delimiting values with commas and adding a new line break at the end.

Finally

Everything should now be setup, you’ll just want to be able to link to your CSV export from another View. If you’re parsing the file extension then you need to include this in the URL; so using the HtmlHelper we need to include the ‘ext’ parameter for the file extension:-

1
2
3
4
5
echo $this->Html->link('export', array(
    'controller' => 'users',
    'action' => 'export',
    'ext' => 'csv'
));

That should be it! Clicking on the ‘export’ link should start the browser downloading ourexport.csv file.

How to Create CakePHP Controller,Model and View by Bake console

CakePHP’s Bake console is easy and powerful tool to kick start your project. You will be able to step up a full working application in a few minutes using it.
In this tutorial, we will show you how to use CakePHP’s Bake console to generate model, controller and view codes by simple console commends.
Please note this tutorial requires basic understanding of CakePHP framework, at least you should be able to write a hello world application on your own.
If you have not yet used CakePHP, please read the offical Cookbook. – See more at:

Set Up Bake console

I have already setup a CakePHP project under :

1
C:\wampp\www\personal-tutorial

First thing we are going to do is to verify if Environment Variables is set properly in your windows machine.

Go to Command Prompt(I hope you know what ‘CMD’ is, do a Google search on ‘CMD windows’ if you have no idea.) in windows and type in “cake bake” from any path:

1
C:\Users\Chauhan&gt;cake bake

If you get following message:

1
2
3
C:\Users\xd&gt;cake bake
'cake' is not recognized as an internal or external command,
operable program or batch file.

You will need to set up the Environment Variables; specifically it is the Path variable. Follow the steps below:

  1. Locate ‘Path‘ Variable under System variables section of Environment Variables.
    If you do not know where this setting is, you can follow this link.
  2. Double click on ‘Path‘ variable and bring it up to edit. Before doing any editing, please backup its original value.
    Path variables are separated by ‘;”.
    What we need to do is to add a path pointing to “C:\wampp\www\personal-tutorial\cake\console\” folder.
    So at the end of existing Path value, we add “;C:\wampp\www\personal-tutorial\cake\console\“. Please pay extra attention to ‘;’ at the beginning of the added string.
  3. Finally click on ‘OK’ to save your setting. And restart Command Prompt (setting will only take effect on new Command Prompt). Type in “cake” to verify if it is working:
1
2
3
4
C:\Users\xd&gt;cake
Welcome to CakePHP v1.3.2 Console
---------------------------------------------------------------
******

Step Up Database table


We will need a database table for CakePHP console to generate the codes. Let us create table in our database:

1
2
3
4
5
CREATE TABLE `posts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` INT( 250 ) NOT NULL ,
`content` TEXT NOT NULL
) ENGINE = InnoDB;

Bake model


Now open Command Prompt and CMD to “C:\wampp\www\personal-tutorial\app” as below:

1
C:\&gt; cd wampp\www\personal-tutorial\app

Then enter “cake bake” in the Command Prompt:

1
2
C:\&gt;cd xampp\htdocs\personal-tutorial\app
C:\xampp\htdocs\personal-tutorial\app&gt;cake bake

If you get a message similar to this:

1
2
3
4
5
6
Welcome to CakePHP v1.3.2 Console
---------------------------------------------------------------
App : app
Path: C:\xampp\htdocs\personal-tutorial\app
---------------------------------------------------------------
Your database configuration was not found. Take a moment to create one.

This means it is a fresh copy of CakePHP project you are working on, you are asked to create database configuration file (which is “app/config/database.php”). Just follow the instructions, it will automatically generate a database.php for you.

If you get a message similar to this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Welcome to CakePHP v1.3.2 Console
------------------------------------------------
App : app
Path: C:\xampp\htdocs\personal-tutorial\app
------------------------------------------------
Interactive Bake Shell
------------------------------------------------
[D]atabase Configuration
[M]odel
[V]iew
[C]ontroller
[P]roject
[F]ixture
[T]est case
[Q]uit
What would you like to Bake? (D/M/V/C/P/F/T/Q)

This means you are ready to go. Just enter “Q” to quit first.

Now go back to Command Prompt and do “cake bake model post” as follow:

1
C:\wampp\www\personal-tutorial\app>cake bake model post

You should get message like similar to this:

1
2
3
4
5
6
C:\wampp\www\personal-tutorial\app>cake bake model post
Welcome to CakePHP v1.3.2 Console
---------------------------------------------------------------
App : app
Path: C:\wampp\www\personal-tutorial\app
---------------------------------------------------------------

Baking model class for Post…

Creating file C:\wampp\www\personal-tutorial\app\models\post.php
Wrote `C:\wampp\www\personal-tutorial\app\models\post.php`

You can download SimpleTest from http://simpletest.org

Baking test fixture for Post…

Creating file C:\wampp\www\personal-tutorial\app\tests\fixtures\post_fixture.php
Wrote `C:\wampp\www\personal-tutorial\app\tests\fixtures\post_fixture.php`
Bake is detecting possible fixtures..

Creating file C:\wampp\www\personal-tutorial\app\tests\cases\models\post.test.php
Wrote `C:\wampp\www\personal-tutorial\app\tests\cases\models\post.test.php`

Bake controller


Baking controller is similar to bake model. From the same Command Prompt above, enter “cake bake controller post scaffold” to bake the controller class. A few notes to take:

1
C:\wampp\www\personal-tutorial\app&gt;cake bake controller post scaffold

This will generate standard controller class which all CRUD methods implemented.

1
C:\wampp\www\personal-tutorial\app&gt;cake bake controller post

This will create a scaffolding controller, which means no functions will be defined in the controller, and view files will not be generated.

1
C:\wampp\www\personal-tutorial\app&gt;cake bake controller post scaffold admin

This will prepend “admin_” to each function. This is perfect if you are using any prefix routes. You can change “admin” to any value you needed.

You should get following message for a successful generation:

1
2
3
4
5
6
7
8
9
10
11
C:\wampp\www\personal-tutorial\app&gt;cake bake controller post
Welcome to CakePHP v1.3.2 Console
---------------------------------------------------------------
App : app
Path: C:\wampp\www\personal-tutorial\app
---------------------------------------------------------------
Creating file C:\wampp\www\personal-tutorial\app\controllers\posts_controller.php
Wrote `C:\wampp\www\personal-tutorial\app\controllers\posts_controller.php`
Bake is detecting possible fixtures..
Creating file C:\wampp\www\personal-tutorial\app\tests\cases\controllers\posts_controller.test.php
Wrote `C:\wampp\www\personal-tutorial\app\tests\cases\controllers\posts_controller.test.php`

Bake view


Bake view files is very straightforward. From the same Command Prompt above, and do

1
C:\wampp\www\personal-tutorial\app&gt;cake bake view post

This will generate corresponding views depends on your controller class. So make sure you follow the order correctly, first bake controller, then view.You should get following message for a successful generation:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
C:\wampp\www\personal-tutorial\app cake bake view post

Welcome to CakePHP v1.3.2 Console
---------------------------------------------------------------
App : app
Path: C:\wamp\www\personal-tutorial\app
---------------------------------------------------------------

Creating file C:\wamp\www\personal-tutorial\app\views\posts\index.ctp
Wrote `C:\wamp\www\personal-tutorial\app\views\posts\index.ctp`

Creating file C:\wamp\www\personal-tutorial\app\views\posts\view.ctp
Wrote `C:\wamp\www\personal-tutorial\app\views\posts\view.ctp`

Creating file C:\wamp\www\personal-tutorial\app\views\posts\add.ctp
Wrote `C:\wamp\www\personal-tutorial\app\views\posts\add.ctp`

Creating file C:\wamp\www\personal-tutorial\app\views\posts\edit.ctp
Wrote `C:\wamp\www\personal-tutorial\app\views\posts\edit.ctp`

Finish


 

Now go and check folders “app/controllers”, “app/models” and “app/views”. You will find “posts_controller.php”, “post.php” and “app/views/posts”. They are all auto generated.

Open browser and head to “http://localhost/personal-tutorial/posts”