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

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?

CodePudding user response：

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"

CodePudding user response：

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"
}