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?
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.