Howto use mysql format fucntion to export data from mysql to excell |
[eluser]Unknown[/eluser]
I'm using to_excell_pi plugin to export data in mysql table into excell / csv format. But when I put format function in select query, it generates error "You have an error in SQL syntax" $this->db->select('type,cost') => export successfully $this->db->select('type,format(cost,3)) => error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`3)` The purpose why I'm using format function is to replace '.' symbol into ',' Any idea how to solve this problem ? to_excell_pi.php ( export excell plugin ) ----------------------------------------------------------------------------------- <?php if (!defined('BASEPATH')) exit('No direct script access allowed'); /* * Excel library for Code Igniter applications * Author: Derek Allard, Dark Horse Consulting, www.darkhorse.to, April 2006 */ function to_excel($query, $filename='exceloutput') { $headers = ''; // just creating the var for field headers to append to below $data = ''; // just creating the var for field data to append to below $obj =& get_instance(); $fields = $query->field_data(); if ($query->num_rows() == 0) { echo '<p>The table appears to have no data.</p>'; } else { foreach ($fields as $field) { $headers .= $field->name . "\t"; } foreach ($query->result() as $row) { $line = ''; foreach($row as $value) { if ((!isset($value)) OR ($value == "")) { $value = "\t"; } else { $value = str_replace('"', '""', $value); $value = '"' . $value . '"' . "\t"; } $line .= $value; } $data .= trim($line)."\n"; } $data = str_replace("\r","",$data); header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=$filename.xls"); echo "$headers\n$data"; } } ?> |
Welcome Guest, Not a member yet? Register Sign In |