Change mysql column names for use with FPutCSV()

Question

$result = mysql_query("show columns from mash");
for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $colArray[$i] = mysql_fetch_assoc($result);
    $fieldArray[$i] = $colArray[$i]['Field'];
}
fputcsv($fp,$fieldArray);

to grab mysql column names and then output them at the top of the created CSV.

However, i know what the column names are going to be, how can i change them for the output in the CSV?

Ie. if the show columns output: id, field1, field2, field3, how can i make these ID, Field 1, Field 2, Field 3. Not just capitalise, but choose what they are going to be...

Answer 1

You'd have to either have a list of columns-to-labels, which would make the script specific to the database:

$labels = array(
  'col_name'=>'Column 1',
  'id'=>'User ID'
); // and so on

$result = mysql_query("show columns from mash");
for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $colArray[$i] = mysql_fetch_assoc($result);
    $fieldArray[$i] = $labels[$colArray[$i]['Field']];
}
fputcsv($fp,$fieldArray);

OR, you'd have to make a database with this sort of meta data. It could have three fields: source_table, source_column, column_label. That adds another query to the mix, but would allow the code to be made generic.

The last route would be to use some simple naming convention with a separator, like underscore (_), then you remove the underscores and apply title case. field_1 becomes "Field 1", "user_id" becomes "User Id", and so on.

$result = mysql_query("show columns from mash");
for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $colArray[$i] = mysql_fetch_assoc($result);
    $fieldArray[$i] = _title_case_function_(str_replace('_', ' ', $colArray[$i]['Field']]));
}
fputcsv($fp,$fieldArray);