We help IT Professionals succeed at work.

Insert new column into CSV export using PHP and MySQL using MySQL value

pda4me
pda4me asked
on
I am creating a CSV export using PHP and a MySQL query.  I need to insert a column at the end of the column called PHOTO_URL similar to the Country column I am already inserting, the Country column you can see is hardcoded with USA value for all records.  The only difference is that I need to reference the column MLSID and then append a .jpg at the end like the MLSID.jpg and it be dynamic based for each record.


<?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 MLSID, 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 .= "Country". "; ";
    $csv_output .= "\n";
  }
  $row++;

  foreach($rowr as $name => $value)
  {
    $csv_output .= $value . "; ";
  }
  $csv_output .= "USA". "; ";
  $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 MLSID, 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 .= "Country; PHOTO_URL;\n";
  }
  $row++;

  foreach($rowr as $name => $value)
  {
  if($name=='MLSID') {$jpg_name=$value;}
  $csv_output .= $value."; ";
  }
  $csv_output .= "USA; ".$jpg_name.".jpg;\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;
?>

Author

Commented:
hmmmm...how would I specify $jpg_name.jpg and $jpg_name_01.jpg up to 4?

Author

Commented:
Thanks!

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