Creating result set of MySQL as CSV String

Question

How can i use mysql_fetch_array / mysql_fetch_row results to create a csv file where each row records are csv and next record is in new line.

I tried this:

$query = "SELECT * FROM myTable DESC LIMIT 10";

$result = mysql_query($query, $conn);
$resultRows = mysql_num_rows($result);

if($resultRows < 1) {
    return array('query' => $query, 'NumberOfRows' => $resultRows, 'status' => 'failure');
};

$rowResult = mysql_fetch_array($result);
//var_dump($rowResult);exit;
foreach($rowResult as $row){

    $resultSet = $row['firstname'];
    $resultSet .= ',' . $row['lastname'];
    $resultSet .= ',' . $row['email'];
    $resultSet .= ',' . $row['phone'];
    $resultSet .= ',' . ($row['address1']);
    $resultSet .= '\n';
    //var_dump($resultSet);exit;
}

So, what error did you got there?

Alfabravo
– Alfabravo

2012-07-25 22:45:19 +00:00
Commented Jul 25, 2012 at 22:45 — Alfabravo
– Alfabravo, Commented Jul 25, 2012 at 22:45
i am not getting results @ all

CodeMonkey
– CodeMonkey

2012-07-25 22:47:01 +00:00
Commented Jul 25, 2012 at 22:47 — CodeMonkey
– CodeMonkey, Commented Jul 25, 2012 at 22:47

rawb · Accepted Answer · 2012-07-25 22:47:04Z

2

I believe the newline character must be in double quotes.

$resultSet .= "\n";

Probably just omitted from the code for length reasons, but be sure you're echoing $resultSet after the foreach.

answered Jul 25, 2012 at 22:47

rawb

2,44619 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bitfox Over a year ago

Yes, the escape sequences between double quotes will be interpreted as special characters

James Williams · Accepted Answer · 2012-07-25 22:55:40Z

If you want the file to download as a CSV then you would include the following headers

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=extraction.csv");
header("Pragma: no-cache");
header("Expires: 0");

Then for the csv section you would use something like the following:

echo "FirstName,LastName,Email,Phone,Address\r\n"; //header
while($row = mysql_fetch_array($result)){
  echo "\"$row[firstname]\",\"$row[lastname]\",\"$row[email]\",\"$row[phone]\",\"$row[address]\"\r\n";
}

But if you just wanted to create a file you would use something like the following:

$fp = fopen('file.csv', 'w');

while($row = mysql_fetch_array($result)){
   fputcsv($fp, $row);
}

fclose($fp);

Johndave Decano · Accepted Answer · 2012-07-25 22:56:03Z

0

Here is a good resource: http://www.codehive.net/PHP-Array-to-CSV-1.html

Usage:

$csv_data = array_to_scv($array, false);
print_r($csv_data);

answered Jul 25, 2012 at 22:56

Johndave Decano

2,1032 gold badges16 silver badges16 bronze badges

Collectives™ on Stack Overflow

Creating result set of MySQL as CSV String

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related