Solved

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

Posted on 2014-09-25
4
372 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 108

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 108

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 33

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 31
Should I use subdomains or addon domains? 3 39
Change text to radio button and calendar form 2 36
Bootstrap collapse causes odd behavior with php loop 7 26
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to dynamically set the form action using jQuery.
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.

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now