Export MySQL Data to CSV in CakePHP

Time to read: 3 minutes

November 06, 2023
Written by
Temitope Taiwo Oyedele
Contributor
Opinions expressed by Twilio contributors are their own
Reviewed by
Matthew Setter
Twilion

Data is king, so managing it efficiently is essential! One common requirement of working with data is exporting data from a database to a CSV (Comma-Separated Values) file, a universal format for sharing structured information.

In this tutorial, you will learn how to export data from a MySQL database to a CSV file with the CakePHP framework.

Prerequisites

Before we dive into the tutorial, make sure you have the following:

Create a CakePHP Project

To do this, navigate to the folder where you want to install the project and run this command:

​​composer create-project --prefer-dist cakephp/app:~4.0 cakephp_csv

When asked, "Set Folder Permissions ? (Default to Y) [Y,n]?", answer with Y.

This will install the latest version of CakePHP in a new directory named cakephp_csv.

Create the database

To begin, we need a database with a table to store the information which will be exported to a CSV file. To keep things simple, the database will store details about a list of workers, including their name, email address, and mobile phone number.

Create a database. I'll be naming mine fiie_test. The next thing  is to, create a new table in your database called workers using the migrations feature in CakePHP. The table needs to contain the following fields:

  • id: This field will serve as the unique identifier for each record. It should have a type of integer and be the table's primary index, with the auto-increment attribute attached to it.
  • name: This field will store the worker's name. It should have a data type of varchar
  • email: This field will store the worker's email address and have a datatype of varchar
  • mobile: this field will store the worker's mobile phone number, and also have a type of varchar

To do this, open up the terminal and run this command:

bin/cake bake migration CreateWorkers

This will create a migrations file in config/Migrations/. Navigate to the just created migrations file and replace the change() function with this:

public function up(): void
{
    $table = $this->table('workers');

    $table->addColumn('name', 'string', [
        'limit' => 255,
        'null' => false,
    ]);

    $table->addColumn('email', 'string', [
        'limit' => 255,
        'null' => false,
    ]);

    $table->addColumn('mobile_no', 'string', [
        'limit' => 255,
        'null' => false,
    ]);

    $table->create();

    $data = [
        [
            'name' => 'temi tope',
            'email' => 'test@gmail.com',
            'mobile_no' => '1234567895',
        ],
        [
            'name' => 'john doe J',
            'email' => 'john@gmail.com',
            'mobile_no' => '7412589635',
        ],
        [
            'name' => 'babtunde tolulope',
            'email' => 'tolu@gmail.com',
            'mobile_no' => '9632587410',
        ],
        [
            'name' => 'anonymous',
            'email' => 'anon@gmail.com',
            'mobile_no' => '8529637410',
        ],
        [
            'name' => 'oyedele',
            'email' => 'oyedele@gmail.com',
            'mobile_no' => '9658741230',
        ],
        [
            'name' => 'koded',
            'email' => 'koded@gmail.com',
            'mobile_no' => '2635897410',
        ],
        [
            'name' => 'lorem ipsum',
            'email' => 'lorem@gmail.com',
            'mobile_no' => '8526937410',
        ],
        [
            'name' => 'asaolu',
            'email' => 'asaolu@gmail.com',
            'mobile_no' => '8974563210',
        ],
    ];

    $table->insert($data)->save();
}

Next, run this command to create the table schema:

bin/cake migrations migrate

This will not only create a workers table but also insert the data into the database. if you look at the contents of the workers table, it should contain data matching those in the screenshot below.

The Workers table with data inserted via the CakePHP migrations.

Connect to the database

To connect the database to the application, open the project folder in your preferred code editor or IDE and open  config\app_local.php. In the default section, inside the Datasource section, update the default configuration by changing the host, username, password, and database properties to match the credentials of your database. For example:

The data sources configuration section of the CakePHP application.

From the image above, the host was changed to 127.0.0.1, the username to root, the password was left blank, and the database was set to the one created earlier.

Now, start the development server in your terminal, by running this command:

bin/cake server

If you open http://localhost:8765, it should look similar to the screenshot below.

The default CakePHP route showing that the development server meets all the prerequisites, and is ready to run.

Create a model and an entity

To create a model and entity, open up a new terminal and run this command:

bin/cake bake model Workers --no-validation --no-rules

Running this command will create the model file WorkersTable.php inside the src/Model/Table folder. Also, we should see the entity file Workers.php inside the src/Model/Entity folder.

Create a controller

To create a controller, open up the terminal once again and run this command:

bin/cake bake controller Details --no-actions

Running this command will create a file called DetailsController.php file inside the src/Controller folder. Open this file and paste the following into it.

<?php

declare(strict_types=1);

namespace App\Controller;


class DetailsController extends AppController
{
    public function initialize(): void
    {
        parent::initialize();

        $this->loadModel("Workers");
    }

    public function downloadCSVReport()
    {
        $this->autoRender = false;

        $workers = $this->Workers->find()->toList();

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=workers-' . date("Y-m-d-h-i-s") . '.csv');
        $output = fopen('php://output', 'w');

        fputcsv($output, array('Id', 'Name', 'Email', 'Mobile'));

        if (count($workers) > 0) {
            foreach ($workers as $worker) {
                $worker_row = [
                    $worker['id'],
                    ucfirst($worker['name']),
                    $worker['email'],
                    $worker['mobile']
                ];

                fputcsv($output, $worker_row);
            }
        }
    }
}

The code above generates a CSV report of worker data from a database and prompts the browser to download the file. To do this, it initializes the controller, loads a model, and sets HTTP headers for CSV content. It then retrieves worker data from the workers table and writes it to the output stream of the PHP process (php://output); which becomes the body of the response.

Add a Route

Adding a route will be the final step. Navigate to config/routes.php and inside the call to $routes->scope(), paste the following:

$builder->connect(
    '/download-csv',
    ['controller' => 'Details', 'action' => 'downloadCSVReport']
);

Test the application

Now, let’s check that the application works as expected by opening to http://localhost:8765/download-csv in your preferred browser.

You'll notice that a CSV file with the name workers-{datetime}.csv will be downloaded. Its contents should match those in the screenshot below.

A CSV file showing all of the data downloaded from the Workers table.

Conclusion

In this article, we learned how to export data from a database to CSV in CakePHP. By following the steps outlined in this tutorial, you're now equipped with the essential knowledge to perform this task using CakePHP. Happy coding!

Temitope Taiwo Oyedele is a software engineer and technical writer. He likes to write about things he’s learned and experienced.

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.