I want to import records from SQL to Excel

Question

Yes, there are many titles but I chose to explain the problem under this title because I could not find a solution in other titles :)

The database contains survey questions and records of the answers to this survey. I want to print the question titles of these records in the first column, and the answers under the columns.

For example,

question table id question_id question

answer table id question_id answer

I tried many methods but failed to show the answers in each line under the question headings.

Excel,
    A       -        B       -         C .. etc.
1 - question_title   question_title    question_title .. etc.

2 - answer_title     answer_title      answer_title   .. etc.
3 - answer_title     answer_title      answer_title   .. etc.
4 - answer_title     answer_title      answer_title   .. etc.

what have i done :)

$objPHPExcel    =   new PHPExcel();
$quest          =   $db->query("SELECT * FROM question WHERE question_id =1");
$answer         =   $db->query("SELECT * FROM answer WHERE question_id =1");
$objPHPExcel->setActiveSheetIndex(0);

$column = 'A';
foreach($quest as $rows){
    $objPHPExcel->getActiveSheet()->SetCellValue($column.'1', $rows['question'],'UTF-8');
    $column++;
}

$rowCount   =   2;
$columns = 'A';
foreach($answer as $row){
    $objPHPExcel->getActiveSheet()->SetCellValue($columns.'2', $row['answer'],'UTF-8');
    $rowCount++;
    $columns++;
}

i need to list the answers below the question result :

Can you put a sample screen shoot of your current table result view. — AziMez, Nov 24 '20 at 14:20
It's still quite unclear to me what you requirements are. You give an example with a fixed query (`WHERE question_id =1`), but you talk about printing out multiple questions. Can you provide a sample of data for the `$quest` and `$answer` variables (once fetched)? — El_Vanja, Nov 24 '20 at 15:26
Maybe have a look at this previous question.... https://stackoverflow.com/questions/4249432/export-to-csv-via-php — Louie, Nov 24 '20 at 18:22

score 0 · Answer 1 · answered Nov 24 '20 at 18:32

From here Export to CSV via PHP

I personally use this function to create CSV content from any array.

function array2csv(array &$array)
{
   if (count($array) == 0) {
     return null;
   }
   ob_start();
   $df = fopen("php://output", 'w');
   fputcsv($df, array_keys(reset($array)));
   foreach ($array as $row) {
      fputcsv($df, $row);
   }
   fclose($df);
   return ob_get_clean();
}

Then you can make your user download that file using something like:

function download_send_headers($filename) {
    // disable caching
    $now = gmdate("D, d M Y H:i:s");
    header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
    header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
    header("Last-Modified: {$now} GMT");

    // force download  
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");

    // disposition / encoding on response body
    header("Content-Disposition: attachment;filename={$filename}");
    header("Content-Transfer-Encoding: binary");
}
Usa

ge example:

download_send_headers("data_export_" . date("Y-m-d") . ".csv");
echo array2csv($array);
die();

I want to import records from SQL to Excel

1 Answers1