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! :wink: 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
(
    &#1111;position_apply] => Patient Registration Clerk
    &#1111;position_type] => 
    &#1111;date_avail] => 
    &#1111;last_name] => test name
    &#1111;first_name] => test first
    &#1111;middle_name] => 
    &#1111;address] => 
    &#1111;address2] => 
    &#1111;city] => 
    &#1111;state] => 
    &#1111;zip] => 
    &#1111;h_telephone] => 
    &#1111;b_telephone] => 
    &#1111;ssn] => 
    &#1111;contact_work] => yes
    &#1111;monday_from] => 
    &#1111;monday_to] => 
    &#1111;tuesday_from] => 
    &#1111;tuesday_to] => 
    &#1111;wednesday_from] => 
    &#1111;wednesday_to] => 
    &#1111;thursday_from] => 
    &#1111;thursday_to] => 
    &#1111;friday_from] => 
    &#1111;friday_to] => 
    &#1111;saturday_from] => 
    &#1111;saturday_to] => 
    &#1111;sunday_from] => 
    &#1111;sunday_to] => 
    &#1111;what_name] => 
    &#1111;date_birth] => 
    &#1111;referred] => 
    &#1111;emergency_name] => 
    &#1111;emergency_phone] => 
    &#1111;m_branch] => 
    &#1111;m_special] => 
    &#1111;rank] => 
    &#1111;hs_name] => 
    &#1111;hs_address] => 
    &#1111;hs_city] => 
    &#1111;hs_state] => 
    &#1111;hs_zip] => 
    &#1111;hs_degree] => 
    &#1111;hs_years] => 
    &#1111;college_name] => 
    &#1111;college_address] => 
    &#1111;college_city] => 
    &#1111;college_state] => 
    &#1111;college_zip] => 
    &#1111;college_degree] => 
    &#1111;college_years] => 
    &#1111;gradschool_name] => 
    &#1111;gradschool_address] => 
    &#1111;gradschool_city] => 
    &#1111;gradschool_state] => 
    &#1111;gradschool_zip] => 
    &#1111;gradschool_degree] => 
    &#1111;gradschool_years] => 
    &#1111;techschool_name] => 
    &#1111;techschool_address] => 
    &#1111;techschool_city] => 
    &#1111;techschool_state] => 
    &#1111;techschool_zip] => 
    &#1111;techschool_degree] => 
    &#1111;techschool_years] => 
    &#1111;typing_speed] => 
    &#1111;shorthand_speed] => 
    &#1111;crt_speed] => 
    &#1111;language_type] => 
    &#1111;affiliations] => 
    &#1111;licence1_state] => 
    &#1111;licence1_reg] => 
    &#1111;licence1_date] => 
    &#1111;licence1_specialty] => 
    &#1111;licence2_state] => 
    &#1111;licence2_reg] => 
    &#1111;licence2_date] => 
    &#1111;licence2_specialty] => 
    &#1111;discharge_company1] => 
    &#1111;discharge_reason1] => 
    &#1111;discharge_company2] => 
    &#1111;discharge_reason2] => 
    &#1111;contact_pemploy] => yes
    &#1111;contact_oemploy] => yes
    &#1111;position1_start] => 
    &#1111;position1_end] => 
    &#1111;position1_name] => 
    &#1111;position1_address] => 
    &#1111;position1_city] => 
    &#1111;position1_state] => 
    &#1111;position1_zip] => 
    &#1111;position1_phone] => 
    &#1111;position1_job] => 
    &#1111;position1_supervisor] => 
    &#1111;position1_responsbties] => 
    &#1111;position1_ssalary] => 
    &#1111;position1_fsalary] => 
    &#1111;position1_reason] => 
    &#1111;position1_difname] => 
    &#1111;position2_start] => 
    &#1111;position2_end] => 
    &#1111;position2_name] => 
    &#1111;position2_address] => 
    &#1111;position2_city] => 
    &#1111;position2_state] => 
    &#1111;position2_zip] => 
    &#1111;position2_phone] => 
    &#1111;position2_job] => 
    &#1111;position2_supervisor] => 
    &#1111;position2_responsbties] => 
    &#1111;position2_ssalary] => 
    &#1111;position2_fsalary] => 
    &#1111;position2_reason] => 
    &#1111;position2_difname] => 
    &#1111;position3_start] => 
    &#1111;position3_end] => 
    &#1111;position3_name] => 
    &#1111;position3_address] => 
    &#1111;position3_city] => 
    &#1111;position3_state] => 
    &#1111;position3_zip] => 
    &#1111;position3_phone] => 
    &#1111;position3_job] => 
    &#1111;position3_supervisor] => 
    &#1111;position3_responsbties] => 
    &#1111;position3_ssalary] => 
    &#1111;position3_fsalary] => 
    &#1111;position3_reason] => 
    &#1111;position3_difname] => 
    &#1111;position4_start] => 
    &#1111;position4_end] => 
    &#1111;position4_name] => 
    &#1111;position4_address] => 
    &#1111;position4_city] => 
    &#1111;position4_state] => 
    &#1111;position4_zip] => 
    &#1111;position4_phone] => 
    &#1111;position4_job] => 
    &#1111;position4_supervisor] => 
    &#1111;position4_responsbties] => 
    &#1111;position4_ssalary] => 
    &#1111;position4_fsalary] => 
    &#1111;position4_reason] => 
    &#1111;position4_difname] => 
    &#1111;other_name] => 
    &#1111;other_address] => 
    &#1111;other_city] => 
    &#1111;other_state] => 
    &#1111;other_zip] => 
    &#1111;other_phone] => 
    &#1111;other_supervisor] => 
    &#1111;other_from] => 
    &#1111;other_to] => 
    &#1111;ref1_name] => 
    &#1111;ref1_address] => 
    &#1111;ref1_city] => 
    &#1111;ref1_state] => 
    &#1111;ref1_zip] => 
    &#1111;ref1_telephone] => 
    &#1111;ref1_years] => 
    &#1111;ref1_difname] => no
    &#1111;ref2_name] => 
    &#1111;ref2_address] => 
    &#1111;ref2_city] => 
    &#1111;ref2_state] => 
    &#1111;ref2_zip] => 
    &#1111;ref2_telephone] => 
    &#1111;ref2_years] => 
    &#1111;ref2_difname] => no
    &#1111;ref3_name] => 
    &#1111;ref3_address] => 
    &#1111;ref3_city] => 
    &#1111;ref3_state] => 
    &#1111;ref3_zip] => 
    &#1111;ref3_telephone] => 
    &#1111;ref3_years] => 
    &#1111;ref3_difname] => no1
    &#1111;other_information] => 
    &#1111;date] => 2004.05.17
    &#1111;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! :D

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
(
    &#1111;position_apply] => Patient Registration Clerk
    &#1111;position_type] => 29
    &#1111;date_avail] => 10/10/2004
    &#1111;last_name] => test
    &#1111;first_name] => test
    &#1111;middle_name] => T
    &#1111;address] => test Road
    &#1111;address2] => 
    &#1111;city] => test
    &#1111;state] => NC
    &#1111;zip] => 12345
    &#1111;h_telephone] => 123-123-2222
    &#1111;b_telephone] => 123-123-1235
    &#1111;ssn] => 222-55-5555
    &#1111;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.