?
Solved

returning columns headings with php and mysql database

Posted on 2003-03-20
3
Medium Priority
?
271 Views
Last Modified: 2010-08-05
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
Comment
Question by:fimbria
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
VGR earned 150 total points
ID: 8177050
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
 
LVL 1

Expert Comment

by:wasser
ID: 8178027
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
 
LVL 15

Expert Comment

by:VGR
ID: 8179353
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

WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question