Field names depending on select statement

[rosko1970]

Hi,

I want to query an db, show the results with the field names on top.
I can get the fieldnames but if i limit the select statement to two fields for instance it will keep displaying all the field names of the table.

this is what i have, i only include the result

<code>
echo "<table>\n";

echo "<tr>\n";

$fields = mysql_listfields( "database", "table");
$field_num = mysql_num_fields($fields);
$i=0;
while ($i < $field_num){

            $col_name  = mysql_field_name  ($fields, $i);

echo "<td>$col_name</td>";

$i++;
}
echo "</tr>";

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";

</code>

Can anyone help me with this ? I googled around for a long time but could not get an good tutorial about this.

Thanks.
Alex

[Houdini]

First off <code> and </code> with all you have inside it is useless and mysql_listfields is not (I stand corrected but it and mysql_list_fields are both deprecated) a valid MySQL or PHP function mysql_list_fields( string database_name, string table_name [, resource link_identifier] ) is so let's try to clean up your code first. When posting your code it would help to use the tools that allow code display properly namely the # sign the <> and the ~ after selecting your code like below

<?php //not code
echo "<table>\n";
echo "<tr>\n";
$fields = mysql_list_fields( "database", "table");//I fixed this for ya
$field_num = mysql_num_fields($fields);
$i=0;
while ($i < $field_num){
$col_name = mysql_field_name ($fields, $i);
echo "<td>$col_name</td>";
$i++;
}
echo "</tr>";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
?>

Try to remove white space which I did now your code looks easier to read and maybe you can get some help.

[rosko1970]

Thanks for correcting.

I'm not much of an forum user, this is like the first forum i ever registered.

hope this helps me learning better php

[Houdini]

OK rosko1970 then what are you wanting to do with PHP HTML and MySQL? You stated that you want to show the field names on top of a browser page kind of a table with headers in HTML then input the actual fields that are contained within your datebase, is this kind of what you want to do?

[rosko1970]

if i do an select * from whatever database is should return also the field names without me defining them. if i change the query to include just two fields, select name, date then it should just return the values and also the field names.
and it should be presented just in html.

[Houdini]

So you are wanting to show just a couple of database fields from a given database with a selection of your choice which the SQL would look kind of like

SELECT name, date FROM tablename

is this correct? or with PHP it would look like

 $query="SELECT name, date FROM tablename";

[rosko1970]

that's correct, and the result should you the database field names as well, no matter what the select part of the query looks like.

[Sxooter]

This is pretty basic stuff. Here's a code fragment that does sort of what you want. I'll leave it up to you to make it do the table stuff and all that:

$query = "select field1, field2....";
$res = _query($query);
$header = true;
print "<table>";
while ($row = _fetch_assoc($res)){
    if ($header){
        print "<TR><TD>";
        print implode("</TD><TD>",array_keys($row));
        print "</TD></TR>";
        $header = false;
    }
    print "<TR><TD>";
    print implode("</TD><TD>",array_values($row));
    print "</TD></TR>";

}
print "</TABLE>";

[rosko1970]

Yeah, this is what i wanted.
small typo in _fetch_assoc, changed it in mysql_fetch_assoc and it worked
now i don't have to declare the column headers everytime.

Thanks for the support !

[Sxooter]

It wasn't really a typo, I use PostgreSQL so I'm never quite sure what the exact equivalent is for mysql.

pretty cool way to do it, eh?

[rosko1970]

Indeed pretty cool, i'm loving this stuff more and more
and when looking at this piece of code it is pretty obvious (read logical)
now i need for oracle as well, but that should be fairly similar.

[Houdini]

You can adapt it to you needs but you should see the logic behind it!

<?php 
$db ="test";//put your database name here!
$connect=mysql_connect(localhost,root)//you might need to change this to (localhost,aUserName,aPassword)
	or die("Could not connect to server".mysql_error());
$select= mysql_select_db($db,$connect)
	or die("Could not select database".mysql_error());
$query="SELECT name, date FROM tableName";//make the tableName your table name
$result=mysql_query($query);		
 ?>
 <center><table border="2" cellspacing="2" cellpadding="2">
 <tr>
 <th><font fact="Arial, Helvetica, sans-serif">Name</th>
 <th><font fact="Arial, Helvetica, sans-serif">Date</th>
 </tr>
 <?php 
 $i=0;
 while($row = mysql_fetch_array($result)){
   $name=mysql_result($result,$i,"name");
   $date=mysql_result($result,$i,"date");
 ?>
 <tr>
 <td><font face="Arial, Helvetica, sans-serif"><?php echo $name; ?></font></td>
 <td><font face="Arial, Helvetica, sans-serif"><?php echo $date; ?></font></td>
 </tr>
 <?php
 $i++;
 }
 echo"<table></center>";
 ?>
 

[rosko1970]

although this code is very tidy and neat (clean) this is not completely want i wanted, i still have to declare the fieldnames. I now understand more how this stuff falls together.
Thanks !!

The piece of code from Sxooter however was exactly what I meant, i only adjust the query and it gives all the fieldnames.