?
Solved

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

Posted on 2014-09-25
4
Medium Priority
?
403 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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 111

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 1200 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

Independent Software Vendors: 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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

764 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