Solved

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

Posted on 2014-09-25
4
391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 110

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 110

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

729 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