I am writing a page for work that uses php and mysql to store and view a list of common commands used on our servers. I want to make it fully dynamic. By this I mean the command field names are pulled from the database. This is so the managers can add new fields to the commands later on.
What i'm trying to do is dynamically list the headers across the top of the table. Then below each of the headers list the information for each command so each command is in it's own row.
I have found a way to pull a list of the fields in the "command" table by querying the "information_schema" table. I'm using a table to organize it and at first used a while statement with my query to make headers for each of the fields. Then I have another query to pull the data in each field with another while statement. I nested that one inside the first one to pull the data for each of the dynamically generated fields. That worked however it put everything in one column with each header being listed once for each command.
When that didn't work like i wanted it I tried ending my first while statement so I have my one row of headers then making a 3rd query identical to the first to again pull the list of fields and nested the second query of field data inside. When i did that the query gave me an empty result.
I was thinking if I could join my second query with the "information_schema" table that might be the solution but I don't know how if it's even possible since the only thing to join with would be the column name from the "command" table. Here is the related code with only the first 2 queries. I've also attached a screenshot of what the page looks like in the browser with the current code. Can anyone offer me any suggestions?
$sql3 = "SELECT COLUMN_NAME FROM information_schema.`COLUMNS` C WHERE table_schema='helpdesk' and table_name='command' and ordinal_position>3 order by ordinal_position";
$sql = "SELECT * FROM `command` LEFT JOIN `comcat` ON command.cat=comcat.ctid WHERE comcat.catname='$cat' and command.active='0' order by syntax";
$query3 = mysql_query($sql3, $conn) or die(mysql_error());
$row3 = mysql_num_rows($query3);
while ($row3 = mysql_fetch_array($query3)){
$query = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_num_rows($query);
while ($row = mysql_fetch_array($query)){
echo '<tr><th>'.$row3['COLUMN_NAME'].'</th></tr><tr><td>'.$row[$row3['COLUMN_NAME']].'</td></tr>';
} }
echo '</table></center><br>';