Solved

Export Mysql Table to CSV Using PHP - What's Wrong?

Posted on 2014-09-25
4
379 Views
Last Modified: 2014-09-25
I liked this example because it was intuitive and matched my needs: http://www.a2zwebhelp.com/export-data-to-csv

Problem is, it's using mysql as opposed to mysqli so here's what I've got:

set_time_limit(600);
include ("carter.inc");
$cxn = mysqli_connect($host,$user,$password,$database)
or die ("couldn't connect to server");

$output="";

$michelle="select * from verizon_test order by id";
$michelle_query=mysqli_query($cxn, $michelle)
or die("Michelle didn't work");
$michelle_columns=mysqli_field_count($cxn);

//gets the field names from your table and sets them up as the first row in your csv file

for($i=0; $i<$michelle_columns; $i++)
{
$heading=mysqli_fetch_fields($michelle_query);
$output	.='"'.$heading.'",';
}
$output.="\n";

/*
while($michelle_row=mysqli_fetch_array($michelle_query))
{
	for($i=0; $i<$michelle_columns; $i++)
	{
		$output .='"'.$michelle_row["$i"].'",';
	}
$output.="\n";
}

$filename="json.csv";
header('Content-type:appliation/csv');
header('Content-Dispoition: attachment; filename='.$filename);

echo $output;
exit;*/

Open in new window


There's more than one problem here, but the first thing I want to tackle is my column names.

The example I liked is using a deprecated function, but I can't figure out how to get mysqli_fetch_fields working. I know it's supposed to be an array, but every time I try to write it as $heading=mysqli_fetch_fields($michelle_query, $i), I get "Warning: mysqli_fetch_fields() expects exactly 1 parameter, 2 given in C:\wamp\www\json\csv_test.php on line 19"

Where am I blowing it?
0
Comment
Question by:brucegust
  • 2
4 Comments
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 40344166
I cannot test this, but it should be about right in principle.
set_time_limit(600);
include ("carter.inc");
$cxn = mysqli_connect($host,$user,$password,$database)
or die ("couldn't connect to server");

$output="";

$michelle="select * from verizon_test order by id";
$michelle_query=mysqli_query($cxn, $michelle)
or die("Michelle didn't work");
$michelle_columns=mysqli_field_count($cxn);

$filename="json.csv";

// OPEN THE CSV FILE HERE
$file = fopen($filename, 'w');

// GET THE FIELD NAMES
$heading=mysqli_fetch_fields($michelle_query);
fputcsv($file, $heading);

// GET THE DATA SET
while($michelle_row=mysqli_fetch_assoc($michelle_query))
{
    fputcsv($file, $michelle_row);
}

// REWIND THE CSV FILE HERE
rewind($file);

// SEND HEADERS AND DATA
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);

fpassthru($file);

Open in new window

0
 

Author Comment

by:brucegust
ID: 40344215
Ray!

I'm getting an error at line 20: "fputcsv() expects parameter 1 to be resource, string given in C:\wamp\www\json\csv_test.php on line 20"

What am I missing?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40344237
Don't know - I can't test the code I posted.  Suggest you use var_dump() to print out the variables that are used on line 20.
0
 
LVL 34

Accepted Solution

by:
Slick812 earned 300 total points
ID: 40344586
greetings, in your code starting on line 11, you have it all mixed up, incorrect as this errors -
$michelle_columns=mysqli_field_count($cxn);
//gets the field names from your table and sets them up as the first row in your csv file
for($i=0; $i<$michelle_columns; $i++)
{
$heading=mysqli_fetch_fields($michelle_query);
$output	.='"'.$heading.'",';
}

Open in new window


using the for loop in MySQLI does NOT get you the column names, the code below may be more correct if you can correctly substitute it for the bad code you use

Bettre Code bellow.
$heading = mysqli_fetch_fields($michelle_query);

foreach ($heading as $val) {
  $output	.='"'.$val->name.'",';
    }
$output.="\n";

Open in new window


just so you may know, this line returns an ARRAY -
$heading = mysqli_fetch_fields($mich);
The $heading array has ONE element added for each column in the select,
so if select returns 4 columns then this array has 4 parts, elements. . . .
each element is a php object, that has several properties, like name, table and type, you can access these like -
$name = $heading[0]->name;
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question