Page 1 of 1

Get field names and list as table headers

Posted: Thu Aug 28, 2008 5:04 am
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...)

Re: Get field names and list as table headers

Posted: Thu Aug 28, 2008 5:09 am
by onion2k
DESCRIBE `table`

You can work the rest out yourself.

Re: Get field names and list as table headers

Posted: Thu Aug 28, 2008 6:48 am
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...)

Re: Get field names and list as table headers

Posted: Thu Aug 28, 2008 6:52 am
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?

Re: Get field names and list as table headers

Posted: Thu Aug 28, 2008 3:40 pm
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.

Re: Get field names and list as table headers

Posted: Thu Aug 28, 2008 3:47 pm
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.

Re: Get field names and list as table headers

Posted: Fri Aug 29, 2008 1:43 am
by pyromaster114
Yeah, see, i knew there was a better way to do it.

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