wordpress rest api send empty json file

Question

excuse me :: my english writing is very bad :)) I am developing a wordpress plugin and i need to get some row from custom table by rest api and excel format. by this codes, excel file create in wordpress root and query works, but i cant download this when request address, when request address, excell file that downloaded is empty and just writing "resourse ... " in it please help me, thanks

add_action( 'rest_api_init', 'wpc_register_wp_api_endpoints' );

function wpc_register_wp_api_endpoints() {

register_rest_route( 'api', '/output/', array(
    'methods' => 'GET',
    'callback' => 'wpc_somename_search_callback',
    'args' => array(
            'date' => array(
                    'required' => true,
                )
        )
));   
}

function wpc_somename_search_callback( $request ) {

    global $wpdb;
    $date = $request['date'];
    $query = "SELECT * FROM {$wpdb->prefix}tickets where inroute_id=16 and date='$date'";
    $list = $wpdb->get_results($query);

    $jsonDecoded = json_decode( json_encode($list), true);

    $fileName = 'example.csv';

    $fp = fopen($fileName, 'w');

    foreach($jsonDecoded as $row){

        fputcsv($fp, $row);
    }

    fclose($fp);

    header('Content-disposition: attachment; filename='.$fileName);
    header('Content-type: application/json');

    print $fp;

    exit; 
}

my request is

http://yootaabmarine.ir/wp-json/api/output/?date=13970822

Answer 1

I think that $jsonDecoded = json_decode( json_encode($list), true); is useless in your code, you are transforming an array to a JSON string and then transforming it back to an array, it is better to use $list directly.

$fp contains a resource, which is a PHP type in its own (see in the doc fopen function return type is resource), and a resource is completely different from a string, so you can't use print $fp;.

We could use readfile to correctly send a file to the browser, here is a solution:

function wpc_somename_search_callback( $request ) {

    global $wpdb;
    $date = $request['date'];
    $query = "SELECT * FROM {$wpdb->prefix}tickets where inroute_id=16 and date='$date'";
    $list = $wpdb->get_results($query);

    $fileName = 'example.csv';

    $fp = fopen($fileName, 'w');

    foreach($list as $row){

        fputcsv($fp, $row);
    }

    fclose($fp);

    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="'. $fileName .'"');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($fileName));
    readfile($fileName);

    exit; 
}

Beware of SQL injections, use prepared queries instead.