Option - Select - PHPBuilder Forums

Option - Select

Anon

Greetings,
I am using PHP4 and an Acess 2000 db. I am able to connect, query and dislay data. I am also able to disply the results from a table as a selection or drop down menu. My question is how to have the options for the drop down menu and also query the "parent table" to show what is already assigned in that record. Example: I want to upgrade a car record and the particular car has a V-6 option (displayed in the drop down) I want to be able to use the drop down menu to select a different engine size (V-8), then update the database. By the way my db is relational. Many thanks in advance.

Anon

well, assuming you have an array of vehicle options a particular record has ($my_options, in the example below), you could do something like this:

<select name=\"options\" multiple=\"yes\" size=\"5\">
<? while(list($k,$v) = @each($option_list)) {
$sel = (in_array($k,$my_options) ? \"SELECTED\" : \"\") ?>
<option <?= $sel ?> value=\"<?= $k ?>\"><?= $v ?>
<? } ?>
</select>

This assumes the key of $option_list is the value you store for options chosen....

Anon

I am sorry, but I am new to PHP and do not understand. To the best of my knowledge, I am not using an array. To query the data for my options I am using the following:
<?
//$cx = odbc_connect( 'vehicle', '', '' );
$selectCat=odbc_exec($cx," SELECT category.catID,
category.category,
category.categoryDescription
FROM category");
echo "<select name=\"catID\">";
echo $category1;

	while(odbc_fetch_row($selectCat))
	{
		$catID=odbc_result($selectCat,1);
		$category=odbc_result($selectCat,2);
		$categoryDescription=odbc_result($selectCat,3);
		echo "<option value=\"$catID1\">$categoryDescription1";
	}
echo "</select>";
?>

Then I am using a similiar query to get what my current selection is. My problem is to know combine them to show what the current record is and then to be able to show the option box of other options. Thanks in advance.

amcgrath

OK, but if I understand you correctly, then what you want to do is select (highlight) options in the select box that are already chosen... if that's the case, then run another query on the table that actually has the categories chosen...

Then, in a while() loop, store those values into an array (called $myCats)...

Then, use the example below.

while(odbc_fetch_row($selectCat))
{
$catID=odbc_result($selectCat,1);
$category=odbc_result($selectCat,2);
$categoryDescription=odbc_result($selectCat,3);
$sel = (in_array($catID,$myCats) ? "SELECTED" : "");
echo "<option $sel value=\"$catID1\">$categoryDescription1";
}
echo "</select>";
?>

Anon

Many thanks for all the help you have given me. I used your code and I am getting a wrong datatype error for the function in_array. Here is my first query to query the database to get the current configuration of the vehicle:
<?
$cx = odbc_connect( 'vehicle', '', '' );
$getVehicle=odbc_exec($cx,
"SELECT vehicle.vehicleID,
vehicle.regNumberID,
vehicle.date,
vehicleDescrip.vehicleDescrip,
grade.grade,
vco.vcoLname,
vco.vcoPhone,
vco.vcoEmail,
office.office,
flight.flight,
category.catID,
category.category,
category.categoryDescription
FROM category INNER JOIN ((flight INNER JOIN office ON flight.flightID =
office.flightID) INNER JOIN (grade INNER JOIN (vco INNER JOIN
(vehicleDescrip INNER JOIN vehicle ON vehicleDescrip.vehicleDescripID =
vehicle.vehicleDescripID) ON vco.vncoID = vehicle.vncoID) ON
grade.gradeID = vco.vcoGradeID) ON office.officeID = vco.officeID) ON
category.catID = vehicle.catID
WHERE (((vehicle.vehicleID)=$vehicleID))");
echo "<select name=\"vehicleID\">";
while(odbc_fetch_row($getVehicle))
{
$vehicleID=odbc_result($getVehicle,1);
$regNumberID=odbc_result($getVehicle,2);
$date=odbc_result($getVehicle,3);
$vehicleDescripID=odbc_result($getVehicle,4);
$grade=odbc_result($getVehicle,5);
$vcoLname=odbc_result($getVehicle,6);
$vcoPhone=odbc_result($getVehicle,7);
$vcoEmail=odbc_result($getVehicle,8);
$office=odbc_result($getVehicle,9);
$flight=odbc_result($getVehicle,10);
$catID=odbc_result($getVehicle,11);
$category=odbc_result($getVehicle,12);
$categoryDescription=odbc_result($getVehicle,13);
echo "<option value=\"$vehicleID\">$regNumberID, $vehicleDescripID, $grade $vcoLname";
}
echo "</select>";?>
Any ideas?

amcgrath

You're getting the error (I assume) because you haven't put the data into an array.

Change this:

while(odbc_fetch_row($getVehicle))
{
$vehicleID=odbc_result($getVehicle,1);
$regNumberID=odbc_result($getVehicle,2);
$date=odbc_result($getVehicle,3);
$vehicleDescripID=odbc_result($getVehicle,4);
$grade=odbc_result($getVehicle,5);
$vcoLname=odbc_result($getVehicle,6);
$vcoPhone=odbc_result($getVehicle,7);
$vcoEmail=odbc_result($getVehicle,8);
$office=odbc_result($getVehicle,9);
$flight=odbc_result($getVehicle,10);
$catID=odbc_result($getVehicle,11);
$category=odbc_result($getVehicle,12);
$categoryDescription=odbc_result($getVehicle,13);
echo "<option value=\"$vehicleID\">$regNumberID, $vehicleDescripID, $grade $vcoLname";
}

to this:

while(odbc_fetch_row($getVehicle))
{

$info[vehicleID]=odbc_result($getVehicle,1);
$info[regNumberID]=odbc_result($getVehicle,2);
$info[date]=odbc_result($getVehicle,3);
$info[vehicleDescripID]=odbc_result($getVehicle,4);
$info[grade]=odbc_result($getVehicle,5);
$info[vcoLname]=odbc_result($getVehicle,6);
$info[vcoPhone]=odbc_result($getVehicle,7);
$info[vcoEmail]=odbc_result($getVehicle,8);
$info[office]=odbc_result($getVehicle,9);
$info[flight]=odbc_result($getVehicle,10);
$info[catID]=odbc_result($getVehicle,11);
$info[category]=odbc_result($getVehicle,12);
$info[categoryDescription]=odbc_result($getVehicle,13);

Or, you can do it this way:

while(odbc_fetch_row($getVehicle))
{
$info = array("column" => odbc_result($column,$counter);

Anon

Pleaes forgive me, I am new to PHP and especially using it with Access. All help is greatly appreciated. I tried a combination of Allen's and vincent's help and still no dice. This is what I have so far and any help is greatly appreciated.

<?
$cx = odbc_connect( 'vehicle', '', '' );
$getVehicle=odbc_exec($cx,
"SELECT vehicle.vehicleID,
vehicle.regNumberID,
vehicle.date,
vehicleDescrip.vehicleDescrip,
grade.grade,
vco.vcoLname,
vco.vcoPhone,
vco.vcoEmail,
office.office,
flight.flight,
category.catID,
category.category,
category.categoryDescription
FROM category INNER JOIN ((flight INNER JOIN office ON flight.flightID =
office.flightID) INNER JOIN (grade INNER JOIN (vco INNER JOIN
(vehicleDescrip INNER JOIN vehicle ON vehicleDescrip.vehicleDescripID =
vehicle.vehicleDescripID) ON vco.vncoID = vehicle.vncoID) ON
grade.gradeID = vco.vcoGradeID) ON office.officeID = vco.officeID) ON
category.catID = vehicle.catID
WHERE (((vehicle.vehicleID)=$vehicleID))");
echo "<select name=\"vehicleID\">";

while(odbc_fetch_row($getVehicle)) 
	{ 
		$info[vehicleID]=odbc_result($getVehicle,1); 
		$info[regNumberID]=odbc_result($getVehicle,2); 
		$info[date]=odbc_result($getVehicle,3); 
		$info[vehicleDescripID]=odbc_result($getVehicle,4); 
		$info[grade]=odbc_result($getVehicle,5); 
		$info[vcoLname]=odbc_result($getVehicle,6); 
		$info[vcoPhone]=odbc_result($getVehicle,7); 
		$info[vcoEmail]=odbc_result($getVehicle,8); 
		$info[office]=odbc_result($getVehicle,9); 
		$info[flight]=odbc_result($getVehicle,10); 
		$info[catID]=odbc_result($getVehicle,11); 
		$info[category]=odbc_result($getVehicle,12); 
		$info[categoryDescription]=odbc_result($getVehicle,13);
		echo "<option value=\"$vehicleID\">.$info[regNumberID], $vehicleDescripID, $grade $vcoLname";
	}
		echo "</select>";?>

<tr>
<td width="25%"><b>Reg Number:</b></td>
<td width="75%"><INPUT TYPE="text" NAME="regNumber" VALUE="<? echo HTMLSpecialChars($regNumberID);?>"></td>
</tr>

<tr>
<td><b>Reg Category: </b></td>
<td>
<?

//$cx = odbc_connect( 'vehicle', '', '' );
$selectCat=odbc_exec($cx,"	SELECT 	category.catID, 
									category.category, 
									category.categoryDescription
							FROM 	category
								");
echo "<select name=\"catID\">";


while(odbc_fetch_row($selectCat)) 
	{ 
		$myCats[catID]=odbc_result($selectCat,1);
		$myCats[category]=odbc_result($selectCat,2);
		$myCats[categoryDescription]=odbc_result($selectCat,3); 
		$sel = (in_array($info,$myCats) ? "SELECTED\" : \"\"); 
		echo "<option $sel value=\"$catID\">$categoryDescription"; 
		//$noun12=$row["noun12"];
	} 
echo "</select>"; 
?>

amcgrath

Give me 2 mins, I'll show you in more detail what I mean...

amcgrath

Here, try this. I can't test it so there may be typo errors, but all in all, it should do what you want it to do....

<?
// Changed to pconnect() for persistency
$cx = odbc_pconnect( 'vehicle', '', '' );

// get the vehicle data
$query = "SELECT vehicle.vehicleID, vehicle.regNumberID, vehicle.date,
vehicleDescrip.vehicleDescrip, grade.grade, vco.vcoLname, vco.vcoPhone, vco.vcoEmail,
office.office, flight.flight, category.catID, category.category,
category.categoryDescription

FROM category
INNER JOIN
((flight INNER JOIN office ON flight.flightID = office.flightID)
INNER JOIN (grade INNER JOIN (vco INNER JOIN (vehicleDescrip INNER JOIN vehicle ON vehicleDescrip.vehicleDescripID =
vehicle.vehicleDescripID) ON vco.vncoID = vehicle.vncoID) ON
grade.gradeID = vco.vcoGradeID) ON office.officeID = vco.officeID) ON
category.catID = vehicle.catID
WHERE (((vehicle.vehicleID)=$vehicleID))";

$getVehicle=odbc_exec($cx, $query);

// get the category data
$query = "SELECT category.catID, category.category, category.categoryDescription FROM category";

// stick the results into an array
$tick = 0;
while(odbc_fetch_row($getVehicle,$tick++))
{
$curr_id = odbc_result($getVehicle,"vehicleID");
$getVehicleInfo[$curr_id]= array("regNumberID" => odbc_result($getVehicle,"regNumberID"),
"date" => odbc_result($getVehicle,"date"),
"vehicleDescripID" => odbc_result($getVehicle,"vehicleDescripID"),
"grade" => odbc_result($getVehicle,"grade"),
"vcoLname" => odbc_result($getVehicle,"vcoLname"),
"vcoPhone" => odbc_result($getVehicle,"vcoPhone"),
"vcoEmail" => odbc_result($getVehicle,"vcoEmail"),
"office" => odbc_result($getVehicle,"office"),
"flight" => odbc_result($getVehicle,"flight"),
"catID" => odbc_result($getVehicle,"catID"),
"category" => odbc_result($getVehicle,"category"),
"categoryDescription" => odbc_result($getVehicle,"categoryDescription")
);
$vehCat[$curr_id] = array("catID" => odbc_result($getVehicle,"catID"),
"category" => odbc_result($getVehicle,"category"),
"categoryDescription" => odbc_result($getVehicle,"categoryDescription")
} )

$tick = 0;
while(odbc_fetch_row($selectCat,$tick++))
{
$cat_id = odbc_result($selectCat,"catID");
$myCats[$cat_id] = array("category" => odbc_result($selectCat,"category"),
"description" => =odbc_result($selectCat,"categoryDescription");

}

<select name="vehicleID">
<? while(list($k,$v) = @each($getVehicleinfo)) { ?>
<option value="<?= $k ?>"> <?= $v["regNumberID"] ?>,
<?= $v["vehicleDescripID"] ?>,
<?= $v["grade"] ?>
<?=$v["vcoLname"] ?>

<? } ?>
</select>

<tr>
<td width="25%">Reg Number:</td>
<!--- the input field regNumber assumes you have only one record, if not I'm not sure where you'd get this into:
<td width="75%"><INPUT TYPE="text" NAME="regNumber" VALUE="<?= HTMLSpecialChars($v["regNumberID"] ?>"></td>
</tr>

<tr>
<td>Reg Category: </td>
<td>

<select name="catID">
<? while(list($k,$v) = @each($myCats)) { ?>
<? $sel = (in_array($k,$myCats) ? " SELECTED " : " ") ?>
<option <?= $sel ?> value="<?= $v["catID"] ?>"><?= $v["categoryDescription"] ?>

<? } ?>
</select>

amcgrath

ooops, forgot to get the cats 😉

Add this under the $query = ... line for categories....

$selectCat=odbc_exec($cx,$query);

amcgrath

Sorry, I didn't really run through it to close, so use me:
Add a closing parenthesis between the ) and ; in this line:

"description" => =odbc_result($selectCat,"categoryDescription");

Anon

Many thanks for all your help, it is greatly appreciated!!! Do you happen to know the syntax for using UPDATE with MS Access and PHP? Once again, many thanks!!!

amcgrath

$query = "update <some table> set <some field = $some_php_value, <some othre field> = $some_other_php_value where <some field again> = $some_field_again";

$msql_query($query);

Anon

Many thanks again, Allen for all your help. How can you pass the variable to another page after you have selected a new option?

amcgrath

Well, what I normally do is remove all of the options currently in the database and replace them with the newly chosen ones. In our earlier example, $catID only held one value. You can change it for multiple like so:

<select name="catID[]" multiple="yes" size="3">
<? while(list($k,$v) = @each($myCats)) { ?>
<? $sel = (in_array($k,$myCats) ? " SELECTED " : " ") ?>
<option <?= $sel ?> value="<?= $v["catID"] ?>"><?= $v["categoryDescription"] ?>

<? } ?>
</select>

Then $catID will be an array with the value of each index corresponding to a choice the user made.