Loop through MYSQL result and post to CSV with FPUTCSV function

Cant get this working right. SQL query works fine, but when posting data to CSV it only outputs first row of data. I want column names in first row, then all rows/data below that. I'm pretty sure the array/loop is setup wrong. Should be easy fix for PHP expert and quick 500 pts x 4.

Just to reiterate, everything works in code except data not looping into csv.


<?php
 
$date = date("Y-m-d");
 
$sql = "my complicated query here";
 
$query = mysql_query($sql) or die("Failed Query of " . $sql);  //do the query
 
while ( $row = mysql_fetch_assoc($query) ) {
 
$list = array
(
"$contact_id,$first_names,$last_name,$home_phone_pretty,$cell_phone_pretty,$work_phone_pretty,$email,$tax_id,$date_of_birth",
);
 
$file = fopen("/tmp/file-export-$date.csv","w");
 
foreach ($list as $line)
  {
  fputcsv($file,split(',',$line));
  }
 
fclose($file);
 
include_once('../lib/pcl/pclzip.lib.php');
  $archive = new PclZip("/tmp/file-export-$date.zip");
  $zip_file = "/tmp/file-export-$date.zip";
  $v_list = $archive->create("/tmp/file-export-$date.csv",
                             PCLZIP_OPT_REMOVE_PATH, 'data');
  if ($v_list == 0) {
    die("Error : ".$archive->errorInfo(true));
 
}
 
send_email();
 
?>

Open in new window

LVL 1
pdheadyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ivo StoykovCommented:
Hi
I suppose the row
  "$contact_id,$first_names,$last_name,$home_phone_pretty,$cell_phone_pretty,$work_phone_pretty,$email,$tax_id,$date_of_birth", 
contains fields from myslq
if this is correct it should be
HTH
I



  $row["contact_id"],$row["first_names"],$row["last_name"],$row["home_phone_pretty"],$row["cell_phone_pretty"],$row["work_phone_pretty"],$row["email"],$row["tax_id"],$row["date_of_birth"]

Open in new window

0
pdheadyAuthor Commented:
Sorry, I already have that in code. Here is complete code.

<?php
 
$date = date("Y-m-d");
 
$sql = "my complicated query here";
 
$query = mysql_query($sql) or die("Failed Query of " . $sql);  //do the query
 
while ( $row = mysql_fetch_assoc($query) ) {
 
$contact_id = $row["contact_id"];
$first_names = $row["first_names"];
$last_name = $row["last_name"];
$home_phone = $row["home_phone"];
$cell_phone = $row["cell_phone"];
$work_phone = $row["work_phone"];
$email = $row["email"];
$tax_id = $row["tax_id"];
$date_of_birth = $row["date_of_birth"];
 
$list = array
(
"$contact_id,$first_names,$last_name,$home_phone_pretty,$cell_phone_pretty,$work_phone_pretty,$email,$tax_id,$date_of_birth",
);
 
$file = fopen("/tmp/file-export-$date.csv","w");
 
foreach ($list as $line)
  {
  fputcsv($file,split(',',$line));
  }
 
fclose($file);
 
include_once('../lib/pcl/pclzip.lib.php');
  $archive = new PclZip("/tmp/file-export-$date.zip");
  $zip_file = "/tmp/file-export-$date.zip";
  $v_list = $archive->create("/tmp/file-export-$date.csv",
                             PCLZIP_OPT_REMOVE_PATH, 'data');
  if ($v_list == 0) {
    die("Error : ".$archive->errorInfo(true));
 
}
 
send_email();
 
?>

Open in new window

0
pdheadyAuthor Commented:
I even tried something simple like this and the output into csv file was:

nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn

:(
$fp = fopen('/tmp/file-export-$date.csv','w');
 
$query = mysql_query($sql) or die("Failed Query of " . $sql);  //do the query
 
while ( $row = mysql_fetch_assoc($query) ) {
 
$contact_id = $row["contact_id"];
$first_names = $row["first_names"];
$last_name = $row["last_name"];
$home_phone = $row["home_phone"];
$cell_phone = $row["cell_phone"];
$work_phone = $row["work_phone"];
$email = $row["email"];
$tax_id = $row["tax_id"];
$date_of_birth = $row["date_of_birth"];
 
$home_phone = format_phone($home_phone);
$cell_phone = format_phone($cell_phone);
$work_phone = format_phone($work_phone);
	
$nextline = "$contact_id" . ',' . "$first_names" . ',' . "$last_name" . ',' . "$home_phone" . ',' . "$work_phone" . ',' . "$cell_phone" . ',' . "$email" . ',' . "$tax_id" . ',' . "$date_of_birth";
fwrite($fp,$nextline);
}
fclose($fp);

Open in new window

0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Ivo StoykovCommented:
Hi
try to move
$file = fopen("/tmp/file-export-$date.csv","w");
above
while ( $row = mysql_fetch_assoc($query) ) {


<?php
 
$date = date("Y-m-d");
 
$sql = "my complicated query here";
 
$query = mysql_query($sql) or die("Failed Query of " . $sql);  //do the query
$file = fopen("/tmp/file-export-$date.csv","w");
 
while ( $row = mysql_fetch_assoc($query) ) {
 
$contact_id = $row["contact_id"];
$first_names = $row["first_names"];
$last_name = $row["last_name"];
$home_phone = $row["home_phone"];
$cell_phone = $row["cell_phone"];
$work_phone = $row["work_phone"];
$email = $row["email"];
$tax_id = $row["tax_id"];
$date_of_birth = $row["date_of_birth"];
 
$list = array
(
"$contact_id,$first_names,$last_name,$home_phone_pretty,$cell_phone_pretty,$work_phone_pretty,$email,$tax_id,$date_of_birth",
);
 
 
foreach ($list as $line)
  {
  fputcsv($file,split(',',$line));
  }
 
fclose($file);
 
include_once('../lib/pcl/pclzip.lib.php');
  $archive = new PclZip("/tmp/file-export-$date.zip");
  $zip_file = "/tmp/file-export-$date.zip";
  $v_list = $archive->create("/tmp/file-export-$date.csv",
                             PCLZIP_OPT_REMOVE_PATH, 'data');
  if ($v_list == 0) {
    die("Error : ".$archive->errorInfo(true));
 
}
 
send_email();
 
?>

Open in new window

0
pdheadyAuthor Commented:
See above example. Still doesn't work. Not even getting data now except the stupid n's.
0
Ivo StoykovCommented:
Well, what about charsets and encoding?
try just to dump $row

while ( $row = mysql_fetch_assoc($query) ) {
  print_r ($row);
// or  var_dump($row);
 
}

Open in new window

0
pdheadyAuthor Commented:
Ok did that and it output all of the data but the array looks abnormal.

Array ( [0] => 71358 [contact_id] => 71358 [1] => John [first_names] => John [2] => Doe [last_name] => Doe [3] => 123-123-1234 [home_phone] => 123-123-1234 [4] => 123-123-1235 [work_phone] => 123-123-1235 [5] => [cell_phone] => [6] => [email] => [7] => 123-23-1233 [tax_id] => 123-23-1233 [8] => 1975-01-01 [date_of_birth] => 1975-01-01 )

next record same as above with different data

next record...

etc...
0
Ivo StoykovCommented:
OK try to reorganize your code like this

<?php
 
$date = date("Y-m-d");
 
$sql = "my complicated query here";
 
$query = mysql_query($sql) or die("Failed Query of " . $sql);  //do the query
$file = fopen("/tmp/file-export-$date.csv","w");
 
$file = fopen("/tmp/file-export-$date.csv","w");
 
while ( $row = mysql_fetch_assoc($query) ) {
 
$line = $row["contact_id"] . "," . $row["first_names"] . "," . $row["last_name"] . "," . $row["home_phone"] . "," . $row["cell_phone"] . "," . $row["work_phone"] . "," . $row["email"] . "," . $row["tax_id"] . "," . $row["date_of_birth"];
fputcsv($file,$line);
}
fclose($file);
 
 
include_once('../lib/pcl/pclzip.lib.php');
  $archive = new PclZip("/tmp/file-export-$date.zip");
  $zip_file = "/tmp/file-export-$date.zip";
  $v_list = $archive->create("/tmp/file-export-$date.csv",
                             PCLZIP_OPT_REMOVE_PATH, 'data');
  if ($v_list == 0) {
    die("Error : ".$archive->errorInfo(true));
 
send_email();
  
?>

Open in new window

0
pdheadyAuthor Commented:
Didn't work. I found some code that worked like a charm tho.


    
    define("FILENAME", "/tmp/file-export-$date.csv");
    $fhandle = fopen(FILENAME, "w");
    $exportquery = "sql query here";
    $header = "";
    $rows = "";
    $rowcounter = 0;
    $exportresult = mysql_query($exportquery);
    while($row = mysql_fetch_array($exportresult, MYSQL_ASSOC)) {
         $rows = "";
         // Construct the Header row
         if(!$rowcounter) {
              $rowcounter++;
                   foreach ($row as $key => $value) {
                   $header .= ereg_replace(",", "", $key) . ",";
                   }
              $header = rtrim($header, ",");
              $header .= "\n";
              fwrite($fhandle, $header);
         }
 
         // Construct a normal row
         foreach ($row as $key => $value) {
              $rows .= ereg_replace(",", "", $value) . ",";
         }
         $rows = rtrim($rows, ",");
         $rows .= "\n";
         fwrite($fhandle, $rows);
    }
    fclose($fhandle); 

Open in new window

0
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.