• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

returning columns headings with php and mysql database

hi,

i need to return the column headings of the database table, that i am doing a select query on.

Current using:

mysql_fetch_array($results)

to collect the data from the database then setting the array to $row, so i collect the results as follows:

print $row["id"]." ";
print $row["code"]." ";

etc....

What sort of coding would i need to return the columns headings??

Thanks in advance

FIMBRIA


0
fimbria
Asked:
fimbria
  • 2
1 Solution
 
VGRCommented:
easy
"describe tablename;"

I did a page that does this kind of thing

(extract, my script also offers databases choice, then table choice in DB, then this : )

<?
if (!isset($globTableLue)) {
    if ($sess_osWin==1) $linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
     else $linkID=mysql_pconnect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
    mysql_select_db($dbName,$linkID) or die ("bad select DB ".mysql_error());
    $query="describe $table;";
    $globNbChamps=0;
    $globLimited=FALSE;
    $result=mysql_query($query,$linkID) or die ("bad query get description of $table in $dbName. ".mysql_error());
    while ($res=mysql_fetch_array($result)) {
      $globChNom[]=$res[0]; // "Field"
      if($res[0]=='dateheure') $globLimited=TRUE;
      $globChTyp[]=$res[1]; // "Type"
      // REM on laisse tomber les colonnes suivantes ("Extra" e{t iti intiressant)
      $pos1=strpos($res[1],'int'); // suppose qu'il n'y a pas de flottants
      $boolPasTrouve=($pos1===false); // vrai si trouvi (via NOT pastrouvi) (pas d'autre solution)
      $globChAcc[]=(!$boolPasTrouve)?0:1; // on mimorise que c'est un numirique (pas de ' en requhte)
      $longueur=19; // difaut
      $pos1=strpos($res[1],'('); // suppose qu'il n'y a pas de flottants
      $boolPasTrouve=($pos1===false); // vrai si trouvi (via NOT pastrouvi) (pas d'autre solution)
      if (!$boolPasTrouve) { // trouvi  
         $pos2=$pos1+1;
         while ($res[1][$pos2]<>')') $pos2++;
         $subchaine=substr($res[1],$pos1+1,$pos2-1-$pos1); // le 3hme par est la longueur pas la position...
         $longueur=(integer)$subchaine;
      } // if longueur
      $globChLon[]=$longueur;
      $globNbChamps++;
    } //while champs de la table
    // le premier champ est implicitement la clef...
    $globTableLue=1;
    session_register("table");
    session_register("globTableLue");
    session_register("globLimited");
    session_register("globChNom");
    session_register("globChTyp");
    session_register("globChAcc");
    session_register("globChLon");
    session_register("globNbChamps");
} // else c'est dij` fait

//
// ----------------------------- LIGNES --------------------------------
//

if (isset($new)) { // positionnis : formulaire
  if ($sess_osWin==1) $linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
   else $linkID=mysql_pconnect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
  mysql_select_db($dbName,$linkID) or die ("bad select DB ".mysql_error());
  $query="replace into $table values (";
  for ($i=0;$i<($globNbChamps-1); $i++)
    if ($globChAcc[$i]==1) $query.="'".${'F'.$globChNom[$i]}."',"; else $query.=${'F'.$globChNom[$i]}.",";
  if ($globChAcc[$globNbChamps-1]==1) $query.="'".${'F'.$globChNom[$globNbChamps-1]}."'"; else $query.=${'F'.$globChNom[$globNbChamps-1]};
  $query.=");";
//test
echo "query add = $query <BR>";
//
  $result=mysql_query($query,$linkID) or die ("bad query replace in $table $query. ".mysql_error());
  LogAction($sess_pseudo,$REMOTE_ADDR,"DB : ajoute via $query.",4);
} // new case

if (isset($get)) {
  if ($sess_osWin==1) $linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
   else $linkID=mysql_pconnect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
  mysql_select_db($dbName,$linkID) or die ("bad select DB ".mysql_error());
  $query="select * from $table where ".$globChNom[0]."=";
  if ($globChAcc[0]==1) $query.="'";
  $query.=${'F'.$globChNom[0]};
  if ($globChAcc[0]==1) $query.="'";
  $query.=";";
//test
//echo "query get = $query <BR>";
//
  $result=mysql_query($query,$linkID) or die ("bad query get $table ID=".$globChNom[0]." . ".mysql_error());
  $res=mysql_fetch_array($result);
//test
//echo $res[0]." ".$res[1]." ".$res[2]." ".$res[3]." ".$res[4]." ".$res[5]." ".$res[6]."<BR>";
//
  for ($i=0;$i<$globNbChamps; $i++) {
    ${'loc'.$globChNom[$i]}=$res[$globChNom[$i]];
//test
//echo "$i ".$globChNom[$i]."=".$res[".$globChNom[$i]."]."<BR>";
//echo "$i loc=".${'loc'.$globChNom[$i]}."<BR>";
//
  } // for de chargement
  echo "a lu enregistrement ".${'F'.$globChNom[0]}."<BR>";
  LogAction($sess_pseudo,$REMOTE_ADDR,"DB : lit via $query.",4);
}

//VGR22112001 ADDed special code for options des sondages, disoli...
if (isset($del)) {
  $FID=${'F'.$globChNom[0]};
  if ($FID<>'') {
    if ($sess_osWin==1) $linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
     else $linkID=mysql_pconnect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
    mysql_select_db($dbName,$linkID) or die ("bad select DB ".mysql_error());
    $query="delete from $table where ".$globChNom[0]."=";
    if ($globChAcc[0]==1) $query.="'";
    $query.=$FID;
    if ($globChAcc[0]==1) $query.="'";
    //VGR22112001 FIXed bug
    if ($table==$dbTableOptions) {
      $FID2=${'F'.$globChNom[1]};
      $query.=" and ".$globChNom[1]."=";
      if ($globChAcc[1]==1) $query.="'";
      $query.=$FID2;
      if ($globChAcc[1]==1) $query.="'";
    } // if options des sondages
    //EoFix
    $query.=";";
//test
//echo "query del = $query <BR>";
//
    $result=mysql_query($query,$linkID) or die ("bad query del $FID . ".mysql_error());
    echo "a effaci enregistrement $FID<BR>";
    LogAction($sess_pseudo,$REMOTE_ADDR,"DB : a effaci via $query.",4);
  } else echo "<BR>rien ` effacer<BR>";
}


echo "<HR><font color=red>Formulaire de modification de la table $table</FONT>";
echo "<FORM METHOD=\"POST\" ACTION=\"$SCRIPT_NAME\">"; //VGR23062002 ENCTYPE=\"multipart/form-data\"
echo "ID=".$globChNom[0].' '.$globChTyp[0]."&nbsp;".(($globChAcc[0]==1)?'texte':'numirique')."&nbsp;<INPUT border=0 TYPE=text NAME=F".$globChNom[0]." VALUE=\"".${'loc'.$globChNom[0]}."\" SIZE=".Max($globChLon[0],9)." MAXLENGTH=".$globChLon[0].">&nbsp;"; // l'ID
echo "<INPUT border=0 TYPE=submit NAME=get VALUE=\"Get\">&nbsp;&nbsp;<INPUT border=0 TYPE=submit NAME=new VALUE=\"Add/Mod\">&nbsp;&nbsp;<INPUT border=0 TYPE=submit NAME=del VALUE=\"Del\"><BR>"; // bouton Load par difaut
echo "<INPUT TYPE=hidden NAME=globTableLue VALUE=$globTableLue>";
echo "<INPUT TYPE=hidden NAME=table VALUE=$table>";
echo "<TABLE>";
echo "<tr><td>nom champ</td><td>type</td><td>genre</td><td>valeur</td></tr>";
for  ($i=1; $i<$globNbChamps; $i++){ // on saute le 0 qui est ci-dessus
  if ($globChTyp[$i]=='text') echo "<TR><TD>".$globChNom[$i]."</td><TD>".$globChTyp[$i]."</td><TD>".(($globChAcc[$i]==1)?'texte':'numirique')."</td><TD><TEXTAREA NAME=F".$globChNom[$i]." ROWS=6 COLS=60 WRAP=\"soft\" MAXLENGTH=400>".${'loc'.$globChNom[$i]}."</TEXTAREA></td></tr>";
   else echo "<TR><TD>".$globChNom[$i]."</td><TD>".$globChTyp[$i]."</td><TD>".(($globChAcc[$i]==1)?'texte':'numirique')."</td><TD><INPUT border=0 TYPE=text NAME=F".$globChNom[$i]." VALUE=\"".${'loc'.$globChNom[$i]}."\" SIZE=".Min($globChLon[$i],70)." MAXLENGTH=".$globChLon[$i]."></td></tr>";
} // for d'affichage des autres champs
echo "</TABLE>";
echo "</FORM>";
echo "<HR>";

// listage
echo "<TABLE border=1>";
echo "<tr>";
for  ($i=0; $i<$globNbChamps; $i++) echo "<td>".$globChNom[$i]."</td>";
echo "</tr>";
if ($sess_osWin==1) $linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
 else $linkID=mysql_pconnect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
mysql_select_db($dbName,$linkID) or die ("bad select DB ".mysql_error());
//construction requhte
$query="select * from $table;";
if ($globLimited) $query.=" order by dateheure desc limit 20;"; // 20 last lines shown below the FORM
else $query.=" order by ".$globChNom[0]." desc limit 20;";
//suite
$result=mysql_query($query,$linkID) or die ("bad query get $table listing . ".mysql_error());
while ($res=mysql_fetch_array($result)) {
  echo "<tr>";
  for ($i=0; $i<$globNbChamps; $i++) echo "<td>".$res[$globChNom[$i]]."</td>";
  echo "</tr>";
} // while de parcours
echo "</TABLE>";

?>

have fun : just set the $db* variables and $table and you're done.
0
 
wasserCommented:
I'd suggest taking a look at the php manual concerning MySQL (http://www.php.net/manual/en/ref.mysql.php).

Specifically, try something like this:

$num_fields = mysql_num_fields($results);

for($i = 0; $i < $num_fields; $i++) {
     $field_name = mysql_field_name($results, $i);
     print "$i: $field_name <br />";
}

I'd test this, but I've only got Oracle handy.

VGR's code may have contained a better way, but I don't have the patience to read 158 lines of code (especially since I only read english :)).

~wasser
0
 
VGRCommented:
code is in English, Sir ;-)

you are right, if mysql_field_name() gives the headings then it's definitely better.

My script's purpose is to offer a automatic-adapting set of FORMs and functions to maintain any table of any database on my system. It works. It has one drawback (requires a PRI KEY column or a unique auto_increment one) but it serves my purposes so far, and since 2 years I guess
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now