JPtheGeezer
asked on
Chained Combo Box in a PHP form linking to SQL 2005 Table using jQuery/Ajax/etc
Hi,
I'm hoping an "Expert in the Exchange" can help with what I hope is a minor mod to a PHP form that I am currently developing.
Basically the form I'm working on uses combo boxes to select data from a SQL 2005 table and then issue a delete to remove the row from the table and this works perfectly on the form.
But in order for the user to select the correct row and ensure that they cannot make a mistake I would like my combo boxes to chain, for example:
FIELD1 combo box is selected based on SQL DB table query;
FIELD 2 combo box shows fields that are linked to FIELD 1;
FIELD 3 combo box shows fields that are linked to FIELD 2;
FIELD 4 combo box shows fields that are linked to FIELD 3;
So what happens is the user can see EXACTLY the row they require deleted and will know that they are making the absolute correct selection.
The problem I have is that most examples on the web show MY_SQL and not MSSQL, and since I have already working combo boxes I don't think I am too far away from getting this going.
I also believe that this will not be a "PHP" function but rather a jQuery or AJAX function which shouldn't be a problem because this is already being utilised with this site and different pages.
OK so after my long winded summary here is the PHP code for the form:
==========================
<?php
define( "HOSTNAME", "svwwap09" ); // db host
define( "DBNAME", "QADDMStaging" ); // db name
define( "USERNAME", "sa" ); // db user name
define( "USERPW", "dummypassword" ); // db user password
define( "SCENARIO_ID", 559 ); // PharmaCare Scenario ID
$hostname = HOSTNAME; //host
$dbname = DBNAME; //db name
$username = USERNAME; //username like 'sa'
$pw = USERPW; //password for the user
$db = new PDO ( "sqlsrv:server=$hostname;d
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$db->setAttribute( PDO::ATTR_CASE, PDO::CASE_LOWER );
Function ComboBox( $strName, $result, $MultiSize=1 ) {
if ($MultiSize > 1 ) {
$selectBox = "<select id='$strName' name='".$strName."[]' id='".$strName."' size=$MultiSize multiple>\n";
} else {
$selectBox = "<select id='$strName' name='".$strName."' id='".$strName."'>\n";
}
$selectBox .= "<option value=-9 selected> </option>";
foreach ($result as $data) {
if (strlen($data->item) > 0)
$selectBox .= "<option value=\"" . $data->item. "\">" . $data->item . "</option> \n";
}
$selectBox .= "</select>\n";
echo $selectBox;
}
?>
<html>
<head>
<title>REMOVE Exclusions Screen</title>
</head>
<body>
<br>
Please Select SKU along with Channel, Country and Customer Group from the selections below to REMOVE from the Exclusions List:
<br></br>
<form name="myform" action="/lib/RemoveExclusi
<input type="hidden" name="check_submit" value="1" />
<table width="600" border="2" height=80>
<tr>
<th width="91"> <div align="center"><b>SKU</b> </div></th>
<th width="91"> <div align="center"><b>Channel<
<th width="91"> <div align="center"><b>Country<
<th width="91"> <div align="center"><b>Customer
</tr>
<tr>
<td><div align="center">
<?php
$sql = "SELECT DISTINCT sku as item from PAC_ForecastExclusions ORDER BY sku";
$sth = $db->query( $sql );
$result = $sth->fetchAll( PDO::FETCH_OBJ );
ComboBox( "sku", $result, 1 );
?></div></td>
<td><div align="center"><?php
$sql = "SELECT DISTINCT channel as item from PAC_ForecastExclusions ORDER BY channel";
$sth = $db->query( $sql );
$result = $sth->fetchAll( PDO::FETCH_OBJ );
ComboBox( "channel", $result, 1 );
?></div></td>
<td><div align="center"><?php
$sql = "SELECT DISTINCT country as item from PAC_ForecastExclusions ORDER BY country";
$sth = $db->query( $sql );
$result = $sth->fetchAll( PDO::FETCH_OBJ );
ComboBox( "country", $result, 1);
?></div></td>
<td><div align="center"><?php
$sql = "SELECT DISTINCT customergroup as item from PAC_ForecastExclusions ORDER BY customergroup";
$sth = $db->query( $sql );
$result = $sth->fetchAll( PDO::FETCH_OBJ );
ComboBox( "customergroup", $result, 1 );
?></div></td>
</tr>
</table>
<br>
Once you have made your selection please press the SUBMIT button below to complete the REMOVAL of the SKU from the Exclusions list.
<br>
<br>
<b>PLEASE NOTE THAT PRESSING THE SUBMIT BUTTON REMOVES THE SKU FROM THE EXCLUSIONS LIST - MAKE SURE YOU ARE MAKING THE CORRECT SELECTION!!!</b>
<br>
<br>
<input type="submit" name="submit" value="Submit" />
</form>
</body>
</head>
</html>
==========================
Thanks guys, I appreciate any help you can offer and I am willing to share any other code that you may require.
I'm hoping an "Expert in the Exchange" can help with what I hope is a minor mod to a PHP form that I am currently developing.
Basically the form I'm working on uses combo boxes to select data from a SQL 2005 table and then issue a delete to remove the row from the table and this works perfectly on the form.
But in order for the user to select the correct row and ensure that they cannot make a mistake I would like my combo boxes to chain, for example:
FIELD1 combo box is selected based on SQL DB table query;
FIELD 2 combo box shows fields that are linked to FIELD 1;
FIELD 3 combo box shows fields that are linked to FIELD 2;
FIELD 4 combo box shows fields that are linked to FIELD 3;
So what happens is the user can see EXACTLY the row they require deleted and will know that they are making the absolute correct selection.
The problem I have is that most examples on the web show MY_SQL and not MSSQL, and since I have already working combo boxes I don't think I am too far away from getting this going.
I also believe that this will not be a "PHP" function but rather a jQuery or AJAX function which shouldn't be a problem because this is already being utilised with this site and different pages.
OK so after my long winded summary here is the PHP code for the form:
==========================
<?php
define( "HOSTNAME", "svwwap09" ); // db host
define( "DBNAME", "QADDMStaging" ); // db name
define( "USERNAME", "sa" ); // db user name
define( "USERPW", "dummypassword" ); // db user password
define( "SCENARIO_ID", 559 ); // PharmaCare Scenario ID
$hostname = HOSTNAME; //host
$dbname = DBNAME; //db name
$username = USERNAME; //username like 'sa'
$pw = USERPW; //password for the user
$db = new PDO ( "sqlsrv:server=$hostname;d
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$db->setAttribute( PDO::ATTR_CASE, PDO::CASE_LOWER );
Function ComboBox( $strName, $result, $MultiSize=1 ) {
if ($MultiSize > 1 ) {
$selectBox = "<select id='$strName' name='".$strName."[]' id='".$strName."' size=$MultiSize multiple>\n";
} else {
$selectBox = "<select id='$strName' name='".$strName."' id='".$strName."'>\n";
}
$selectBox .= "<option value=-9 selected> </option>";
foreach ($result as $data) {
if (strlen($data->item) > 0)
$selectBox .= "<option value=\"" . $data->item. "\">" . $data->item . "</option> \n";
}
$selectBox .= "</select>\n";
echo $selectBox;
}
?>
<html>
<head>
<title>REMOVE Exclusions Screen</title>
</head>
<body>
<br>
Please Select SKU along with Channel, Country and Customer Group from the selections below to REMOVE from the Exclusions List:
<br></br>
<form name="myform" action="/lib/RemoveExclusi
<input type="hidden" name="check_submit" value="1" />
<table width="600" border="2" height=80>
<tr>
<th width="91"> <div align="center"><b>SKU</b> </div></th>
<th width="91"> <div align="center"><b>Channel<
<th width="91"> <div align="center"><b>Country<
<th width="91"> <div align="center"><b>Customer
</tr>
<tr>
<td><div align="center">
<?php
$sql = "SELECT DISTINCT sku as item from PAC_ForecastExclusions ORDER BY sku";
$sth = $db->query( $sql );
$result = $sth->fetchAll( PDO::FETCH_OBJ );
ComboBox( "sku", $result, 1 );
?></div></td>
<td><div align="center"><?php
$sql = "SELECT DISTINCT channel as item from PAC_ForecastExclusions ORDER BY channel";
$sth = $db->query( $sql );
$result = $sth->fetchAll( PDO::FETCH_OBJ );
ComboBox( "channel", $result, 1 );
?></div></td>
<td><div align="center"><?php
$sql = "SELECT DISTINCT country as item from PAC_ForecastExclusions ORDER BY country";
$sth = $db->query( $sql );
$result = $sth->fetchAll( PDO::FETCH_OBJ );
ComboBox( "country", $result, 1);
?></div></td>
<td><div align="center"><?php
$sql = "SELECT DISTINCT customergroup as item from PAC_ForecastExclusions ORDER BY customergroup";
$sth = $db->query( $sql );
$result = $sth->fetchAll( PDO::FETCH_OBJ );
ComboBox( "customergroup", $result, 1 );
?></div></td>
</tr>
</table>
<br>
Once you have made your selection please press the SUBMIT button below to complete the REMOVAL of the SKU from the Exclusions list.
<br>
<br>
<b>PLEASE NOTE THAT PRESSING THE SUBMIT BUTTON REMOVES THE SKU FROM THE EXCLUSIONS LIST - MAKE SURE YOU ARE MAKING THE CORRECT SELECTION!!!</b>
<br>
<br>
<input type="submit" name="submit" value="Submit" />
</form>
</body>
</head>
</html>
==========================
Thanks guys, I appreciate any help you can offer and I am willing to share any other code that you may require.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
could you give link to se your page in live?+
ASKER
Sorry, it's an intranet site (does anybody use the word intranet anymore???) that's only available to users inside our domain.
Quick question, is the source of the main page a HTML file or a PHP file?
Quick question, is the source of the main page a HTML file or a PHP file?
both are php file because your php inside :)
ASKER
I think the issue could be that the following line cannot reach the "ajax.php" file:
$.post("cascade dropdown mssql ajax.php", { dropdownid:1, v:$(this).val() }, function(data) { $("#divdd1").html(data); });
These files are not in the root of the website, they are in a sub folder inside the root, would I need to specify /lib/ in front of the file, like this?:
$.post("cascade dropdown mssql /lib/ajax.php", { dropdownid:1, v:$(this).val() }, function(data) { $("#divdd1").html(data); });
$.post("cascade dropdown mssql ajax.php", { dropdownid:1, v:$(this).val() }, function(data) { $("#divdd1").html(data); });
These files are not in the root of the website, they are in a sub folder inside the root, would I need to specify /lib/ in front of the file, like this?:
$.post("cascade dropdown mssql /lib/ajax.php", { dropdownid:1, v:$(this).val() }, function(data) { $("#divdd1").html(data); });
yes it should be in the same path and if you decide to change the name, update it everywhere
ASKER
Hi All,
Thank you for your time and efforts, I was able to get around this by being a little lazy and issuing a count based on the fields selected in the combo boxes and if the count came to zero a message box would appear telling them the record did not exist. And if the combo selection existed based on the query it would remove it from the DB.
So I'm happy to close this call but I will split the points between you two for posting replies.
Thank you for your time and efforts, I was able to get around this by being a little lazy and issuing a count based on the fields selected in the combo boxes and if the count came to zero a message box would appear telling them the record did not exist. And if the combo selection existed based on the query it would remove it from the DB.
So I'm happy to close this call but I will split the points between you two for posting replies.
ASKER
Thank you for the time and effort in writing new code for me, I have copied the new code and overwritten my current code, I also created a new PHP file called "ajax.php" and placed the contents of the script into it.
I have tested the new code and no dropdowns appear, I noticed there were a couple of trailing quote marks in the script code for each dropdown that look like they weren't supposed to be there but that didn't change the result.