Split array data and export to multiple CSV

[jthan03]

Good day to everyone,

 

I would like to is there a possible solution for getting the array data into the database then split it into multiple array of data and then export it into multiple CSV file?

 

Here is the code that I use:

 

download.php

   public function downloadCSVbyBatch($batch){
//        $data['keywordIDs'] = $this->ItemsearchModel->getConcatKeywordIDbyBatch($batch);
       $keys = str_replace("-", ",", $batch);
//        echo $keys;
//        $key = $this->ItemsearchModel->getSearchResultsbyBatch($batch);


       $data['results'] = $this->ItemsearchModel->getSearchResultsWhereIN($keys);
//  var_dump($data['results']);
//        print_r($data['results']);
//            echo $data['results']['keywords'];
       if($data['results']){
           foreach($data['results'] as $result){
               $data['csvlines'][] = $result;
           }
       }
//        echo '<pre>';
//        print_r($data);
//        echo '</pre>';
       $this->load->view("csv", $data);
       header("Content-type: application/download");
 header("Content-disposition: filename=csvResults.csv");
 header("Content-Transfer-Encoding: UTF-8");

   }

 

then here is the query and the csv format:

query

   public function getSearchResultsWhereIN($keyIDs){
       $keyIDs = substr($keyIDs,0,-1);
       $query = $this->db->query("SELECT serial_number,search_id,price,whether_tax,item_code,product_name,item_description,product_url,affiliate_url,site_name,store_name,store_url,image_url,keyword_search_1,keyword_search_2,price_from,price_to
           FROM result
           WHERE serial_number IN  ($keyIDs)");

       if($query->num_rows()>=1){
           return $query->result_array();
       }else{
           return FALSE;
       }
   }

 

csv.php

<?php


echo 'serial_number,search_id,price,whether_tax,item_code,product_name,item_description,product_url,affiliate_url,site_name,store_name,store_url,image_url,keyword_search_1,keyword_search_2,price_from,price_to," "'."\n";
foreach($csvlines as $csv){
   echo implode(",", $csv)."\n";


}
?>

 

 

 

Your help will be so much appreciated.

[PFMaBiSmAd]

You can only download ONE file per download link. You would need to add a GET parameter (similar to pagination) to each download link that specifies which block of data it should output. Your code would then use that GET parameter to determine the correct rows that the database query should retrieve.

 

The only way you could download multiple csv files at once, would be to create a .zip archive containing all the csv files.

 

Why do you need to do this using multiple .csv files?

[jthan03]

You can only download ONE file per download link. You would need to add a GET parameter (similar to pagination) to each download link that specifies which block of data it should output. Your code would then use that GET parameter to determine the correct rows that the database query should retrieve.

 

The only way you could download multiple csv files at once, would be to create a .zip archive containing all the csv files.

 

Why do you need to do this using multiple .csv files?

its because the number of rows are too many and when I clicked the download button, it just displays all the data in the web itself rather than having a csv file given all the data.

[PFMaBiSmAd]

Are you sure the problem is the size? It's more likely that your framework is outputting something before the headers, which prevents them from working, and the browser doesn't know what to do with the csv data.

 

Do you have php's error_reporting set to E_ALL and display_errors set to ON, so that all the php detected errors will be reported and displayed?

[jthan03]

Are you sure the problem is the size? It's more likely that your framework is outputting something before the headers, which prevents them from working, and the browser doesn't know what to do with the csv data.

 

Do you have php's error_reporting set to E_ALL and display_errors set to ON, so that all the php detected errors will be reported and displayed?

yes, it's all ON... here is the whole code of the download.php and the model.php for further understanding:

download_full.php:

<?php


if (!defined('BASEPATH'))
   exit('No direct script access allowed');


class Download extends CI_Controller {


   public function __construct() {
       parent::__construct();
       $this->load->model("ItemsearchModel");


       $this->logged = $this->session->userdata('logged');
       if ($this->logged == FALSE) {
           redirect('login', 'refresh');
       }
   }


   public function index() {
       $data['message'] = FALSE;
       $data["batch"] = $this->session->userdata('batchnum');
//        echo $batch.'-------';
       $data['keyIDs'] = FALSE;
//        var_dump($batch);
       if (!$data["batch"]) {
           $data["batch"] = $this->ItemsearchModel->getLastBatchNumber();
           if (!$data["batch"]) {
               $data['message'] = "No uploaded CSV file.";
               $data['mtype'] = "warning";
           } else {
               $data['keyIDs'] = $this->ItemsearchModel->getKeyitemIDs($data["batch"]);
           }
       } else {
           $data['keyIDs'] = $this->ItemsearchModel->getKeyitemIDs($data["batch"]);
       }
       $data['batchbefore'] = $this->ItemsearchModel->getBatchItems($data["batch"]-1);
       $data['batchafter'] = FALSE;
//        echo $data['message'].'-----------';
//        echo $data['keyIDs'];
//        $data['message'] = FALSE;


       $this->template->write_view('section', 'download', $data, TRUE);
       $this->template->render();
   }


   public function search_engines($id) {
       $data['keywordInfo'] = $this->ItemsearchModel->getKeywordInfo($id);
//        $this->ItemsearchModel->clearSearchResultsbyKewordID($id);
       if ($data['keywordInfo'] && isset($data['keywordInfo']['search_keyword_1'])) {
           if(!$this->ItemsearchModel->checkifSearched($data['keywordInfo'])){
               $yahooResult = $this->apisearchengine->yahooSearchAPI($data['keywordInfo']);
               if ($yahooResult) {
                   $this->ItemsearchModel->saveResults($yahooResult);
               }
               $conecoResult = $this->apisearchengine->conecoSearchAPI($data['keywordInfo']);
               if ($conecoResult) {
                   $this->ItemsearchModel->saveResults($conecoResult);
               }
               $biddersResult = $this->apisearchengine->biddersSearchAPI($data['keywordInfo']);
               if ($biddersResult) {
                   $this->ItemsearchModel->saveResults($biddersResult);
               }
               $amazonResult = $this->apisearchengine->amazonSearchAPI($data['keywordInfo']);
   if ($amazonResult) {
                   $this->ItemsearchModel->saveResults($amazonResult);
               }
               $rakutenAuctionResult = $this->apisearchengine->rakutenSearchAPI($data['keywordInfo']);
               if ($rakutenAuctionResult) {
                   $this->ItemsearchModel->saveResults($rakutenAuctionResult);
               }
               $rakutenResult = $this->apisearchengine->rakutenSearchAPI($data['keywordInfo']);
               if ($rakutenResult) {
                   $this->ItemsearchModel->saveResults($rakutenResult);
               }
//                echo '<pre>';
//                print_r($yahooResult);
//                echo '</pre>';
//                $this->load->view('returnResults', $data);
           }
       }
   }

   public function table_footer($batch){
       $data["batch"] = $batch;
       $this->load->view('table_footer', $data);
   }

   public function viewbatch($batchid,$offset=0,$limit=10){
//        $batchid = 1;
//        $this->uri_segment=0;
//        $limit =10;
       $data['limit'] = $limit;
       $data['message'] = FALSE;
       $data['batch'] = $batchid;
       $data['batchbefore'] = $this->ItemsearchModel->getBatchItems($data["batch"]-1);
       $data['batchafter'] = $this->ItemsearchModel->getBatchItems($data["batch"]+1);
       $data['keyIDs'] = $this->ItemsearchModel->getKeyitemIDs($data["batch"]);
       $data['totalrows'] = $this->ItemsearchModel->getItemsbyBatch($batchid,NULL,NULL);
       $data['keywordInfo'] = $this->ItemsearchModel->getItemsbyBatch($batchid,$limit,$offset);

       //create pagination
       $config['base_url'] = BASEURL.'download/viewbatch/'.$batchid.'/';
       $config['total_rows'] = count($data['totalrows']);
       if($offset==0){
           $config['cur_page'] = 0;
       }else{
           $config['cur_page'] = $offset/$limit;
       }
       $config['suffix'] = '/'.$limit;

//      echo count($data['totalrows']);exit;
       $config['per_page'] = $limit;
       $config['num_links'] = 3;
       $this->pagination->initialize($config);
       $data['pagination'] = $this->pagination->create_links();


//        print_r($config);

       $this->template->write_view('section', 'downloadcomplete', $data, TRUE);
       $this->template->render();
   }

   public function downloadCSVbyKeyword($keyword){
       $data['results'] = $this->ItemsearchModel->getSearchResults($keyword);
//        var_dump($data['results']);
       if($data['results']){
           foreach($data['results'] as $result){
               $data['csvlines'][] = $result;
           }
       }
//        echo '<pre>';
//        print_r($data['results']);
//        echo '</pre>';

       $this->load->view("csv", $data);
       header("Content-type: application/download");
header("Content-disposition: filename=$keyword.csv");
header("Content-Transfer-Encoding: UTF-8");
   }

   public function downloadCSVbyWhereIn($keyword_ids){
//        $data['keywordIDs'] = $this->ItemsearchModel->getConcatKeywordIDbyBatch($batch);
       $keys = str_replace("-", ",", $keyword_ids);
//        echo $keys;
       $data['results'] = $this->ItemsearchModel->getSearchResultsWhereIN($keys);
       if($data['results']){
           foreach($data['results'] as $result){
               $data['csvlines'][] = $result;
           }
       }
//        echo '<pre>';
//        print_r($data['results']);
//        echo '</pre>';

       $this->load->view("csv", $data);
       header("Content-type: application/download");
header("Content-disposition: filename=csvResults.csv");
header("Content-Transfer-Encoding: UTF-8");
   }

   public function downloadCSVbyBatch($batch){
//        $data['keywordIDs'] = $this->ItemsearchModel->getConcatKeywordIDbyBatch($batch);

        $keys = str_replace("-", ",", $batch);    
//        echo $keys;
//       $key = $this->ItemsearchModel->getSearchResultsbyBatch($batch);


       $data['results'] = $this->ItemsearchModel->getSearchResultsWhereIN($keys);
//  var_dump($data['results']);exit;
//        print_r($data['results']);
//            echo $data['results']['keywords'];
 //var_dump(count($data['results']) <= 1000);exit;
       if($data['results']){
   foreach($data['results'] as $result){
 $data['csvlines'][] = $result;
           }    
 }

//        echo '<pre>';
//        print_r($data);
//        echo '</pre>';exit;
 $this->load->view("csv", $data);
       header("Content-type: application/download");
 header("Content-disposition: filename=csvResults.csv");
 header("Content-Transfer-Encoding: UTF-8");
   }


   public function clearlist($batch){
       $keywords = $this->ItemsearchModel->getConcatKeywordIDbyBatch($batch);
       $this->ItemsearchModel->clearResult($keywords);
       redirect('upload','refresh');
   }

   public function load_allResults($batch){
       $keywordInfo = $this->ItemsearchModel->getItemsbyBatch($batch,NULL,NULL);
//        print_r($keywordInfo);
//        $key = "";
//        foreach($keywords as $keys){
//            if($key==""){
//                $key = $keys['keyword_id'];
//            }else{
//                $key = $key.','.$keys['keyword_id'];
//            }
//        }
//        echo $key;
//        $keywordInfo = $this->ItemsearchModel->getKeywordInfoWherein($key);
//        if($keywordInfo){
           foreach($keywordInfo as $k){
               $data['keywordInfo'] = $k;
               $this->load->view('returnResults', $data);
           }
//        }

       $this->table_footer($batch);

   }
//    public function


}


/* End of file welcome.php */
/* Location: ./application/controllers/welcome.php */

 

ItemsearchModel.php

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');


class ItemsearchModel extends CI_Model {


   public function __construct() {
       parent::__construct();
       $this->db = $this->load->database('default',TRUE);
   }


   public function getLastBatchNumber(){
       $query = $this->db->query("SELECT MAX(batch) as batchnum FROM request");
       if($query->num_rows()>=1){
           $query = $query->row_array();
           return $query['batchnum'];
       }else{
           return FALSE;
       }


   }


   public function saveRequest($request){
       mysql_set_charset('utf8');
       $this->db->insert_batch('request',$request);
   }


   public  function getKeyitemIDs($batch){
       $this->db->where("batch",$batch);
       $query = $this->db->get("request");


       $keyID = "";
       foreach($query->result_array() as $keyword){
           if($keyID==""){
               $keyID = $keyword['keyword_id'];
           }else{
               $keyID = $keyID.'@@'.$keyword['keyword_id'];
           }
       }
       return $keyID;
   }


   public function getKeywordInfo($id){
       $this->db->where('keyword_id',$id);
       $query = $this->db->get('request');
       if($query->num_rows()>=1){
           return($query->row_array());
       }else{
           return FALSE;
       }
   }

   public function saveResults($result){
       mysql_set_charset('utf8');
       $this->db->insert_batch("result",$result);
   }

   public function getBatchItems($batch){
       $this->db->where("batch",$batch);
       $query = $this->db->get("request");
       if($query->num_rows()>=1){
           return $batch;
       }else{
           return FALSE;
       }
   }

   public function getSearchResults($keyword){
       $query = $this->db->query("SELECT serial_number,search_id,price,whether_tax,item_code,product_name,item_description,product_url,affiliate_url,site_name,store_name,store_url,image_url,keyword_search_1,keyword_search_2,price_from,price_to
           FROM result
           WHERE serial_number = $keyword");

       if($query->num_rows()>=1){
           return $query->result_array();
       }else{
           return FALSE;
       }
   }

   public function getConcatKeywordIDbyBatch($batch){
       $query = $this->db->query("SELECT GROUP_CONCAT(keyword_id) as keywords FROM request WHERE batch = $batch");

       if($query->num_rows()>=1){
           return $query->result_array();
       }else{
           return FALSE;
       }
   }

   public function getSearchResultsWhereIN($keyIDs){
       $keyIDs = substr($keyIDs,0,-1);
       $query = $this->db->query("SELECT serial_number,search_id,price,whether_tax,item_code,product_name,item_description,product_url,affiliate_url,site_name,store_name,store_url,image_url,keyword_search_1,keyword_search_2,price_from,price_to
           FROM result
           WHERE serial_number IN  ($keyIDs)");

       if($query->num_rows()>=1){
           return $query->result_array();
       }else{
           return FALSE;
       }
   }

   public function clearResult($keywords){
//        $this->db->query("DELETE FROM request WHERE keyword_id IN ($keywords)");
//        $this->db->query("DELETE FROM result WHERE serial_number IN ($keywords)");
       $this->db->query("TRUNCATE request");
       $this->db->query("TRUNCATE result");
   }

   public function clearSearchResultsbyKewordID($keyword_id){
       $this->db->where("serial_number",$keyword_id);
       $this->db->delete("result");
   }

   public function checkifSearched($keywordInfo){
       $this->db->where("serial_number",$keywordInfo['keyword_id']);
       $query = $this->db->get("result");
       if($query->num_rows()>=1){
           return TRUE;
       }else{
           return FALSE;
       }
   }

   public function getKeywordInfoWherein($keywords){
//        print_r(
//        $keywords = implode(",",$keywords);
       $query = $this->db->query("SELECT * FROM result WHERE serial_number IN ($keywords)");
       return $query->result_array();
   }

   public function getItemsbyBatch($batch,$limit=NULL,$offset=NULL){
       if($limit==NULL&&$offset==NULL){
           $limitoffset = "";
       }else{
           $limitoffset = "LIMIT $limit OFFSET $offset";
       }

       $query = $this->db->query("SELECT * FROM request WHERE batch = $batch $limitoffset");

//        $this->db->where("batch",$batch);
//        $query = $this->db->get("request");
       if($query->num_rows()>=1){
           return $query->result_array();
       }else{
           return FALSE;
       }
   }
}


/* End of file attendance.php */
/* Location: ./application/controllers/attendance.php */

 

by the way, the FRAMEWORK that we're using is CodeIgniter... Again, you help will be so much appreciated...