rename a mysql table header

Question

UPDATE (code as asked for):

$result = mysql_query('SELECT * FROM `some_table`'); 
if (!$result) die('Couldn\'t fetch records'); 
$num_fields = mysql_num_fields($result); 
$headers = array(); 
for ($i = 0; $i < $num_fields; $i++) 
{     
       $headers[] = mysql_field_name($result , $i); 
} 
$fp = fopen('php://output', 'w'); 
if ($fp && $result) 
{     
   header('Content-Type: text/csv');
   header('Content-Disposition: attachment; filename="export.csv"');
   header('Pragma: no-cache');    
   header('Expires: 0');
   fputcsv($fp, $headers); 
   while ($row = mysql_fetch_row($result)) 
   {
      fputcsv($fp, array_values($row)); 
   }
die;

}

$result = mysql_query('SELECT who, phonenumber, notes, location FROM `phpbb_phonelist` WHERE `activenumber` = 1');  
if (!$result) die('Couldn\'t fetch records');

I'm trying to change the header for who, phonenumber, notes, and location. I've tried using an as statement (see below), but it creates line breaks in the header:

$result = mysql_query('SELECT who as "Test1", phonenumber as "Test 2", notes as "Test 3", location as "Test 4" FROM `phpbb_phonelist` WHERE `activenumber` = 1');  
if (!$result) die('Couldn\'t fetch records');

Output of the above:

who,"Phone  Number",notes,location

Where's your fetching code? And what's the output are you trying to get? — Mureinik, Jan 18 '15 at 01:26
I don't understand how that can output what you say it does... I am so confused....... — gloomy.penguin, Jan 18 '15 at 01:35

score 0 · Accepted Answer · edited May 23 '17 at 10:25

Your code works as-is for me.... basically... I just changed the select. The rest is the same.

$result = mysql_query('SELECT table_id as "Table ID", table_value as "Table Value", table_char as "Table Char"  FROM `tablename`');

if (!$result) die('Couldn\'t fetch records');

$num_fields = mysql_num_fields($result);
$headers = array();

for ($i = 0; $i <$num_fields; $i++) {
   $headers[] = mysql_field_name($result , $i);
}

$fp = fopen('php://output', 'w');

if ($fp && $result) {
       header('Content-Type: text/csv');
       header('Content-Disposition: attachment; filename="export.csv"');
       header('Pragma: no-cache');
       header('Expires: 0');
       fputcsv($fp, $headers);
       while ($row = mysql_fetch_row($result))
       {
          fputcsv($fp, array_values($row));
       }
    die;
}

Gave me this output in a csv:

Table ID    Table Value      Table Char
1           one              o
2           two              t

from this table:

mysql> select * from tablename;
+----------+-------------+-----------+------------+
| table_id | table_value | table_int | table_char |
+----------+-------------+-----------+------------+
|        1 | one         |        11 | o          |
|        2 | two         |        22 | t          |
+----------+-------------+-----------+------------+
2 rows in set (0.00 sec)

here is an example of PDO from one of my other answers... it uses parameter binding so you don't end up suffering from basic sql injection attacks.

<?php

// print_r($_POST); 

$dsn  = 'mysql:host=localhost;dbname=test';   
$user = 'root';
$pass = ''; 

$db = new PDO($dsn, $user, $pass);

$id      = isset($_POST['id'])    ? $_POST['id']    : ''; 
$value   = isset($_POST['value']) ? $_POST['value'] : '';  

if (!empty($id) && !empty($value)) {

   $query = "update my_table set value = ? where table_id = ? "; 
   $stmt  = $db->prepare($query);

   // run the query and bind the values 
   $success = $stmt->execute(array($value,$id));

   if ($success) echo "it worked!";
            // else echo "it did not work...!"; 
            else echo $stmt->errorInfo()[2];
}
else echo "post params were blank"; 

?>

and this answer shows how you can get the column names with PDO...

$rs = $db->query('select * from tablename');

for ($i = 0; $i < $rs->columnCount(); $i++) {
    $col = $rs->getColumnMeta($i);
    $columns[] = $col['name'];
}

print_r($columns);

(man... I don't know how I feel about the new StackOverflow CSS... everything is different and weird......)

spencer7593 · Answer 2 · 2015-01-18T01:57:37.103

I'm not sure I have an understanding of the problem you are reporting (I'm prefacing my "answer" with that remark, in case I'm not understanding the question you are asking.)

The default character for escaping identifiers is a backtick. You seem to have an understanding of that, given your use of backticks around some identifiers in your query.

You also seem to understand that you can assign an alias to a column in the resultset by following an expression in the SELECT list with AS aliasname.

What's not clear is why you are using double quotes around the aliases you assign. (It's possible you have SQL_MODE variable set to include ANSI_QUOTES, in which case the double quotes would work. But without that setting, I think you need to use backticks around any identifier (including column aliases) that are unqualified reserved words or that contain special characters, such as a space.

If you used backticks around the aliases, for examples:

SELECT p.who           AS `Test1`
     , p.phonenumber   AS `Test 2`
     , p.notes         AS `Test 3`
     , p.location      AS `Test 4`
  FROM `phpbb_phonelist` p
 WHERE p.activenumber = 1

Then those alias names are going to be returned in the metadata of the resultset returned by your query. Those will be the names of the columns in your resultset.

 SELECT t.table_id    AS `Table ID`
      , t.table_value AS `Table Value`
      , t.table_char  AS `Table Char`
   FROM `tablename` t
  ORDER BY t.table_id

(We typically include an ORDER BY to make the resultset more deterministic.)

I'm puzzled as to why are you using the mysql interface, which has been deprecated, rather than using mysqli or PDO.

score 0 · Answer 3 · edited May 23 '17 at 10:29

No, I do not think so.

First off title "rename a mysql table header" makes zero sense.

Content disposition is not a valid HTTP 1.1 standard.

See Uses of content-disposition in an HTTP response header

Attachment? Is this an email? Does not look like one.

Expires:0;

From the W3C RFC 2616 Sec. 14.21: The format is an absolute date and time as defined by HTTP-date in section 3.3.1; it MUST be in RFC 1123 date format:

What's with the "die"?

rename a mysql table header

3 Answers3