Exporting MYSQL table to a CSV file using PHP

[fohanlon]

Hi

Heres what I have:

$client = $_POST['client'];
if (count($_POST['choice']) >= 1)
{
  for ($i = 0; $i < count($_POST['choice']); $i++)
  {
// this bit is only to set different column headings for a particular client
              if($client == "XXXX" && $_POST['choice'][$i] == "Product")
$str .= "Brand, ";
  else if($client == "XXXX" && $_POST['choice'][$i] == "Description")
$str .= "Item Name, ";
  else if($client == "XXXX" && $_POST['choice'][$i] == "Location")
$str .= "Type, ";
  else
$str .= $_POST['choice'][$i] . ", ";
   
   $str2 .= $_POST['choice'][$i] . ", ";
   }
}

$pos = strrpos($str2, ", ");
$str2 = substr_replace($str2, "", $pos, -1);

$timestamp = strftime("%d%m%Y%H%M%S");
mt_srand((double)microtime()*1000000);
$ref_num = $timestamp ."-". mt_rand(1, 999);
$fp = fopen ("../csv/" . $client . "/" . $client . " - " . $ref_num . ".csv", 'w+') or die("Cannot open file");
$filename = "../csv/" . $client . "/" . $client . " - " . $ref_num . ".csv";

$result = mysql_query("SELECT $str2 FROM $client ORDER BY Code");
if(!$result) { die("Error reading client stocklist: " . mysql_error());  }
$num_rows = mysql_num_rows($result);

$columns = $str . "\n";
fwrite ($fp, $columns);
$cols = explode(", ", $str2);
$row = "";

while ($r = mysql_fetch_array($result))
{
for($i = 0; $i < sizeof($cols); $i++)
$row .= $r[$cols[$i]] . ",";

// create rows 
$pos = strrpos($row, ",");
$row = substr_replace($row, " ", $pos, 0);
$row .= "\n";
fwrite ($fp, $row);
$row = "";
}

Close file etc.

Heres the scenario:
On a previous page a user selects checkboxes whose values are equivalent to the field names in a $client table.

The csv file is being created with one problem:

No matter how many checkboxes are clicked the last value (last checkbox) is not added to the csv file even though the count for cols is correct.  Seems to be losing something in the for loop.

Any help would be appreciated.

Many thanks,

Fergal.

[Psycho]

Have you tried echoing the query to the page to verify if the query is correct?

I think your for loop the manner in which you create $str2 (not very descriptive - I'd use $selectStr myself) *could* be the problem. Again, I'd suggest echoing the values out tot he apge to ensure they are getting processed correctly. Here's a breif rewrite of how I would approach:

[code]foreach ($_POST['choice'] as $choice)
  {
    // this bit is only to set different column headings for a particular client
    if($client == "XXXX" && $choice == "Product") {
      $str[] = "Brand";
    } else if($client == "XXXX" && $choice == "Description") {
      $str[] = "Item Name"; 
    } else if($client == "XXXX" && $choice == "Location") {
      $str[] = "Type";
    } else {
      $str[] = $choice;
    }
 
    $str2[] = $choice;
  }

$str  = implode(", ", $str);
$str2 = implode(", ", $str2);

$timestamp = strftime("%d%m%Y%H%M%S");
mt_srand((double)microtime()*1000000);
$ref_num = $timestamp ."-". mt_rand(1, 999);
$fp = fopen ("../csv/" . $client . "/" . $client . " - " . $ref_num . ".csv", 'w+') or die("Cannot open file");
$filename = "../csv/" . $client . "/" . $client . " - " . $ref_num . ".csv";

$result = mysql_query("SELECT $str2 FROM $client ORDER BY Code");
if(!$result) { die("Error reading client stocklist: " . mysql_error());  }

fwrite ($fp, $str."\n");

while ($r = mysql_fetch_array($result))
  {
    foreach ($r as $value) {
    fwrite ($fp, implode(", ", $r));
  }
} [/code]