(PHP, MYSQL) How to check the conditions from different database table?
<p>I'm new to PHP and now I have some problems about the logic to query the data from my database.</p>

<p>First if I have 2 databases which are Application file and Position file.</p>

<p>Application file includes</p>

<ol>
<li>Application_ID, 2.Applicant_ID, 3.Position_ID</li>
</ol>

<p>and Position file includes </p>

<p>1.Position_ID, 2.Position_Name, 3.Department_Name</p>

<p>Let me show the unfinished codes...</p>

<p>Link (I copy everything in HTML provided field) -> <a href="https://jsfiddle.net/2e50wvzq/" rel="nofollow noreferrer">https://jsfiddle.net/2e50wvzq/</a></p>

<p>I would like to focus here...</p>

<pre><code>$result4 = mysql_query("SELECT * FROM position"); 
while ($rows4 = mysql_fetch_array($result4, MYSQL_ASSOC)){ 

    $pos_dept = $rows4['Position_Department'];

    $result5 =  mysql_query("SELECT * FROM position WHERE Position_Department = '".$pos_dept."' "); 

    while($rows5 = mysql_fetch_array($result5, MYSQL_ASSOC)){

            $pos_id = $rows5['Position_ID'];

    }

    for($i = 0; $i &lt; $counter; $i++){       
            if($dept_array[$i] == $pos_dept){

            }else{

                        $result6 = mysql_query("SELECT App_Data_ID FROM application_data_file WHERE Position_ID = '".$pos_id."' "); 


                        $pos_app = 0;


                        while ($rows6= mysql_fetch_array($result6, MYSQL_ASSOC)){ 

                            $pos_a = $rows6['App_Data_ID'];

                            $pos_app++;

                        }

                        array_push($dept_array," '".$pos_dept."' ");


                        ?&gt;
                        &lt;TR&gt;

                        &lt;TD&gt; &lt;?php echo $rows4['Position_Department']; ?&gt; &lt;/TD&gt;
                        &lt;TD&gt; &lt;?php echo $rows4['Recruitment_Seat'];    ?&gt; &lt;/TD&gt;

                        &lt;TD&gt; &lt;?php echo $pos_app; ?&gt;                    &lt;/TD&gt;
                        &lt;TD&gt; &lt;?php echo $recruited;   ?&gt;                    &lt;/TD&gt;
                        &lt;TD&gt; &lt;?php echo $Vacancy;  ?&gt;                   &lt;/TD&gt;



                        &lt;/TR&gt;

&lt;?php 
                } 
        } 
}
?&gt;
</code></pre>

<p>Assume that the data in databases are state as...</p>

<p>Application File</p>

<ol>
<li><p>00001, 00001, 00001</p></li>
<li><p>00002, 00003, 00001</p></li>
<li><p>00003, 00002, 00002</p></li>
<li><p>00004, 00004, 00001</p></li>
<li><p>00005, 00006, 00002</p></li>
</ol>

<p>and Position File</p>

<ol>
<li><p>00001, Programmer, Department of IT</p></li>
<li><p>00002, Accountant, Department of Accounting</p></li>
</ol>

<p>As my codes, the result in the table should be as this picture (focus in "Total Applicants" tab in the first table)</p>

<p>-> <a href="https://i.stack.imgur.com/SiVYJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SiVYJ.png" alt="enter image description here"></a></p>

<p>From my thinking so far, if I use the codes as I posted above the result will turn to be like table 2 (or maybe similar).</p>

<p>How can I provide the result like table 1 result? Please help.</p>