Mysqli到PHP中的CSV导出 - 为大数据返回500内部服务器错误

Question

I have been trying to export a large amount of data (after performing matched query).

When I limit the number to small like 50, it works very well. But once I removed the limit from my query it runs runs and shows internal server error (500). I made sure my server's max execution time is 360000 and the memory limit is 1600 M. The expected result count is around 200K.

Here is the code I am using with LIMIT

$dbhost = 'myhost';
$dbuser = 'user';
$dbpass = 'pass';
$dbname = 'mydb';

$conn = new mysqli("$dbhost", "$dbuser", "$dbpass", "$dbname");

if ($conn->connect_errno) {
    echo "Failed to connect to MySQL: (" . $conn->connect_errno . ") " . $conn->connect_error;
}   

$sql = "SELECT *FROM `master_Prospects` WHERE  `City` IN (SELECT DISTINCT `City` FROM `data_Footprint`) AND `State` IN (SELECT DISTINCT `State` FROM `data_Footprint`) AND `Street` IN (SELECT DISTINCT `Street` FROM `data_Footprint`) LIMIT 0, 50" ;
$query = mysqli_query($conn,$sql);

$columns_total = mysqli_num_fields($query); 
$fields_data = mysqli_fetch_fields($query);

$heading_array = array();           
for ($i = 0; $i < $columns_total; $i++) {
    $heading = $fields_data[$i]->name;
    $heading_array[$i] = $heading;
}

// output headers so that the file is downloaded rather than displayed
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');

$output = fopen('php://output', 'w');
fputcsv($output,$heading_array);

// loop over the rows, outputting them
while ($row = mysqli_fetch_assoc($query)) fputcsv($output, $row);