How to Export data in CSV format – CodeIgniter 4

CSV (comma-separated values) is a widely used format to import and export data between applications.

With fputcsv() function write data in the CSV file.

In this tutorial, I show how you can export MySQL database data in CSV format in CodeIgniter 4.

1. Database configuration

• Open .env file which is available at the project root.

NOTE – If dot (.) not added at the start then rename the file to .env.

• Remove # from start of database.default.hostname, database.default.database, database.default.username, database.default.password, and database.default.DBDriver.
• Update the configuration and save it.

database.default.hostname = 127.0.0.1
database.default.database = testdb
database.default.username = root
database.default.password = 
database.default.DBDriver = MySQLi

2. Create Table

• Create a new table users using migration.

php spark migrate:create create_users_table

• Now, navigate to app/Database/Migrations/ folder from the project root.
• Find a PHP file that ends with create_users_table and open it.
• Define the table structure in the up() method.
• Using the down() method delete users table which calls when undoing migration.

<?php namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateUsersTable extends Migration
{
    public function up() {
       $this->forge->addField([
          'id' => [
              'type' => 'INT',
              'constraint' => 5,
              'unsigned' => true,
              'auto_increment' => true,
          ],
          'name' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'email' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
          'city' => [
              'type' => 'VARCHAR',
              'constraint' => '100',
          ],
       ]);
       $this->forge->addKey('id', true);
       $this->forge->createTable('users');
    }

    //--------------------------------------------------------------------

    public function down() {
       $this->forge->dropTable('users');
    }
}

• Run the migration –

php spark migrate

3. Model

• Create Users.php file in app/Models/ folder.
• Open the file.
• Specify table name "users" in $table variable, primary key "id" in $primaryKey, Return type "array" in $returnType.
• In $allowedFields Array specify field names – ['name', 'email','city'] that can be set during insert and update.

Completed Code

<?php 
namespace App\Models;

use CodeIgniter\Model;

class Users extends Model
{
    protected $table = 'users'; 
    protected $primaryKey = 'id';

    protected $returnType = 'array';

    protected $allowedFields = ['name', 'email','city'];
    protected $useTimestamps = false;

    protected $validationRules = [];
    protected $validationMessages = [];
    protected $skipValidation = false;

}

4. Route

• Open app/Config/Routes.php file.
• Define 2 routes –
  • / – Display user list and export link.
  • exportData – It is used to create and download CSV file.

Completed Code

$routes->get('/', 'UsersController::index');
$routes->get('exportData', 'UsersController::exportData');

5. Controller

• Create UsersController.php file in app/Controllers/ folder.
• Open the file.
• Import Users Model.
• Create two methods –
  • index() – Select all records from the users table and assign to $data['users']. Load index view and pass $data.
  • exportData() – This method is use to create and download CSV file.

Assign file name to $filename variable and set header requests for file download.

Fetch all records from the users table and assign to $usersData.

Create a file and write column names using fputcsv().

$header = array("ID","Name","Email","City"); 
fputcsv($file, $header);

Skip this step if you don’t want a header row.

Loop on the fetched data and write to the file.

Close the file.

Completed Code

<?php namespace App\Controllers;

use App\Models\Users;

class UsersController extends BaseController{

   public function index(){

     // Get data 
     $users = new Users();
     $data['users'] = $users->select('*')->findAll();

     return view('index',$data);
   }

   public function exportData(){ 
     // file name 
     $filename = 'users_'.date('Ymd').'.csv'; 
     header("Content-Description: File Transfer"); 
     header("Content-Disposition: attachment; filename=$filename"); 
     header("Content-Type: application/csv; ");

     // get data 
     $users = new Users();
     $usersData = $users->select('*')->findAll();

     // file creation 
     $file = fopen('php://output', 'w');

     $header = array("ID","Name","Email","City"); 
     fputcsv($file, $header);
     foreach ($usersData as $key=>$line){ 
        fputcsv($file,$line); 
     }
     fclose($file); 
     exit; 
   }
}

6. View

Create index.php file in app/Views.

Add anchor tag to display export link – <?= site_url('exportData') ?>.

Loop on the $users Array to list records.

Completed Code

<!DOCTYPE html>
<html>
<head>
   <title>How to export data in CSV format - CodeIgniter 4</title>

   <meta name="viewport" content="width=device-width, initial-scale=1.0">

</head>
<body>

   <!-- Export Data --> 
   <a href='<?= site_url('exportData') ?>'>Export</a><br><br>

   <!-- User Records --> 
   <table border='1' style='border-collapse: collapse;'> 
     <thead> 
       <tr> 
         <th>ID</th> 
         <th>Name</th> 
         <th>Email</th> 
         <th>City</th> 
       </tr> 
     </thead> 
     <tbody> 
     <?php 
     if(isset($users) && count($users) > 0){
       foreach($users as $user){
     ?>
         <tr>
           <td><?= $user['id'] ?></td>
           <td><?= $user['name'] ?></td>
           <td><?= $user['email'] ?></td>
           <td><?= $user['city'] ?></td> 
         </tr>
     <?php
       }
     }else{
     ?>
        <tr>
          <td colspan="5">No record found.</td>
        </tr>
     <?php
     }
     ?>
     </tbody> 
   </table>
</body>
</html>

7. Run

• Navigate to the project using Command Prompt if you are on Windows or terminal if you are on Mac or Linux, and
• Execute “php spark serve” command.

php spark serve

• Run http://localhost:8080 in the web browser.

8. Export file structure

Exported file has following structure –

ID,Name,Email,City
1,"Yogesh singh",yogesh@makitweb.com,Bhopal
2,"Sonarika bhadoria",bsonarika@makitweb.com,Pune
3,"Sunil singh",sunil@makitweb.com,Indore
4,"Jitendra singh",jiten@makitweb.com,Bhopal

9. Demo

View Demo

10. Conclusion

You can dump your MySQL database data in CSV format and later use it in the application or use it for backup.

View this tutorial, If you want to know CSV file data import in CodeIgniter 4.