How to export data from MySQL using Codeigniter4 Database utility

CI4 provides the database utilities class that contains methods to manage database.
We can make use of the getCSVFromResult method to get the results as per CSV format or getXMLFromResult to get the results in XML format.

For CSV method below is an example.

1.CREATE dummy table with some values.

CREATE TABLE `user` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `country` varchar(100) default NULL,
  `region` varchar(50) default NULL,
  `postalZip` varchar(10) default NULL,
  `phone` varchar(100) default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;


INSERT INTO `user` (`name`,`email`,`country`,`region`,`postalZip`,`phone`)
VALUES
  ("Quamar Avila","nonummy.ac@aol.org","Germany","Bình Phước","57238","1-668-477-3499"),
  ("Yoko Clarke","nunc.nulla.vulputate@outlook.ca","South Africa","Niger","B3C 2H2","1-526-636-1356"),
  ("Garrison Barron","dui.augue@icloud.couk","Peru","Sląskie","455183","(124) 327-5046"),
  ("Noah Newman","nullam.vitae@outlook.com","Brazil","Niger","01765","(534) 524-8743"),
  ("Cruz Vaughn","eu.accumsan@yahoo.net","Colombia","UK","82-96","(466) 908-3438");

2. Create Controller File GetCsv.php under App\Controllers folder

<?php namespace App\Controllers;

use CodeIgniter\Controller;
use App\Models\GetCsvModel;
class GetCsv extends Controller
{
    function __construct()
	{
        $this->utilModel = new GetCsvModel();
	} 
    public function getCsv(){
        $output =$this->utilModel->getUsers();
        $filename = 'users.csv'; 
        header("Content-Description: File Transfer"); 
        header("Content-Disposition: attachment; filename=$filename"); 
        header("Content-Type: application/csv; ");
        $file = fopen('php://output', 'w');
        fwrite($file,$output);
        fclose($file);
        exit;
    }

     public function getXml(){
        $output =$this->utilModel->getUsersXml();
        $filename = 'users.xml'; 
        header("Content-Description: File Transfer"); 
        header("Content-Disposition: attachment; filename=$filename"); 
        header("Content-Type: application/xml; ");
        $file = fopen('php://output', 'w');
        fwrite($file,$output);
        fclose($file);
        exit;
    }
}

3. Create Model File GetCsvModel.php under App\Models folder .

<?php namespace App\Models;

use CodeIgniter\Model;

class GetCsvModel extends Model{
    protected $table         = 'user';
    protected $primaryKey = 'id';  
    
    public function getUsers($a = array()){
        $this->dbutil = \CodeIgniter\Database\Config::utils();
        $response = array();
        $q = "select u.id,u.email,u.region,u.phone from user u";
        $query = $this->query($q);
        return $this->dbutil->getCSVFromResult($query);
    }

    public function getUsersXml($a = array()){
        $this->dbutil = \CodeIgniter\Database\Config::utils();
        $response = array();
        $q = "select u.id,u.email,u.region,u.phone from user u";
        $query = $this->query($q);
        return $this->dbutil->getXMLFromResult($query);
    }
}

To invoke the inbuilt utility we have written the line in Model as mentioned above

$this->dbutil = \CodeIgniter\Database\Config::utils();

4. Next, add a routing path for the code under App\Config\Routes.php

$routes->get('/dbutils/getcsv', 'GetCsv::getCsv');
$routes->get('/dbutils/getxml', 'GetCsv::getXml');

5. Run the code on localhost:8080/dbutils/getcsv, you will be able to download the query result in a csv file and
on localhost:8080/dbutils/getxml you will be able to see the XML file download.