在导出csv之前设置数据库列别名

Question

Hey guys I am trying to export a csv in my script and it works perfectly. Now what I need to do, is rename the columns to clean names and proper names before fully exporting the csv.

Here is a picture of the database table: http://i.imgur.com/aQrOZLk.png

Here is the code:

include_once "config.php";

$table = 'patients'; // table you want to export
$file  = 'export'; // csv name.

$result = mysql_query("SHOW COLUMNS FROM " . $table . ""); 
$i      = 0;

if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
    $csv_output .= $row['Field'] . ",";
    $i++;
}
}
$csv_output .= "
";
$values = mysql_query("SELECT * FROM " . $table . "");

while ($rowr = mysql_fetch_row($values)) {
for ($j = 0; $j < $i; $j++) {
    $csv_output .= $rowr[$j] . ", ";
}
$csv_output .= "
";
}

$filename = $file . "_" . date("d-m-Y_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;
?>

As you can see it fetches the table names from this code:

$result = mysql_query("SHOW COLUMNS FROM " . $table . ""); 
$i      = 0;

if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
    $csv_output .= $row['Field'] . ",";
    $i++;
}
}

Now what I am trying to do is allow it to rename the database tables from pat_id to Patient ID pat_fname to Patient First Name pat_lname to Patient Last name and so on. To make it looks neat and readable in the csv file.

How am I able to do that? I already tried a few codes like:

$query = 'SHOW COLUMNS pat_id AS "user_id", pat_fname AS "first name", pat_lname AS "last name" FROM ' . $table;

But it didnt work and gives off errors.

Answer 1

Since you are going to deal with many tables, create a multidimensional array and predefine the field names in it.

Like this, $tables['table1'][1]=""; $tables['table1'][2]=""; $tables['table2'][1]="";

Then replace this block,

if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'] . ",";
$i++;
}
}

with a block to fetch the field names of the table from your array. This is the only way to do it.