06-14-2010, 03:12 AM
[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";
}
}
?>
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";
}
}
?>