Dynamic Select Boxes on Forms

konaman

What is the most efficient way to populate multiple select boxes on a form with content from a MySQL db?

The goal is to hardcode as little as possible in the forms and have the select box contents generated for multiple fields on the form.

The select box contents may / or may not come from different datasources.

Thanks in advance - konaman

Anon

You can do something like this:

$sql="select id, name from table order by name";
$result = mysql(database,$sql);

$selectBox ='<select name="countries">';

while($row=mysql_fetch_array($result))
{
$selectBox.='<option value="'.$row[id].'">'.$row[name].'</select>';

}

$selectBox.='</select>';

echo $selectBox;

Guillermo

konaman

Dude - you're the heat. Thanks.

With the current lack of stored procedures, etc - in MySQL - do you have any suggestions on how optimize the amount of pull on the server?

For example, loading up all the in-line sql queries at the top of the form vs. only populating the control (secect box)if a user clicks on it? -- konaman

Anon

If your options don't change much but still you want to do them dynamic you could try something like this:
Im not sure this is even going to work but i just thought of it. and hope it works 🙂

function createSelect($file)
{
// this looks for the file

if(file_exists($file))
{
$time = filemtime($file);
$time_now= mktime();
$dif=(($time_now- $time)/86400);

  // this check how old it is
  if($dif<1)//less than one day old
  {
     include($file);
  }else
  {
     regSelect($file);
  }

}else
{
regSelect($file);
}
}

then you need a regSelect function:

function regSelect($this)
{
if($this!="")
{
if($this=='select_1.txt')
{
$sql="select id, name from table_1 order by name";

}else
if($this=='select_2.txt')
{
  $sql="select id, name from table_2 order by name";   
}

$result = mysql(database,$sql);

while($row=mysql_fetch_row($result))
{
    $select.='<option value="'.$row[0].'">'.$row[1].'</option>';    
}

// what this does is generate a txt file that contains all the options
// of the select and names it as select_1.txt or select_2.txt

$file=fopen($this,"w+");
fputs($file,$select);
fpassthru($file);

}else
{
echo "No select to generate !!";
}
}

How you use this should be something like this:

Then if the file select_1.txt already exists and it is quite new then the server will just bring the txt file fast
and if it wasn't already created or is quite old it will regenerate it and use it.

Im not sure if the response is going to be faster in this select example but you could use the same kind of thing and try to cache heavy pages.

Guillermo

konaman

Dude -

Please double check the syntax on this.
What I'm getting is a select box that is not
populated -- but the variables are being output to the right of the select box.

Konaman

Guillermo wrote:

You can do something like this:

$sql="select id, name from table order by name";
$result = mysql(database,$sql);

$selectBox ='<select name="countries">';

while($row=mysql_fetch_array($result))
{
$selectBox.='<option value="'.$row[id].'">'.$row[name].'</select>';

}

$selectBox.='</select>';

echo $selectBox;

Guillermo

guillermo

My Mistake replase the $row[name].'</select>';

with $row[name].'</option>';

Guillermo