Sparky Sparky - 2 months ago 6
MySQL Question

Manipulate data when exporting to CSV?

I'm using CodeIgniter v2.2.4

Consider the following code to export/download a CSV file representing results from a database query.

CONTROLLER:

public function export_csv($id = NULL)
{
$this->load->dbutil();
$this->load->helper('download');

$query = $this->my_model->create_csv($id); // call the model

$data = $this->dbutil->csv_from_result($query, ',');

force_download( $id . '.csv', $data );
}


MODEL:

public function create_csv($id)
{
$this->db->from('mytable');

$this->db->where('id', $id);

$this->db->select('
id AS `ID`,
full_name AS `Full Name`,
company_name AS `Company Name`,
phone AS `Phone Number`,
select_list AS `User Options`
', FALSE);

return $this->db->get();
}


The code above is working, however the
select_list
value is
0
,
1
, or
2
and that's inserted into the CSV export. How can I manipulate these values into more meaningful text for my final CSV file?

Example:

'DB value' => 'insert into CSV'
0 => 'N/A',
1 => 'foo',
2 => 'bar'

Answer

Easiest way is to use a select statement along these lines

$this->db->select(
   "id            AS `ID`,
    full_name     AS `Full Name`,
    company_name  AS `Company Name`,
    phone         AS `Phone Number`,
    CASE select_list WHEN = 1 THEN 'foo' WHEN = 2 THEN 'bar' ELSE 'N/A' END
    AS `User Options`", FALSE);

CANCEL THAT! It is wrong.

It should be as follows when using the optional expression after CASE, i.e. CASE select_list ...

$this->db->select(
   "id            AS `ID`,
    full_name     AS `Full Name`,
    company_name  AS `Company Name`,
    phone         AS `Phone Number`,
    CASE select_list WHEN 1 THEN 'foo' WHEN 2 THEN 'bar' ELSE 'N/A' END
    AS `User Options`", FALSE);

I was mixing the different forms the statement can use in the first (incorrect) example.