We help IT Professionals succeed at work.

PHP export to CSV from MySQL , scrub data before insert into csv

pda4me
pda4me asked
on
Medium Priority
466 Views
Last Modified: 2012-06-27
I am using the following code to query a MySQL database and then export to csv.  The only problem is that the field bedrooms contains data like this "3 Bedrooms" but in the CSV I am required to provide ONLY the numerical value.  How do I strip out the space and text after the initial numerical value and PRIOR to insert into the CSV?  Keep in mind that on rare occasions the bedrooms field may have two digits, so we have to allow for that.

<?php
$host = 'localhost';
$user = 'mysqlUser';
$pass = 'myUserPass';
$db = 'myDatabase';
$table = 'products_info';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$values = mysql_query("SELECT bedrooms, COLUMN2, COLUMN3 FROM ".$table."");
$row = 0;
while ($rowr = mysql_fetch_assoc($values)) 
{
  if ($row == 0)
  {
    foreach($rowr as $name => $value)
    {
      $csv_output .= $name . "; ";
    }
    $csv_output .= "\n";
  }
  $row++;

  foreach($rowr as $name => $value)
  {
    $csv_output .= $value . "; ";
  }
  $csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Commented:
Try this -->

<?php
$host = 'localhost';
$user = 'mysqlUser';
$pass = 'myUserPass';
$db = 'myDatabase';
$table = 'products_info';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$values = mysql_query("SELECT bedrooms, COLUMN2, COLUMN3 FROM ".$table."");
$row = 0;
while ($rowr = mysql_fetch_assoc($values))
{
  if ($row == 0)
  {
    foreach($rowr as $name => $value)
    {
      $csv_output .= $name . "; ";
    }
    $csv_output .= "\n";
  }
  $row++;

  foreach($rowr as $name => $value)
  {
        if ($name=='bedrooms') {$value=$value+0;}
    $csv_output .= $value . "; ";
  }
  $csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>


M@RS

Author

Commented:
AWESOME, thanks Mars!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.