Hi
What i want to do is to have a HTML select that contains values read from my database?

i'm reading data for the values of a HTML Select.
i use the below code to get the values from my database. i echo these to the screen and they're as expected.

$sql="SELECT * FROM CallType;";
$rs=odbc_exec($conn,$sql);

for ($row=0;odbc_fetch_row($rs);$row++)
{
$CallType=odbc_result($rs,"CallType");
$one = array(
$row => $CallType

);
echo $one[$row];
}

then using the below HTML call buildSelect function(i obtained from the web), but this ownly puts the 1st and last values obtained from the database into the Select.
Can anyone explain how it works?

<select name="CallType">
<? buildSelect($one, array("Call In (Repairs)", "true", "")) ?>
</select>

function buildSelect($selArr, $selOpt){
$selectText = $selOpt[0];
if ($selectText != "") {
$selDefault = "selected";
if ($selOpt[1] == "true") {
$selDefault = "";
}
echo "<option $selDefault>$selectText</option>\n";
}
foreach($selArr as $key => $value){
$sel = "";
for ($loop = 2; $loop < count($selOpt); $loop++) {
if ($selOpt[$loop] == $key) {
$sel = "selected";
}
}
echo "<option $sel value='$key'>$value</option>\n";
}
return;
}

    Here is a function I use often for this purpose

    function dbselectbox($db,$selname,$sql,$optvalue,$optname,$selected="",$topvalue="0", $topname="Select", $maxwidth=""){
		// start the select output
		$output = "<select name=\"". $selname . "\">";
		if($topvalue!=""){
			// we have a top value
			// now check to see if there is more than one
			$tvarray=explode(",",$topvalue);
			$tnarray=explode(",",$topname);
			for($f=0;$f<=count($tvarray)-1;$f++){
				$output.="<option value='".$tvarray[$f]."'";
				$output .= $tvarray[$f] == $selected ? " selected" : "";
				$output.=">".$tnarray[$f]."</option>";
			}
		}
		// now read the db for the rest of the options
		if($results=$db->get_results($sql)){
			foreach($results as $row){
				$loop = 0;
				$names = explode(",", $optname);
				$namestring = "";
				for($loop=0;$loop<=count($names);$loop++){
					$namestring .= $row->$names[$loop] . " ";
					++ $loop;
				}
				$output .= "<option value=\"" . $row->$optvalue."\"";
				$output .= $row->$optvalue == $selected ? " selected" : "";
				//if we have specified a maximum width then truncate the string
				if($maxwidth>0) $namestring=substr($namestring,0,$maxwidth);
				$output .= "> " . $namestring;
				$output .= "</option>";
			}
		}
		//finally complete the select statement and return the whole thing
		$output .= "</select>&nbsp;&nbsp;";
		return($output);
	}

    You will need to change the "if($results=$db->get_results($sql)){" line to do your database call and pass the results into $results

    Phodetheus

      Hi Phodetheus,
      Thanks from your help from looking at you code and the code i had i was able to come up with the code below, which works ok. Just have to make it a function and drop it into the main code

      <?php // build arrays for select boxes
      $conn=odbc_connect('SupportSystem','ccs','ccs');
      if (!$conn)
      {
      echo "Connection BAD";
      exit("Connection Failed: " . $conn);
      }
      $sql="SELECT * FROM CallType;";
      $rs=odbc_exec($conn,$sql);
      echo "<table><tr>";
      echo "<th>Call Type</th>";
      "</tr>";
      $output .= "<select name=\"Call Source\">";
      while (odbc_fetch_row($rs))
      {
      $CallType=odbc_result($rs,"CallType");
      echo "<tr><td>$CallType</td>";
      $output .= "<option value=\"$CallType\">$CallType";
      $output .= "</option>";
      }
      $output .= "</select>";
      echo "$output";
      ?>

        Made it into a function. Pretty happy with it now. Although openning and closing database for each Select box ()

        <?php // build arrays for select boxes

        function select_Generation($TableName,$ColumnName){
        $conn=odbc_connect('SupportSystem','ccs','ccs');
        if (!$conn)
        {
        exit("Connection Failed: " . $conn);
        }
        $sql="SELECT * FROM $TableName;";
        $rs=odbc_exec($conn,$sql);
        $output = "<select name=\"$ColumnName\">";
        while (odbc_fetch_row($rs))
        {
        $CompanyType=odbc_result($rs,"$ColumnName");
        $output .= "<option value=\"$CompanyType\">$CompanyType";
        $output .= "</option>";
        }
        $output .= "</select>";
        echo "$output";
        odbc_close($conn);
        }
        ?>

        <body>
        <?php select_Generation('CallType','CallType')?>
        </body>
        </html>

          Looks good.

          How many select boxes do you have and how many visitors do this page are you expecting? If it's only a few then this should not be a problem at all.

          Phodetheus

            Maybe a max of 6 to 10 select boxes on a page.
            Should only be a handful of users at a time, worst case senario max visitors at the same time would be 10-15.
            Using Sybase so this shouldn't be a problem, I just don't want to have too much openning & closing of the database and cause the performance to suffer.

              I am a MySQL user myself but if sybase is anything like it these should not cause even the slightest flicker. Maybe other sybase users could comment.

              Phodetheus

                Write a Reply...