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

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.

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.

You want to use friendly column names in your select, not your show columns.

Try something like this instead of SELECT * :

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

Notice that MySQL expects your column names to be shown in single quotes, not double quotes.

To handle this stuff as column headers in csv ... try this.

$values = mysql_query($query);
$i = mysql_num_fields($values);
for ($j = 0; $j < $i; $j++) {
   $csv_output .= mysql_field_name($j) . ", ";
}
$csv_output .= "
";

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