Get field names and list as table headers

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pyromaster114
Forum Newbie
Posts: 19
Joined: Fri Mar 14, 2008 8:12 pm

Get field names and list as table headers

Post by pyromaster114 »

I'm trying to create a small script that will, when given a table in a mysql database, make an HTML table of all the entries in it, with a header on each column that is the field name from the SQL table.

I am at a loss as to how to get the thing to actually spit out the table names currently... let alone put them as the headers for the first row of the HTML table.

Help is greatly appreciated... It's driving me nuts that I can't figure out how to do this. (Cause it should be really easy...)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Get field names and list as table headers

Post by onion2k »

DESCRIBE `table`

You can work the rest out yourself.
pyromaster114
Forum Newbie
Posts: 19
Joined: Fri Mar 14, 2008 8:12 pm

Re: Get field names and list as table headers

Post by pyromaster114 »

I seriously have yet to get that command to work in any PHP script ever that I've written.
:banghead:
This isn't working... cause I get the stuff to spit it out once when I do this:

Code: Select all

<?php
 
/*SQL Settings */
//address of your mySQL server
$hostname = "localhost";
//username to your mySQL server (must have full access)
$hostusername = "root";
//password to username on your mySQL server
$hostpassword = "";
//database name you wish to use
$dbname = "database1";
/*End SQL Settings */
 
/*MySQL Database Connect */
@mysql_connect($hostname,$hostusername,$hostpassword) or die ("error");
/*End MySQL Database Connect */
 
/*MySQL Select Database */
@mysql_select_db($dbname) or die("error");
/*End MySQL Select Database */
 
/*Get Table Name */
$tablename = "table1";
/*End Get Table Name */
 
$tableinfo = mysql_query("SHOW COLUMNS FROM $tablename");
 
while ($column = mysql_fetch_assoc($tableinfo))
{
    echo "$column[Field] <br />";
}
?>
However, I can't get it to do it again.
(I need it to do it again essentially to call up the fields that it's now found to get the info in them out and into the HTML table...)

Why when I do this does it only do it once still?

Code: Select all

<?php
 
/*SQL Settings */
//address of your mySQL server
$hostname = "localhost";
//username to your mySQL server (must have full access)
$hostusername = "root";
//password to username on your mySQL server
$hostpassword = "";
//database name you wish to use
$dbname = "database1";
/*End SQL Settings */
 
/*MySQL Database Connect */
@mysql_connect($hostname,$hostusername,$hostpassword) or die ("error");
/*End MySQL Database Connect */
 
/*MySQL Select Database */
@mysql_select_db($dbname) or die("error");
/*End MySQL Select Database */
 
/*Get Table Name */
$tablename = "table1";
/*End Get Table Name */
 
$tableinfo = mysql_query("SHOW COLUMNS FROM $tablename");
 
while ($column = mysql_fetch_assoc($tableinfo))
{
    echo "$column[Field] <br />";
}
echo "<br />";
 
while ($column = mysql_fetch_assoc($tableinfo))
{
    echo "$column[Field] <br />";
}
 
 
?>
(Note the repeat of the while statement. Why doesn't this work? I've been fighting with this thing for like an hour now...)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Get field names and list as table headers

Post by onion2k »

After the first while loop the pointer that tells PHP where in the recordset it should be is at the end. There are no more records. Next time you try to go through it with a while loop ... no more records. You'd need to reset the pointer to the start with mysql_data_seek($tableinfo, 0);

That said though.. why do you need to loop around it again?
pyromaster114
Forum Newbie
Posts: 19
Joined: Fri Mar 14, 2008 8:12 pm

Re: Get field names and list as table headers

Post by pyromaster114 »

I need to loop it around again because after putting the names of the stuff in the HTML table's header,
I need to actually do a mysql_query() for the actual information in the table, and then output it under each table header correctly (echo $result[fieldname])
therefore, I need the names again to actually tell it which part of the row to put where... I'm sure there's a better way of doing it, but I can't think of it.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Get field names and list as table headers

Post by onion2k »

Put the field names into an array, then use a foreach to loop around that array for each row in the second query. Eg

Code: Select all

$cols = array();
 
$columns = mysql_query("SHOW COLUMNS FROM $tablename");
while ($c = mysql_fetch_object($columns)) {
  $cols[] = $c->field;
}
 
echo "<table>";
echo "<tr>";
foreach ($cols as $c) {
  echo "<td>".$c."</td>";
}
echo "</tr>";
 
$result = mysql_query("SELECT * FROM $tablename");
while ($record = mysql_fetch_object($result)) {
  echo "<tr>";
  foreach ($cols as $c) {
    echo "<td>".$record->$c."</td>";
  }
  echo "</tr>";
}
 
Something like that anyway. I've not tested that.
pyromaster114
Forum Newbie
Posts: 19
Joined: Fri Mar 14, 2008 8:12 pm

Re: Get field names and list as table headers

Post by pyromaster114 »

Yeah, see, i knew there was a better way to do it.

Anyways, i got it working now. Thanks for the help.
Post Reply