wantabe2 Posted February 3, 2010 Share Posted February 3, 2010 Hopefully this post will be more understanable than the last one... I've attached the code to this post & it all works like it is supposed to. The only change I'd like to make is in the second drop down box. I'd like for the information in the second drop down box to be only the employees of the offices chosen in the first drop down box. For instance, if I choose office 1 in drop down box 1, I'd like for only the employees of office 1 to be shown in drop down box 2. I have an ODBC connection set up on my workstation to the database. You can see the tables & columns in the code but I'll explain it better below: The first drop down box: office1, office2, and office3 are being pulled from the NAME column in the DIVISIONAL_OFFICE table. The second drop down box: the staff names are being pulled from the STAFF table. The STAFF table also has a LOC_CODE table that the staff are assigned to. For instance, employees in office1 are assigned a loc_code of of1 & employees from office2 are assigned to of2. If I need to explain more please let me know. I can't figure it out. [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
jammesz Posted February 3, 2010 Share Posted February 3, 2010 You will need to integrate a form or AJAX (preferred) to update the second drop down box with the relevant results Quote Link to comment Share on other sites More sharing options...
wantabe2 Posted February 3, 2010 Author Share Posted February 3, 2010 I'm new to PHP...can you provide a code example or a link with a code example? This is how I've made it so far by looking at code that does what I ned it to do & then editing it & learning as I go. Thanks Quote Link to comment Share on other sites More sharing options...
jammesz Posted February 3, 2010 Share Posted February 3, 2010 I have modified your given code to do what you ask via the form method. This will work assuming you have a office field in your staff table. I dont know what the office field name is so i've put it as 'officename', so you should change it to what ever the fields actual name is. <html> <body> <table width="100%" align="center"> <tr><td><form action="<?=$_SERVER['PHP_SELF']?>" method="POST"> <?php $conn=odbc_connect('dsn','un','pw'); if (!$conn) {exit("Connection Failed: " . $conn);} //we've failed the connection $sql="SELECT * FROM divisional_office WHERE name='office1' OR name='office2' OR name='office3' ORDER BY name"; //your query string $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");} // some SQL error echo "<select name='someoffice'>"; while (odbc_fetch_row($rs)) //iterates through each database row it's fetched { $officename=odbc_result($rs,"name"); //change to whatever you want, this will grab the results echo "<option value=\"".$officename."\">$officename</option>"; } echo "</select> <input type=\"submit\" name=\"submit_office\" value=\"Select\"></form>"; echo "</td><td>"; if(isset($_POST['submit_office'])){ $sql="SELECT * FROM staff WHERE officename='".$_POST['someoffice']."'"; //your query string $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");} // some SQL error echo "<select name='somename'>"; while (odbc_fetch_row($rs)) //iterates through each database row it's fetched { $compname=odbc_result($rs,"first_name"); //change to whatever you want, this will grab the results $conname=odbc_result($rs,"last_name"); echo "<option>$compname $conname</option>"; } odbc_close($conn); echo "</select>"; } echo "</td></tr></table>"; ?> </body> </html> hope this helps Quote Link to comment Share on other sites More sharing options...
wantabe2 Posted February 3, 2010 Author Share Posted February 3, 2010 Thanks sooo much!! The STAFF table does not have an officename field name. The fields/columns in the STAFF table are below: LOC_CODE - this column name represents where the employee belongs to (the loc_codes are CH, KN, and GR) LAST_NAME - this is the employee last name FIRST_NAME - this is the employee last name I replaced the officename in the code with LOC_CODE & when I run the php file from the browser I get the selct button near the first drop down box & when I choose it I get redirected to a page that says page cannot be displayed, the url reads http://myserver/%3C?=$_SERVER['PHP_SELF']?> Should I use something else instead of officename or loc_code? Quote Link to comment Share on other sites More sharing options...
jammesz Posted February 4, 2010 Share Posted February 4, 2010 This should work: <html> <body> <table width="100%" align="center"> <tr><td><form action="<?=$_SERVER['PHP_SELF']?>" method="POST"> <?php $conn=odbc_connect('dsn','un','pw'); if (!$conn) {exit("Connection Failed: " . $conn);} //we've failed the connection $sql="SELECT * FROM divisional_office WHERE name='office1' OR name='office2' OR name='office3' ORDER BY name"; //your query string $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");} // some SQL error echo "<select name='someoffice'>"; while (odbc_fetch_row($rs)) //iterates through each database row it's fetched { $officename=odbc_result($rs,"name"); //change to whatever you want, this will grab the results $LOC_CODE=odbc_result($rs,"LOC_CODE"); echo "<option value=\"".$LOC_CODE."\">$officename</option>"; } echo "</select> <input type=\"submit\" name=\"submit_office\" value=\"Select\"></form>"; echo "</td><td>"; if(isset($_POST['submit_office'])){ $sql="SELECT * FROM staff WHERE LOC_CODE='".$_POST['someoffice']."'"; //your query string $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");} // some SQL error echo "<select name='somename'>"; while (odbc_fetch_row($rs)) //iterates through each database row it's fetched { $compname=odbc_result($rs,"first_name"); //change to whatever you want, this will grab the results $conname=odbc_result($rs,"last_name"); echo "<option>$compname $conname</option>"; } odbc_close($conn); echo "</select>"; } echo "</td></tr></table>"; ?> </body> </html> This is assuming that LOC_CODE is in both tables. If this doesnt work than ill need to know how the two tables are linked. Quote Link to comment Share on other sites More sharing options...
jammesz Posted February 4, 2010 Share Posted February 4, 2010 You should edit <form action="<?=$_SERVER['PHP_SELF']?>" method="POST"> to <form action="Whatever.php" method="POST"> Where Whatever.php is the current php page the dropdown boxes are in. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.