how can I populate a list box with column names from sql?

[shure2]

Hi,

I'm getting close but need a final bit of help to get my list box populating.

I have a table called company with the columns compname, compcode, compindustry

I want to populate these in a listbox and keep the value of their name (such as compname), but have it renamed for the list box to something better like 'Company Name'. I don't really want to change the database column names at this point because I'll have to change all my code.

Here is what I have:

<select name="field">
		<?php
			$selectfield = "select column_name from information_schema.columns where table_schema = 'mct' AND table_name='company'";
			$resultfield = mysql_query($selectfield) or die(mysql_error());
                        $rowfield=mysql_fetch_array($resultfield);

                    echo '<option value="'.$rowfield.'"</option>'; //echo compname with 'Company Name' label'
                    //echo compcode with 'Company Code' label'
                    //echo compindustry with 'Company Industry' label'

thanks for your advice

[nawrik]

Just an idea...
...it may send you on the right track.

Separate every value in the array.
(i'm not sure how you would do this, but this is how I envisage it):

$rowfield['compname'];
$rowfield['compcode'];

etc....

Then perform a series of PHP string functions on each variable:

Maybe using

$rowfield['compname'] = substr_replace($rowfield['compname'], 'pany ', 3, -4);

This (I think) would count 3 characters into the variable (com) and 4 characters in from the right (name) and replace with "pany " (*note the space after 'y').

Do this for compcode (3, -4) and compindustry (3, -8).

They should equal = company name / company code / company industry.

Then maybe use:

ucwords($rowfield['compname']);

Which converts the first letter of every word in a string to uppercase:

Company Name.

Just a thought.

I don't understand enough about pulling arrays and whether they even work in string functions but give it a try.

I would like to see your attempts.

Good luck

[bradgrafelman]

Will the table structure change in the future (e.g. columns added/deleted/renamed)? If not, why not just hardcode the structure into the PHP script?

If it can change, then where do the names (e.g. "Company Name" for "compname") come from? Sounds like you'd want to create a separate table that matches a column name to a display name.

[shure2]

thanks for your help guys, I have hard coded it as a temporary solution, but will use your advice to build a more dynamic solution in the future.

[Tonata]

Hi, I am also a newbie. Im not sure if this is what you are looking for

<?php 
   echo  "<td colspan='8'><form id='form1' name='form1' method='post' action='coulmn_names.php'>";

 $query = mysql_query("SELECT * FROM `company`");
 $option1 = mysql_field_name($query,1);
 $option2 = mysql_field_name($query,2);
 $option3 = mysql_field_name($query,3);
 $option4 = mysql_field_name($query,4);

 if ($option1="compname")
 {
	 $option1 = "Company Name";
 }

  if ($option2="compcode")
 	{
	 $option3 = "Company Code";
 }
  	echo "Select:";
  	echo "<select name='select'>"; 
    echo "<option>$option1</option>";
echo "<option>$option2</option>";
    echo "</select>";

echo "</form>";
 ?>

If you found a solution please post for all of us, thank you.