export mysql data to excel using php ajax call

Question

i am doing an ajax call and called a php page which is extracting data from a mysql table and need to convert it to excel and need to store in server folder only.

Now the problem is while doing the ajax call, its going to that php page and returning to the main page without creating the excel file.

But when i tested the php page directly then its creating the excel file and downloading it. But why its not working via ajax call.

This is my ajax call from previous page --

$.ajax({
    type: "POST",
    url: "admin-advertiser-details-exports.php?selectedColumns="+selectedColumns+"&excelColumn="+excelColumn,
    contentType:false,  
    processData:false,  
    success:function(data){  
        alert('Export done');  
    }
});

And this is my php page which is exporting to excel --

<?php
    session_start();
    include 'db.php';

    if(!isset($_SESSION))
    {
        $uname=$_SESSION['uname'];
    }
    else if(empty($_SESSION['uname']))
    {
        header('Location: index.php',TRUE);
    }

    $UserName = $_SESSION['uname'];
    $UserID = $_SESSION['uid'];
    $UserType = $_SESSION['utype'];

    $selectedColumns = $_GET['selectedColumns'];
    $excelColumn = $_GET['excelColumn'];

    $array = explode('\t',$excelColumn);

    $sql = "select ".$selectedColumns." from advertisers_details ad join user_details ud on ad.adv_manager=ud.user_id order by ad.adv_id asc";

    $setRec = mysqli_query($linkID1, $sql);
    $columnHeader = '';

    foreach ($array as $value) {
        $value = '"' . $value . '"' . "\t";
        $columnHeader .= $value;
    }

    $setData = '';

    while ($rec = mysqli_fetch_row($setRec)) 
    { 
        $rowData = ''; 

        foreach ($rec as $value) 
        { 
            $value = '"' . $value . '"' . "\t"; 
            $rowData .= $value; 
        }

        $setData .= trim($rowData) . "\n"; 
    }

    header("Content-type: application/octet-stream"); 
    header("Content-Disposition: attachment; filename=advertiser_detail.xls"); 
    header("Pragma: no-cache"); 
    header("Expires: 0"); 
    echo ucwords($columnHeader) . "\n" . $setData . "\n";
?>

If i am running this php page directly the i am getting the excel file, but via ajax call its not working.

you are using POST but passing data in the url (via GET). Also if you get a redirect you are probably not preventing the default action for the element that trigger the ajax (the form?) Show us the full js where you trigger the ajax — user2399035
– user2399035, Commented Apr 18, 2018 at 9:20
"need to store in server folder only."...that PHP is programmed to send the file for download, not save it to the server. So you need to change the PHP, if that's the behaviour you need. And downloading files via ajax does not work well, because the data returned (i.e. the excel document data) ends up in the response Javascript variable, not as a file on the client's computer, and that is a prison from which it cannot easily escape. If you want to trigger a download via Javascript, better to do a window.location or window.open command to the URL which prompts the download. — ADyson
– ADyson, Commented Apr 18, 2018 at 9:22
@ADyson ok lets say i want to download only, then also its not working bro — Rana Pratap
– Rana Pratap, Commented Apr 18, 2018 at 9:24
But that contradicts with your stated aim to "save on the server". If a donwload is actually what you want (in which case, change your question text to be accurate), then see my above edited comment for why it doesn't download via ajax. That kind of question gets asked a lot on SO as well, you can probably find lots of previous material relating to it, with similar explanations, if you search. — ADyson
– ADyson, Commented Apr 18, 2018 at 9:24

Deepak Lakhara · Accepted Answer · 2019-01-01 09:58:19Z

Hope this example work best for you. I have used window.open and set url of target file with parameter.

JS code used

/* Export Orders */
$(document).on('click','#btn_export_delivery_order',function(){       
        var first_input = $('#name_email_order_search').val();
        var date_range = $('#reportrangeorder').val();        
        var order_status = $('#order_status').val();                
        window.open("ajax_responce.php?method_name=export_orders&shop="+ shop+"&first_input=" + first_input + "&date_range=" + date_range + "&order_status=" + order_status + "", '_blank');
});

Code used in ajax_response.php

public function export_orders(){ ob_start();

        if (isset($_GET['shop']) && $_GET['shop'] != '') {
            $export = array();
            $export_data = array();
            $shopinfo = $this->get_settings($_GET['shop']);
            $store_client_id = $shopinfo['store_client_id'];
            $date_format = $shopinfo['date_format_first'];

            $options_arr = array("skip" => 0, "limit" => 99999);            
            $where = $this->where_to_clause_export_search($store_client_id, $_GET);
            $fullfilment_status_array = array('Unfulfilled','Fullfill','Partially fullfill','Cancelled');

            $export_result = $this->select_result(TABLE_ORDERS, '*', $where, $options_arr);

            foreach($export_result['data'] as $row){
                    $export['Name'] = $row['order_name'];
                    $export['Billing Name'] = $row['first_name'] . ' ' . $row['last_name'];
                    $export['Email'] = $row['email'];
                    $export['Fulfillment Status'] = $fullfilment_status_array[$row['status']];
                    $export['Total'] = $row['total'];
                    $export['Created at'] = date($date_format, strtotime($row['created_at']));
                    $export['Delivery Status'] = $row['delivery_status'] == 0 ? 'Pending' : 'Delivered';
                    $export['Delivery Date'] = date($date_format, strtotime($row['delivery_date']));
                    $export['Delivery Time'] = (isset($row['delivery_time']) && $row['delivery_time'] != '') ? $row['delivery_time'] : '-';
                    $export_data[] = $export;
            }

            /* Export Data */
            $filename = "orders_export.csv";
            $f = fopen('php://output', 'w');
            header('Content-type: application/csv');
            header('Content-Disposition: attachment; filename=' . $filename);
            $outputdata = $export_data;

            $firstLineKeys = false;
            foreach ($outputdata as $row) {
                if (empty($firstLineKeys)) {
                    $firstLineKeys = array_keys($row);
                    fputcsv($f, $firstLineKeys);
                    $firstLineKeys = array_flip($firstLineKeys);
                }
                fputcsv($f, $row);
            }
            fclose($f);
            ob_end_flush();
            exit();
        }
    }

This code is tested well and working fine.

Collectives™ on Stack Overflow

export mysql data to excel using php ajax call

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related