MySql Column As Table Header

DB field to be displayed as table header

[mvaz]

Hi all
I have a table with the following structure:
Id¦article_description¦author¦upload_date¦language
The articles are in various languages, and the appropriate language is selected when the table is populated.
What I want to do is group these articles based on language and display them in a table, each language in separate columns of the table, with the table header(<th>) populated from the database column itself.
I am aware of how to extract data from the table and display it in a table format, but I am struggling to get just the languages as the <th>, after which I know how to look for the article information based on the <th>, which I believe will be a array variable.
Please could the experienced coders here brief me on how to extract the data from the language column in the database, and display the same as <th> for a table. I would be ever so grateful if I could be talked through the appropriate code or even the steps on this.
Many thanks in advance – Melwyn.

[edited by: eelixduppy at 5:17 pm (utc) on Dec. 22, 2009]

[rocknbil]

I'm not sure which you mean, so here's both.

To get the table fields, which is what I'm guessing you want by <th>,

$flds=field_names('your-table-name');
echo <tr>";
foreach ($flds as $hd) { echo "<td>$hd</td>"; }
echo "</tr>";

function field_names($table) { 
 if (! isset($table)) { die("No table in field names"); } 
 $fields = Array (); 
 $counter=0; 
 $result = mysql_query("show columns from $table"); 
 if (!$result) { die("Could not get table names: " . mysql_error()); } 
 while ($row = mysql_fetch_array($result,MYSQL_NUM)) { 
 $fields[$counter]=$row[0]; 
 $counter++; 
 } 
 return $fields; 
} 

To build a select list by language,

echo ' 
<select name="language" id="language"> 
<option value="">Select Language</select> 
'; 
$result = mysql_query("select language from articles order by language asc"); 
if (!$result) { die("Could not get languages"); } 
while ($row = mysql_fetch_array($result,MYSQL_NUM)) { 
 echo <option value="' . $row[0]; 
 if (isset($_POST['language']) and ($_POST['language']==$row[0])) { 
 echo ' selected';  
 } // or selected="selected", for XHTML 
 echo '">' . $row[0] . '</option>'; 
 $counter++; 
} 
echo '</select>'; 

[mvaz]

That's superb coding rock. Many thanks, it works like a charm. Much appreciated.

[mvaz]

Hi all
the code provided by rocknbil is fine, but please see below and advise if this is possible at all.

Id ¦ Title ¦ Author ¦ Language ¦ Submit_Date ¦ Article ¦ Approve ¦ Publish_Date
English
French
German
English
German
Polish
English
Arabic

What I am trying to do is, convert the language column as table headers and list all articles under their respective language columns in the table.

I can extract the language colum data from the table, but cannot find a solution to use this data as a table header, where the language is displayed only once.

Many thanks in advance - Melwyn

[rocknbil]

I wouldn't do it this way. Eliminate "language" from the column output and try this.
The two descriptions are confusing, looks like you have one data set in your original post, something else in the last post.

I'll just cobble something together based on the original table description:
Id¦article_description¦author¦upload_date¦language

Consider the following styles.

.header { background:#000000; text-align:center; font-weight:700; } /* black bg/wht txt*/
.lang-header { background: #f0ecdd; font-weight:700; } /* light neutral tan-ish */

May contain errors, typed on the fly, debug as required

//Id¦article_description¦author¦upload_date¦language  
//Group by is not really needed 
$query = "select * from articles order by language asc,upload_date desc"; 
$lang_printed=0; 
$curr_lang=$content=NULL; 
$result=mysql_query($query); 
// Note how the language header works. IT starts as "null" so 
//obviously "Arabic" != NULL. When the language changes to English, 
// Arabic != English to it will generate another language row - once. 
while ($row = mysql_fetch_array($result)) { 
 if ($row[4] != $curr_lang) { 
 $content .= ' 
 <tr><td class="lang-header" colspan="4">'.$row[4].'</td></tr>'; 
 $curr_lang=$row[4]; 
 } 
 // id, title/desc. with link, author, date 
 $content .= ' 
 <tr><td>' . $row[0] . '</td> 
 <td><a href="details.php?id=' . $row[0] . '">' . $row[1] . '</a></td> 
 <td>' . $row[2] . '</td> 
 <td>' . $row[3] . '</td></tr> 
 '; 
} 
if ($content) { 
 echo ' 
 <table width="75%" align="center"> 
 <tr> 
 <td class="header">ID</td> 
  <td class="header">DESC</td> 
  <td class="header">AUTHOR</td> 
  <td class="header">DATE</td> 
 </tr>' . 
 $content . 
 '</table>'; 
} 
else { echo '<p>No articles to display</p>'; } 

This should give you:

ID------------DESC-----------AUTHOR------------DATE
Arabic -------------------------------------------
122------ title 1------------author 1 ------- date 1
English -------------------------------------------
123------ title 2------------author 2 ------- date 2
124------ title 3------------author 3 ------- date 3
125------ title 4------------author 4 ------- date 4
French --------------------------------------------
126------ title 5------------author 5 ------- date 5
German --------------------------------------------
127------ title 6 -----------author 6 ------- date 6
128------ title 7------------author 7 ------- date 7
Polish ---------------------------------------------
129------ title 8------------author 8 ------- date 8

[mvaz]

Many thanks rocknbil, I will give it a try this evening, and let you know how it works; I am sure, with my experience in the solutions you have provided in the past, it will definitely work fine.
Once again, many thanks.