Page 1 of 2
[SOLVED]Two select queries help needed
Posted: Thu May 13, 2004 11:39 am
by melindaSA
I am building a Human Resources online application and open positions application.
I have 3 tables:
departments - depID, departments, dep_number
positions - posID, depID, title, experience, hours, description, publishdate
application - appID, first_name, middle_name, last_name, address, city, state, zip, position_apply, position_type, ...etc
I am using a dropdown to pull the open positions into an application form and when submitted
putting the position_apply (position.title) into the application table:
Code: Select all
<?php
include ('app_inc_fns.php');
$conn = db_connect();
echo "<select name='position_apply'>"; //Start selection box
$query = mysql_query("select * from positions");
while($rows = @mysql_fetch_array($query)) {
echo "<option value="$rows[title]">$rows[title]</option>";
}
echo "</select>";
?>
So far this works great!
What I would like to do, is also pull the dep_number via a hidden field in the application form
and submit to the application table (position_type).
Code: Select all
<?php
"<input type="hidden" name="position_type" value=$dep_number>";
?>
What is the best way for me to do this?? I am stuck!!
Help would be appreciated...
Thanks,
--Melinda
-- Update --
Posted: Thu May 13, 2004 9:09 pm
by melindaSA
I have got this working half-way! Here is what I did:
Code: Select all
<?php
<?php
include ('app_inc_fns.php');
$conn = db_connect();
$query = "SELECT * from positions";
$result = mysql_query( $query );
if ( $row = mysql_fetch_assoc( $result ) ) {
do {
extract( $row );
$subquery = "SELECT * FROM positions";
$subresult = mysql_query( $subquery );
if ( $subrow = mysql_fetch_assoc( $subresult ) ) {
echo "<select name="position_apply">"; { //Start selection box
echo "<option value="$row[title]">$row[title]</option>";
}
echo "</select>";
do {
extract( $subrow );
echo '<input type="hidden" name="position_type" value="'.$subrow['depID'].'">';
}
while( $subrow = mysql_fetch_assoc( $subresult ) );
}
}
while( $row = mysql_fetch_assoc( $result ) );
}
?>
?>
The problem is that the dropdown will not work right, it puts each position_apply into a seperate dropdown. I does add the position_apply and the correct position_type into the mysql database.
Can anyone see what I am doing wrong??
Help!!
Posted: Thu May 13, 2004 11:13 pm
by feyd
your echos of <select> are inside a do..while() loop.
Posted: Fri May 14, 2004 7:43 am
by melindaSA
Thank you feyd!!!
Is this what you mean:
Code: Select all
<?php
<?php
include ('app_inc_fns.php');
$conn = db_connect();
$query = "SELECT * from positions";
$result = mysql_query( $query );
if ( $row = mysql_fetch_assoc( $result ) ) {
do {
extract( $row );
$subquery = "SELECT * FROM positions";
$subresult = mysql_query( $subquery );
if ( $subrow = mysql_fetch_assoc( $subresult ) ) {
do {
extract( $subrow );
echo '<input type="hidden" name="position_type" value="'.$subrow['depID'].'">';
}
while( $subrow = mysql_fetch_assoc( $subresult ) );
}
}
while( $row = mysql_fetch_assoc( $result ) );
echo "<select name="position_apply">"; { //Start selection box
echo "<option value="$row[title]">$row[title]</option>";
}
echo "</select>";}
?>
?>
It is not duplicating the dropdowns, but it now does not pull any records into the dropdown. Have I placed the echos in the wrong place?
Sorry if this is stupid question??
Posted: Fri May 14, 2004 9:51 am
by melindaSA
OK, so I have this now, the dropdown works (and populates the database), but I cannot get the hidden field to populate the database.
Code: Select all
<?php
<?php
include ('app_inc_fns.php');
$conn = db_connect();
echo '<select name="position_apply">'; //Start selection box
$query = 'SELECT * from positions';
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
echo '<option value="'.$row[title].'">'.$row[title].'</option>'; }
echo '</select>'; //end selection box
{
echo '<input type="hidden" name="position_type" value="'.$row['depID'].'">';}
mysql_free_result($result);
?>
?>
Please, could someone help me with this....I have been looking at it for so long....
Thank You!!
Posted: Mon May 17, 2004 6:30 am
by melindaSA
Can anyone help me with this!

Please.....
Posted: Mon May 17, 2004 8:33 am
by McGruff
First, check what's arriving at the form processor script. What do you see when you:
(a) look at the source code of the form page (ie is all the dynamic content being echo'd out as you expect?)
(b) with:
Code: Select all
<?php
echo '<pre>';
print_r($_POST);
echo '</pre>';
?>
..in the form processor script?
Posted: Mon May 17, 2004 9:17 am
by melindaSA
Thank you McGruff!
The source of my form looks correct, here it is:
Code: Select all
<select name="position_apply"><option value="R.N. ">R.N. </option><option value="Director of 4th Floor/CCU">Director of 4th Floor/CCU</option><option value="Cardiopulmonary Tech I">Cardiopulmonary Tech I</option><option value="Rad Tech">Rad Tech</option><option value="Patient Registration Clerk">Patient Registration Clerk</option><option value="LPN">LPN</option><option value="LPN">LPN</option><option value="R.N.">R.N.</option><option value="Cardiopulmonary Tech I">Cardiopulmonary Tech I</option><option value="Physical Therapist">Physical Therapist</option><option value="Rehab Aide/Athletic Trainer">Rehab Aide/Athletic Trainer</option><option value="Aquatics Instructor">Aquatics Instructor</option><option value="C.N.A. II">C.N.A. II</option><option value="Speech/Language Pathologist">Speech/Language Pathologist</option><option value="Patient Account Support Clerk">Patient Account Support Clerk</option><option value="Operator">Operator</option><option value="Dietary Aide I">Dietary Aide I</option><option value="Registrar">Registrar</option><option value="Director of Human Resources">Director of Human Resources</option><option value="R.N.">R.N.</option><option value="Oncology Coordinator">Oncology Coordinator</option></select><input type="hidden" name="position_type" value="">
And the form processing script, outputs the following:
Code: Select all
Array
(
їposition_apply] => Patient Registration Clerk
їposition_type] =>
їdate_avail] =>
їlast_name] => test name
їfirst_name] => test first
їmiddle_name] =>
їaddress] =>
їaddress2] =>
їcity] =>
їstate] =>
їzip] =>
їh_telephone] =>
їb_telephone] =>
їssn] =>
їcontact_work] => yes
їmonday_from] =>
їmonday_to] =>
їtuesday_from] =>
їtuesday_to] =>
їwednesday_from] =>
їwednesday_to] =>
їthursday_from] =>
їthursday_to] =>
їfriday_from] =>
їfriday_to] =>
їsaturday_from] =>
їsaturday_to] =>
їsunday_from] =>
їsunday_to] =>
їwhat_name] =>
їdate_birth] =>
їreferred] =>
їemergency_name] =>
їemergency_phone] =>
їm_branch] =>
їm_special] =>
їrank] =>
їhs_name] =>
їhs_address] =>
їhs_city] =>
їhs_state] =>
їhs_zip] =>
їhs_degree] =>
їhs_years] =>
їcollege_name] =>
їcollege_address] =>
їcollege_city] =>
їcollege_state] =>
їcollege_zip] =>
їcollege_degree] =>
їcollege_years] =>
їgradschool_name] =>
їgradschool_address] =>
їgradschool_city] =>
їgradschool_state] =>
їgradschool_zip] =>
їgradschool_degree] =>
їgradschool_years] =>
їtechschool_name] =>
їtechschool_address] =>
їtechschool_city] =>
їtechschool_state] =>
їtechschool_zip] =>
їtechschool_degree] =>
їtechschool_years] =>
їtyping_speed] =>
їshorthand_speed] =>
їcrt_speed] =>
їlanguage_type] =>
їaffiliations] =>
їlicence1_state] =>
їlicence1_reg] =>
їlicence1_date] =>
їlicence1_specialty] =>
їlicence2_state] =>
їlicence2_reg] =>
їlicence2_date] =>
їlicence2_specialty] =>
їdischarge_company1] =>
їdischarge_reason1] =>
їdischarge_company2] =>
їdischarge_reason2] =>
їcontact_pemploy] => yes
їcontact_oemploy] => yes
їposition1_start] =>
їposition1_end] =>
їposition1_name] =>
їposition1_address] =>
їposition1_city] =>
їposition1_state] =>
їposition1_zip] =>
їposition1_phone] =>
їposition1_job] =>
їposition1_supervisor] =>
їposition1_responsbties] =>
їposition1_ssalary] =>
їposition1_fsalary] =>
їposition1_reason] =>
їposition1_difname] =>
їposition2_start] =>
їposition2_end] =>
їposition2_name] =>
їposition2_address] =>
їposition2_city] =>
їposition2_state] =>
їposition2_zip] =>
їposition2_phone] =>
їposition2_job] =>
їposition2_supervisor] =>
їposition2_responsbties] =>
їposition2_ssalary] =>
їposition2_fsalary] =>
їposition2_reason] =>
їposition2_difname] =>
їposition3_start] =>
їposition3_end] =>
їposition3_name] =>
їposition3_address] =>
їposition3_city] =>
їposition3_state] =>
їposition3_zip] =>
їposition3_phone] =>
їposition3_job] =>
їposition3_supervisor] =>
їposition3_responsbties] =>
їposition3_ssalary] =>
їposition3_fsalary] =>
їposition3_reason] =>
їposition3_difname] =>
їposition4_start] =>
їposition4_end] =>
їposition4_name] =>
їposition4_address] =>
їposition4_city] =>
їposition4_state] =>
їposition4_zip] =>
їposition4_phone] =>
їposition4_job] =>
їposition4_supervisor] =>
їposition4_responsbties] =>
їposition4_ssalary] =>
їposition4_fsalary] =>
їposition4_reason] =>
їposition4_difname] =>
їother_name] =>
їother_address] =>
їother_city] =>
їother_state] =>
їother_zip] =>
їother_phone] =>
їother_supervisor] =>
їother_from] =>
їother_to] =>
їref1_name] =>
їref1_address] =>
їref1_city] =>
їref1_state] =>
їref1_zip] =>
їref1_telephone] =>
їref1_years] =>
їref1_difname] => no
їref2_name] =>
їref2_address] =>
їref2_city] =>
їref2_state] =>
їref2_zip] =>
їref2_telephone] =>
їref2_years] =>
їref2_difname] => no
їref3_name] =>
їref3_address] =>
їref3_city] =>
їref3_state] =>
їref3_zip] =>
їref3_telephone] =>
їref3_years] =>
їref3_difname] => no1
їother_information] =>
їdate] => 2004.05.17
їB1] => Submit
)
As you can see, it is not adding the position_type, which is the hidden input field.
I am not sure why this is not working??
Am I calling the hidden input field incorrectly??
Thank you for looking at this for me!
Posted: Mon May 17, 2004 9:49 am
by Wayne
there is no value in the hidden field, is there a value in this field of your database for all jobs? especially check the database details for the last job in your SELECT list.
Posted: Mon May 17, 2004 10:03 am
by melindaSA
Yes, there is a depID for every job in the positions table in the database.
This why, I think that I have an error in my code calling the hidden field!
Posted: Mon May 17, 2004 10:25 am
by Wayne
you have some { in funny places and some other things but nothing major .... try this
Code: Select all
<?php
include ('app_inc_fns.php');
$conn = db_connect();
echo '<select name="position_apply">'; //Start selection box
$query = 'SELECT * from positions';
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
echo '<option value="'.$row[title].'">'.$row[title].'</option>';
$DepID = $row['depID'];
}
echo '</select>'; //end selection box
echo '<input type="hidden" name="position_type" value="' . $DepID . '">';
mysql_free_result($result);
?>
Posted: Mon May 17, 2004 10:49 am
by melindaSA
This is almost working!
It is adding the depID to the database, but the incorrect one....It is adding the last position depID, and not the one selected:
Code: Select all
Array
(
їposition_apply] => Patient Registration Clerk
їposition_type] => 29
їdate_avail] => 10/10/2004
їlast_name] => test
їfirst_name] => test
їmiddle_name] => T
їaddress] => test Road
їaddress2] =>
їcity] => test
їstate] => NC
їzip] => 12345
їh_telephone] => 123-123-2222
їb_telephone] => 123-123-1235
їssn] => 222-55-5555
їcontact_work] => yes
29 is the depID for Oncology Coordinator
Code: Select all
<select name="position_apply"><option value="R.N. ">R.N. </option><option value="Director of 4th Floor/CCU">Director of 4th Floor/CCU</option><option value="Cardiopulmonary Tech I">Cardiopulmonary Tech I</option><option value="Rad Tech">Rad Tech</option><option value="Patient Registration Clerk">Patient Registration Clerk</option><option value="LPN">LPN</option><option value="LPN">LPN</option><option value="R.N.">R.N.</option><option value="Cardiopulmonary Tech I">Cardiopulmonary Tech I</option><option value="Physical Therapist">Physical Therapist</option><option value="Rehab Aide/Athletic Trainer">Rehab Aide/Athletic Trainer</option><option value="Aquatics Instructor">Aquatics Instructor</option><option value="C.N.A. II">C.N.A. II</option><option value="Speech/Language Pathologist">Speech/Language Pathologist</option><option value="Patient Account Support Clerk">Patient Account Support Clerk</option><option value="Operator">Operator</option><option value="Dietary Aide I">Dietary Aide I</option><option value="Registrar">Registrar</option><option value="Director of Human Resources">Director of Human Resources</option><option value="R.N.">R.N.</option><option value="Oncology Coordinator">Oncology Coordinator</option></select><input type="hidden" name="position_type" value="29">
Posted: Mon May 17, 2004 10:56 am
by Wayne
its adding the the last depID in the SELECT list, if you want it to add the depID of the selected Job then you will have to code it slightly differently, as this way is dependant on all the jobs being for the same department.
there are lots of ways you could do it, but you will have to decide on the best one.
Posted: Mon May 17, 2004 11:03 am
by melindaSA
Thank you Wayne!
Could I do this by using a subquery, subresult??
Code: Select all
<?php
$query = "SELECT * from positions";
$result = mysql_query( $query ) or die (mysql_errno() . ": " . mysql_error(). "\n");
if ( $row = mysql_fetch_assoc( $result ) ) {
do {
extract( $row );
?>
Code: Select all
<?php
$subquery = "SELECT * FROM positions";
$subresult = mysql_query( $subquery ) or die (mysql_errno() . ": " . mysql_error(). "\n");
if ( $subrow = mysql_fetch_assoc( $subresult ) )
{
do {
extract( $subrow );
?>
Posted: Mon May 17, 2004 11:17 am
by McGruff
I just looked at this quickly so I might have got it wrong, but am I correct in thinking that each job title has an assoicated $DepID?
If so:
Code: Select all
while($row = mysql_fetch_assoc($result)) {
echo '<option value="' . $row[title] . '|' . $row['depID'] . '">' . $row[title] . '</option>';
echo "/n"; // add a new line here, for example
}
The submitted value contains title & DepID: explode() to separate.
PS: if you echo some "\n" (newline - must be double quotes) in the html the source is a lot easier to read.