Question 21000 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I replace column name with the comments given in table's field definition – Mysql

I want to change the column name with a descriptive name, like in my table I have a field name "job_title",
I want to replace this heading with "what is your job title", and job_description with "Describe your job description"
For accomplishing this task I can use Aliasing but I want to change the column names dynamically instead of hard code.
I have described these questions in the comments section of the individual field of the table and I am trying to fetch these comments from the database and display comments of the field as the column heading but couldn’t accomplish it.
This is my PHP code:

    $sql_getcolumns="select * interview_col_comments where table_name ='interview'";
    $result = $mysqli->query($sql_getcolumns);
    echo "<table>";
     echo "<tr>";
        while($row = mysqli_fetch_array($result)) 
      {
          echo "<th>".$row[0]."</th>";
      }

echo "";
I also tried to find a way in the PhpMyAdmin interface if I labeled column names and retrieve labels using a query in PHP
but didn’t find this option in the SQL interface.

Is there another Approach using PHP, or SQL Which I can use to give columns of the table descriptive names?

Tags:

2

Answers


  1. 0
    • One approach can be make an array in php like below.
    <?php  $comment=[
'job_title' => "what is your job title",'job_description'=>"Describe your job description"];
 echo "<th>".$comment[$row[0]]."</th>";
?>'
    • Another approach can be make a another table with column description in mysql and replace on run time.
      id|column_id|description
   1|job_title|"what is your job title"      
   2|job_description|"Describe your job description"
    Login or Signup to reply.
  2. 0

    What you are looking for is called "localization".

    There are many ways to do that.

    I usually ALSO prefer to keep this information tightly wired to the database I’m working with, so what I did is:

    I used the Database Column’s Comment field to provide meta-information.
    For example, a columns comment can look like this:

    #required #de=Vertragsnummer #en=Contract_Number #search

    Now, using the following query, I can retrieve the comments, and build a ColumnMetadataObject out of the information using some regex / string operations.

    SELECT 
    c.`TABLE_NAME`,
    c.`COLUMN_NAME`, 
    c.`COLUMN_COMMENT`, 
    t.`TABLE_COMMENT` 
FROM 
    information_schema.columns c left join 
    information_schema.TABLES t ON 
    c.TABLE_NAME = t.TABLE_NAME and 
    c.TABLE_SCHEMA = t.TABLE_SCHEMA  
where 
    c.`table_schema` = 'MyDatabase'

    After parsing the information and providing the required Meta-Data-Objects, My header output just looks like this:

    <?=$db->getColumnMetdata('contractNumber')->getHeader($_SESSION["user_language"]));?>

    Code in between can vary in complexity. My ColumnMetadata also contains other information like required, searchable, length, possible foreign keys, and much more. That part would be up to you – just for localize headers, an associative Array would work as well. something like :

    ["de"] => {
   "table1.contractNumber" => "Vertragsnummer"
   "table1.Id" => "Id"
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search