Custom Export in csv with MSSQL

QuestionsCustom Export in csv with MSSQL
Paolo D. asked 6 years ago

I searched the forum and the DEMO dir of phpgrid and ended up with this piece of code:

$e["on_export"] = array("custom_export", null, false);
$grid->set_events($e);

function custom_export($param)
{
$sql = $param["sql"]; // the SQL statement for export
$grid = $param["grid"]; // the complete grid object reference

if ($grid->options["export"]["format"] == "csv")
{

$server = 'my server';
$connectionInfo = array( "Database"=>"my_db", "UID"=>"my_user", "PWD"=>"my_pwd");

$conn = sqlsrv_connect($server, $connectionInfo);

$fields = array();
foreach ($grid->options["colModel"] as $c)
{
// remove not-to-export columns
if ($c["export"] === false) continue;

$header[$c["name"]] = $c["title"];
$fields[] = $c["name"];
}

$sql = "SELECT ".implode(", ",$fields)." FROM ".$grid->table;
$result = sqlsrv_query($conn, $sql);

if (strstr($grid->options["export"]["filename"],".csv") === false)
$grid->options["export"]["filename"] .= ".csv";

header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename='.$grid->options["export"]["filename"]);

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

// push rows header
fputcsv($fp, $header);

// push rows
while ($row = sqlsrv_fetch_array($result))
fputcsv($fp, $row);

die;

}
}

but if I insert the code instead of the csv file I get a "cannot find the page" error.

On top of that, should I decide to change the records extracted would be correct to change this line
$sql = "SELECT ".implode(", ",$fields)." FROM ".$grid->table;
in this way?
$sql = "SELECT ".implode(", ",$fields).", test.testfield FROM ".$grid->table "left outer join test on ".$grid->table ".primarykey =test.primarykey ";

Thanks

3 Answers
Abu Ghufran answered 6 years ago

Looks like some debugging in this function is required.
You can try printing sqlsvr error.
http://php.net/sqlsrv_errors

Paolo L. answered 5 years ago

The grid I'm trying to export is the detail grid in a 3 level structure
Master -> Detail -> Detail / Detail

Maybe that's the problem?

Paolo L. answered 5 years ago

Fixed it. I had to change the SQL statement from:

$sql = "SELECT ".implode(", ",$fields)." FROM ".$grid->table;

to:

$sql="Select CALL_ID, CALL_TYPE_ID, CALL_CLIENT_CODE, CALL_ITEM_ID, CALL_STATUS_ID, convert (varchar(12), CALL_DATE) as CALL_DATE, convert(varchar(8), CALL_HOUR,108) as CALL_HOUR, CALL_SUBJECT, CALL_USER_OPEN, CALL_ASSIGNED_TO from CALLS WHERE CALL_CLIENT_CODE = '$id'";

Since the CALL_DATE and CALL_HOUR fields (without the opportune convert function) generated the error message I reported

I also added in the function:
global $id;
to get the client code selected in the detail grid

Your Answer

6 + 14 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?