[SOLVED]Two select queries help needed

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

[SOLVED]Two select queries help needed

Post 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
Last edited by melindaSA on Sat May 22, 2004 12:20 pm, edited 1 time in total.
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

-- Update --

Post 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!!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your echos of <select> are inside a do..while() loop.
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Post 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??
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Post 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!!
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Post by melindaSA »

Can anyone help me with this! :wink: Please.....
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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?
Last edited by McGruff on Tue Aug 09, 2005 12:17 am, edited 1 time in total.
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Post 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!
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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.
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Post 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!
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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);
?>
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Post 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">
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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.
melindaSA
Forum Commoner
Posts: 99
Joined: Thu Oct 02, 2003 7:34 am

Post 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 );
?>
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
Last edited by McGruff on Tue Aug 09, 2005 12:12 am, edited 1 time in total.
Post Reply