Jump to content

Split array data and export to multiple CSV

jthan03

By jthan03
in PHP Coding Help

 Share

Recommended Posts

jthan03 Newbie

jthan03

Posted
Posted

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.

Link to comment
Share on other sites
PFMaBiSmAd Advanced Member

PFMaBiSmAd

Posted
Posted

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?

Link to comment
Share on other sites
jthan03 Newbie

jthan03

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
PFMaBiSmAd Advanced Member

PFMaBiSmAd

Posted
Posted

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?

Link to comment
Share on other sites
jthan03 Newbie

jthan03

Posted
  • Author
Posted

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...

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.