• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1659
  • Last Modified:

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

0
pdheady
Asked:
pdheady
  • 5
  • 4
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now