Exporting MySQL Table Column to CSV and perform formatting

[system]

I’m trying to export a MySQL Table to a CSV, but also perform formatting on it as well.

There’s a column called “meta_value” in my table, that is the only column I want. I also want to format the data in that column into a csv.

The data in the “meta_value” column looks like this:

		["3C9d9SlArYcUsAWszgL68xEEveSzCDNzfJ2hYqPg","Retailer","$1M - $5M","22","10-20","Amazon Canada","InfiPlex","I am a past client","Data Extraction","Browser Search","i like comments","[email protected]"]

Upon export, I would like to remove the first randomly generated string, and then for the others, I would like to separate them out into their own columns within the .csv itself.

My PHP is below that runs through connecting to the MySQL DB, table, and exporting the CSV. I’m just not sure how to do the fancy formatting that I outlined above.

Thank you.

[php] <?php

/* vars for export */
// database record to be exported
$db_record = 'XXXXXXXXX';

// optional where query
$where = 'WHERE 1 ORDER BY 1';

// filename for export
$csv_filename = 'db_export_'.$db_record.'_'.date('Y-m-d').'.csv';

// database variables
$hostname = "localhost";
$user = "XXXXXXXXX";
$password = "XXXXXXXXX";
$database = "XXXXXXXXX";

// Database connecten voor alle services
mysql_connect($hostname, $user, $password)
or die('Could not connect: ' . mysql_error());				
mysql_select_db($database)
or die ('Could not select database ' . mysql_error());

// create empty variable to be filled with export data
$csv_export = '';

// query to get data from database
$query = mysql_query("SELECT * FROM ".$db_record." ".$where);
$field = mysql_num_fields($query);

// create line with field names
for($i = 0; $i < $field; $i++) {
  $csv_export.= mysql_field_name($query,$i).';';
}

// newline (seems to work both on Linux & Windows servers)
$csv_export.= '
';

// loop through database query and fill export variable
while($row = mysql_fetch_array($query)) {
  // create line with field values
  for($i = 0; $i < $field; $i++) {
    $csv_export.= '"'.$row[mysql_field_name($query,$i)].'";';
  }	
  $csv_export.= '
';	
}

// Export the data and prompt a csv file for download
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=".$csv_filename."");
echo($csv_export);

?>[/php]

[ErnieAlex]

Well, I will give you a start on this. You must remember that you are pulling ALL of the data and only needing
a small part of it. And, you need to just use a simple command to remove the part you do not want listed.

So, in your current query, you do this: SELECT * FROM As you see, it selects ALL of the data and you only
need part of it. Therefore, just change that to pull out the one column field you need. Loosely something like
this should work: SELECT meta_value FROM As you see, the results of the query is just the data you need.

Next, to remove the unwanted data, there are many ways to handle this. I would do it in two passes. The first
will remove the starting and ending brackets. And, then, remove everything up to and including the first comma.
You can do this (untested) like this:
$meta_value = substr($row[‘meta_value’], 1, -1); // Remove []‘s
$temp = explode(’,’, $meta_value); // Separate line by comma’s
unset($temp[0]); // Remove the first one
$meta_value = implode(",", $temp); // Recombine back into line with comma’s

As you see this takes a few steps, but, are easy enough. You could use a string search for the first comma and
then then use the substring. This is perhaps a better way, either will work. Here is this version:
$meta_value = substr($row[‘meta_value’], 1, -1); // Remove []'s
$meta_value = substr($meta_value, strpos($meta_value, “,”)+1); // Take all characters starting at 1st “,”
Not sure on the +1, but, I do think that the strpos() function starts at zero, so you have to add one to go past
the comma. The substr() function will take the rest of the string if you do not use the third argument.

Well, that should get you started… Let us know. Good luck