Export Search Results to CSV

[transfield]

Hi,
From my code below, I want to export & download the search results into a csv file. The output should look like this:
Column A of the CSV FIle
cascadium
the results of $row1[‘size’]
the results of $num1
the results of $median1

Could you show me the code to achieve this please? Thanks a lot.

 <?php
    include("/path/to/my/website/public_html/connect_to_the_database.php");
    
    $query6=mysql_query("SELECT size, COUNT(size) as fcount FROM condo WHERE (location LIKE '%cascadium%') AND (sale_rent LIKE '%rent%')  AND (size > 0) GROUP BY size ORDER BY fcount DESC LIMIT 1");
    $row1=@mysql_fetch_array($query6);
    $query1=mysql_query("SELECT price FROM condo WHERE (location LIKE '%cascadium%') AND (sale_rent LIKE '%rent%') AND (size = {$row1['size']}) AND (price > 0) ORDER BY price DESC");
    
    //median calculation
    $thearray=array(); 
    			while ( $row=@mysql_fetch_array($query1,MYSQL_NUM) ) { 
    				$thearray[]=$row[0];
    			}
    			$num0=count($thearray); 
    			if ($num0 % 2) { 
    				$median1 = $thearray[floor($num0+1)/2 - 1]; 
    			} else { 
    				$median1 = ($thearray[$num0/2] + $thearray[$num0/2 - 1]) / 2; 
    			} 
    			
    $num1=@mysql_num_rows($query1);
    echo "<p><strong>Keyword: </strong>".cascadium."<br/>";
    echo "<strong>Most Common Land / Built-up Area: </strong>".$row1['size']."sq ft <br/>";
    echo "<strong>Total Samples Analysed: </strong>".$num1."<br/>";
    echo "<strong>Median Asking Price: </strong> RM".$median1."<br/></p>";
    
    ?>

[Gandalf]

Writing a csv file is straightforward; open the file, write the record(s) and close the file:

$fh = fopen($dir.'mycsv.csv', 'w');
fwrite($fh, $record);
fclose($fh);

If there are multiple records then you can do the fwrite in an if or while loop…

[transfield]

Thanks for your reply, gandalf458.

Based on your example, what is $record?

How do I add cascadium, the results of $row1[‘size’], the results of $num1 and the results of $median1 into your example?

Thanks

[Gandalf]

$record is your 4 fields (values) concatenated together with comma separators. In case the values may themselves include commas it would be safest to enclose each value between double-quotes:

$record = 'cascadium,"' . $row1['size'] . '","' . $num1 . '","' . $median1 . '"';

I think that’s right. It just doesn’t look very elegant.

[transfield]

Thanks once again, gandalf458.

I’ve decided to use the following code instead as I want the results in Column A.

$dir = "/path/to/my/website/public_html/";

$list = array (array('cascadium'), array($row1['size']), array($num1), array($median1));
$fp = fopen($dir.'mycsv.csv', 'w');
foreach ($list as $fields) {
fputcsv($fp, $fields);
}
fclose($fp);

My next question is, how do I automatically download this csv file without having to click any additional hyperlink or button? Explained slightly differently, I want the data to be written to the csv file & I want the csv file to download immediately.

You’ve been great help.

[djsmithme]

I use the following to save multi dimensional arrays to CSV which may be of help.

This class just echo’s out the values to the screen so you will need to update the header or add the code above to write it to a file on yourserver:

header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=export.csv');

class CSV {

    //Create the CSV from $data array
    public function createCSVFromArray($data) {
        $headings = [];
        $tabledata = '';
        //get the headings of the columns
        if ($data) {
            foreach ($data[0] as $k => $r) {
                array_push($headings, $k);
            }
            //Headers
            $tableheadings = $this->outputAsCSV($headings);
            //Data
            foreach ($data as $result) {
                $tabledata .= $this->outputAsCSV($result);
            }
            echo $tableheadings . $tabledata;
        } else {
            return false;
        }
    }

    private function outputAsCSV($fields) {
        $output = $separator = '';
        foreach ($fields as $field) {
            $output .= $separator . $field;
            $separator = ','; 
        }
        $output .= "\r\n"; 
        return $output;
    }
}

[transfield]

Thank you for your help, djsmithme.

The code looks good but my coding skills are poor. Therefore, I don’t know how to implement your good code.

If you could show me how to implement your code into mine, I would be grateful.

[djsmithme]

I use it for exporting SQL results but here is an example:

<?php


class CSV {

    //Create the CSV from $data array
    public function createCSVFromArray($data) {
        $headings = [];
        $tabledata = '';
        //get the headings of the columns
        if ($data) {
            foreach ($data[0] as $k => $r) {
                array_push($headings, $k);
            }
            //Headers
            $tableheadings = $this->outputAsCSV($headings);
            //Data
            foreach ($data as $result) {
                $tabledata .= $this->outputAsCSV($result);
            }
            echo $tableheadings . $tabledata;
        } else {
            return false;
        }
    }

    private function outputAsCSV($fields) {
        $output = $separator = '';
        foreach ($fields as $field) {
            $output .= $separator . $field;
            $separator = ','; 
        }
        $output .= "\r\n"; 
        return $output;
    }
}



header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=export.csv');

$data = array(
  array(
      'col1' => 'foo',
      'col2' => 'bar',
      'col3' => 'baz',      
  ),
  array(
      'col1' => 'foo2',
      'col2' => 'bar2',
      'col3' => 'baz2',      
  ),
  array(
      'col1' => 'foo3',
      'col2' => 'bar3',
      'col3' => 'baz3',      
  )
);

$csv = new CSV();
$csv->createCSVFromArray($data);

[transfield]

It says Parse error: syntax error, unexpected ‘[’ on the line that has this code:

$headings = [];

[dsarchyuk]

Replace that line with

$headings = array();

‘’ not supported in earlier versions of PHP.