Page 1 of 1

subquery problem -- I think

Posted: Thu Dec 11, 2003 2:56 pm
by melindaSA
I am trying to pull Open Positions from a mySQL database, I have half the code working, but I need to add another query.

Here is the part that works you can see results at http://www.bjrh.org/HRjobs/results.php:

Code: Select all

<?php

require_once('positions_inc_fns.php');
session_start();


$db_host  = '******';
$db_user  = '*****';
$db_pass  = '*****';
$db_name  = '*****';
$db_table = 'positions' . 'category' . 'departments';
$conn = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name,$conn);

// ---- END CONFIGURATIONS ------------------------------------------//

$query = "SELECT * FROM category ORDER BY catID";
$result = mysql_query( $query );
if ( $row = mysql_fetch_assoc( $result ) ) {
    do {
        extract( $row );
        $subquery = "SELECT * FROM positions WHERE catID=$catID ORDER BY title";
        $subresult = mysql_query( $subquery );
        if ( $subrow = mysql_fetch_assoc( $subresult ) ) {
            echo "<img src="../images/bullet.gif"><font face="Verdana" size="2">" .$category."</font><br>";

            do {
                extract( $subrow );
                echo "<font face="Verdana" size="1"><a href="show_positions.php?posID=$posID">$title - $depID</a></font><br>";

             }
            while( $subrow = mysql_fetch_assoc( $subresult ) );
        }
    }
    while( $row = mysql_fetch_assoc( $result ) );
}


else {
    echo "No category available";
}


?>
What I need to do is replace the depID with the department name from the departments table.

I have used the following on another page, which works fine, but I am not sure where and how to call this function in the code above:

Code: Select all

<?php
$dep_array=get_departments();
             foreach ($dep_array as $thisdepartments)
             {
              if ($thisdepartments['depID'] == $positions['depID'])
              echo '<I>' . $thisdepartments['departments'] . ' Department</I><br><br>';
             }


?>
I would appreciate some help on this!
Thanks,

Posted: Thu Dec 11, 2003 3:25 pm
by AnsonM

Posted: Thu Dec 11, 2003 4:27 pm
by microthick
Hi Melissa,

You can use a simple join instead.

Code: Select all

$subquery = "SELECT positions.*, departments.* FROM positions, departments WHERE positions.depID = departments.depID AND catID=$catID ORDER BY positions.title";