php, mysql, how can I get the field name inside my dynamic sql html table loop ?

Question

I'd like to do something different depending on field name in my loop. My code dynamically produces a html table based on the fields used in the sql query. I'd like to produce a link to a certain page if the field in the loop is the primary key... Any ideas ?

I've marked where I need to get the field name with HERE.

    if (mysql_num_rows($result)>0) {
        //loop thru the field names to print the correct headers
        $i = 0;

        while ($i < mysql_num_fields($result)) {
            $out .= "<th bgcolor='#CFCFCF'><font size=2>". mysql_field_name($result, $i) . "</font></th>";
            $i++;
        }
        echo "</tr>";

        //display the data
        while ($rows = mysql_fetch_array($result,MYSQL_ASSOC)) {
            $out .= "<tr>";
            foreach ($rows as $data) {
                //HERE
                $out .= "<td bgcolor='#DCDCDC'><font size=2>". $data . "</font></td>";
            }
        }
    }

Answer 1

I think the only way to do this is to do a SHOW COLUMNS FROM tablename; query beforehand.

The "key" column in the result will give you the key status for each column. You can find and save the position of the primary key from there, and output that column's HTML accordingly.

Answer 2

Try this:

if (mysql_num_rows($result)>0) {
    //loop thru the field names to print the correct headers
    $i = 0;

    while ($i < mysql_num_fields($result)) {
        $out .= "<th bgcolor='#CFCFCF'><font size=2>". mysql_field_name($result, $i) . "</font></th>";
        $i++;
    }
    echo "</tr>";

    //display the data
    while ($rows = mysql_fetch_array($result,MYSQL_ASSOC)) {
        $out .= "<tr>";
        foreach ($rows as $colName=>$data) {
            if($colName == 'keyname'){
                doStuff();
            }else{
                $out .= "<td bgcolor='#DCDCDC'><font size=2>". $data . "</font></td>";
            }
        }
    }
}

if you want to give a certain col you take from mysql a certain name do it like that:

SELECT table.id as 'otherName' , table.phone as 'PhoneNumber'  FROM table

then the resulting array would look like that

array( "othername" => 1, "PhoneNumber" => '0035 2500 65887')

EDIT: if you need to know if a certain ColName is a primary key then use this

if( mysql_num_rows(mysql_query("'SHOW INDEXES FROM day WHERE Column_name = '$colname' AND Key_name = 'PRIMARY'")) > 0){
    //is a primary key
}else{
    //is not a primary key
}